dev_tools
guides
Back to guides

Prisma + PostgreSQL

Set up Prisma ORM with PostgreSQL for fully type-safe database access, migrations, and seeding in a Node.js or Next.js project.

Prisma
PostgreSQL
TypeScript
ORM
1

Initialize the Project

Create a new Node.js project with TypeScript. Skip this step if you're adding Prisma to an existing project.

mkdir my-prisma-app && cd my-prisma-app && npm init -y && npm install -D typescript ts-node @types/node && npx tsc --init
2

Install Prisma

Install the Prisma CLI as a dev dependency and the Prisma Client as a runtime dependency.

npm install -D prisma && npm install @prisma/client
3

Initialize Prisma

Run prisma init to create prisma/schema.prisma and a .env file. Pass --datasource-provider postgresql to pre-configure the provider.

npx prisma init --datasource-provider postgresql
4

Configure Database URL

Set your PostgreSQL connection string in .env. For cloud providers, grab the connection string from their dashboard.

.env
# Local PostgreSQL
DATABASE_URL="postgresql://postgres:password@localhost:5432/myapp?schema=public"

# Supabase (Transaction Mode pooler — recommended for serverless)
# DATABASE_URL="postgresql://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:6543/postgres?pgbouncer=true"

# Neon
# DATABASE_URL="postgresql://[user]:[password]@[host]/[database]?sslmode=require"
5

Define the Schema

Model your data in prisma/schema.prisma. Prisma models map 1:1 to database tables. Relations are declared with @relation.

prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

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

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  role      Role     @default(USER)
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId  Int
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([authorId])
}

enum Role {
  USER
  ADMIN
}
💡Run `npx prisma format` to auto-format your schema file.
6

Run the First Migration

Create and apply a migration. Prisma generates SQL and stores the migration history in prisma/migrations/. Always review the generated SQL before applying in production.

npx prisma migrate dev --name init
7

Create a Singleton Prisma Client

In development, Next.js hot-reload can create many Prisma Client instances. A singleton pattern prevents connection pool exhaustion.

src/lib/prisma.ts
import { PrismaClient } from '@prisma/client'

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined
}

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    log: process.env.NODE_ENV === 'development'
      ? ['query', 'error', 'warn']
      : ['error'],
  })

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma
}
8

Perform CRUD Operations

Use the generated Prisma Client to query your database. All methods are fully typed based on your schema.

src/lib/users.ts
import { prisma } from './prisma'

"text-muted-foreground italic">// CREATE
export async function createUser(email: string, name: string) {
  return prisma.user.create({
    data: { email, name },
  })
}

"text-muted-foreground italic">// READ — single user with their posts
export async function getUserWithPosts(id: number) {
  return prisma.user.findUnique({
    where: { id },
    include: { posts: { orderBy: { createdAt: 'desc' } } },
  })
}

"text-muted-foreground italic">// READ — paginated list
export async function listUsers(page = 1, pageSize = 20) {
  return prisma.user.findMany({
    skip: (page - 1) * pageSize,
    take: pageSize,
    orderBy: { createdAt: 'desc' },
  })
}

"text-muted-foreground italic">// UPDATE
export async function publishPost(postId: number) {
  return prisma.post.update({
    where: { id: postId },
    data: { published: true },
  })
}

"text-muted-foreground italic">// DELETE
export async function deleteUser(id: number) {
  "text-muted-foreground italic">// Posts are deleted automatically via onDelete: Cascade
  return prisma.user.delete({ where: { id } })
}

"text-muted-foreground italic">// TRANSACTION — create a user and first post atomically
export async function createUserWithPost(
  email: string,
  name: string,
  postTitle: string,
) {
  return prisma.$transaction(async (tx) => {
    const user = await tx.user.create({ data: { email, name } })
    const post = await tx.post.create({
      data: { title: postTitle, authorId: user.id },
    })
    return { user, post }
  })
}
9

Seed the Database

Create prisma/seed.ts to populate the database with initial data. Register the seed script in package.json so prisma migrate reset runs it automatically.

prisma/seed.ts
import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  const alice = await prisma.user.upsert({
    where: { email: 'alice@example.com' },
    update: {},
    create: {
      email: 'alice@example.com',
      name: 'Alice',
      role: 'ADMIN',
      posts: {
        create: [
          { title: 'Hello World', content: 'My first post', published: true },
          { title: 'Draft post', content: 'Coming soon...' },
        ],
      },
    },
  })

  console.log({ alice })
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect())
💡Add `"prisma": { "seed": "ts-node prisma/seed.ts" }` to package.json, then run `npx prisma db seed`.
10

Open Prisma Studio

Launch Prisma Studio — a visual browser-based GUI to inspect and edit your database data without writing SQL.

npx prisma studio
💡Prisma Studio opens at http://localhost:5555

Found this guide helpful? Check out more tools and guides.

Browse All Guides