Prisma with Postgres

Table of Contents

Setting Up PostgreSQL with Prisma in a Next.js Project

1. Setting Up Your Project

  1. Initialize a new Next.js project:

    npx create-next-app@latest my-nextjs-app
cd my-nextjs-app

  2. Install Prisma and PostgreSQL client:

    npm install prisma @prisma/client
npm install pg

  3. Initialize Prisma in your project:

    npx prisma init

2. Configure PostgreSQL

  1. Set up a PostgreSQL database:

    • You can set up a PostgreSQL database locally, on Docker, or use a managed service like Heroku or Supabase.

  2. Update the .env file with your PostgreSQL connection string:

    DATABASE_URL="postgresql://user:password@localhost:5432/mydatabase"

3. Define Your Prisma Schema

  1. Open prisma/schema.prisma and define your data model:

    datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

4. Migrate the Database

  1. Create and apply the migration:

    npx prisma migrate dev --name init

  2. Generate the Prisma Client:

    npx prisma generate

  3. Push the DB to actual Database:

    npx prisma db push

5. Running the Application

  1. Start the Next.js development server:

    npm run dev

  2. Access your application:

    • Open your browser and navigate to http://localhost:3000.

Additional Tips

  • Prisma Studio: A GUI to view and edit data in your database.

    npx prisma studio

  • Environment Variables: Use .env.local for local development and .env.production for production environment variables.

  • Error Handling: Ensure you handle errors properly in your API routes and components.

  • Deployment: When deploying your Next.js app, ensure your PostgreSQL database is accessible and that environment variables are correctly set up in your hosting service (e.g., Vercel, Netlify).

Prisma DBML generator:

DBML: Database Markup Language, is a good representation for diagrams.

https://github.com/notiz-dev/prisma-dbml-generator

Here is the generator

  1. Install this generator:
npm install -D prisma-dbml-generator
  1. Add the generator to the schema.prisma
generator dbml {
  provider = "prisma-dbml-generator"
}

  1. Running npx prisma generate for the schema.prisma

  2. Visualize the schema.dbml

More resources:

  • Drawsql a good ERD maker.
  • DBDiagram another great ERD maker using DBML.
    • Exports to DBDocs too.
  • DBDocs documentation for database.
  • Neon a good Postgres database provider.

Thanks to Aman Bhatt for the blog!

Tags :