Prisma ORM Complete Guide for Next.js (2026)

#nextjs #prisma #typescript #postgres

Prisma is the most widely adopted ORM in the TypeScript ecosystem. It generates a fully typed client from your schema, handles migrations, and works with PostgreSQL, MySQL, SQLite, and MongoDB. This guide covers the patterns that actually hold up in a real Next.js production app.

Installation

npm install prisma @prisma/client
npx prisma init

DATABASE_URL="postgresql://postgres:postgres@localhost:5432/mydb"

Schema Design

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

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

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

model Post {
  id          String    @id @default(cuid())
  title       String
  slug        String    @unique
  content     String
  published   Boolean   @default(false)
  publishedAt DateTime?
  views       Int       @default(0)
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
  authorId    String
  author      User      @relation(fields: [authorId], references: [id], onDelete: Cascade)
  tags        TagsOnPosts[]
  comments    Comment[]
  @@index([slug])
  @@index([authorId])
  @@index([published, createdAt])
}

model Tag {
  id    String        @id @default(cuid())
  name  String        @unique
  slug  String        @unique
  posts TagsOnPosts[]
}

model TagsOnPosts {
  postId     String
  tagId      String
  assignedAt DateTime @default(now())
  post Post @relation(fields: [postId], references: [id], onDelete: Cascade)
  tag  Tag  @relation(fields: [tagId], references: [id], onDelete: Cascade)
  @@id([postId, tagId])
}

enum Role { USER ADMIN MODERATOR }

Key decisions: cuid() over auto-increment IDs (URL-safe, no row count exposure), explicit @@index() on every foreign key (Prisma doesn't add them automatically), onDelete: Cascade so deleting a user removes all their posts.

Migrations

npx prisma migrate dev --name add_user_avatar   # dev: generate + apply
npx prisma migrate deploy                        # production: apply only
npx prisma migrate status                        # check status
npx prisma generate                              # regenerate client after pull

Commit the generated files in prisma/migrations/ — they're the history of your schema.

Singleton Pattern for Next.js

Next.js hot reload creates a new module on every file change, exhausting the connection pool fast:

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

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

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

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = db

Import db everywhere, never new PrismaClient() directly.

CRUD Operations

// Create with nested relations
const post = await db.post.create({
  data: {
    title: 'Getting Started with Prisma',
    slug: 'getting-started-prisma',
    content: '...',
    author: { connect: { id: userId } },
    tags: {
      create: [
        { tag: { connectOrCreate: { where: { slug: 'prisma' }, create: { name: 'Prisma', slug: 'prisma' } } } }
      ]
    }
  },
  include: { author: true, tags: { include: { tag: true } } }
})

// Read with select (always prefer select over include for performance)
const post = await db.post.findUnique({
  where: { slug },
  include: {
    author: { select: { id: true, name: true, avatar: true } },
    tags: { include: { tag: true } },
    _count: { select: { comments: true } }
  }
})

// Paginated list with filters
const posts = await db.post.findMany({
  where: {
    published: true,
    tags: { some: { tag: { slug: 'typescript' } } }
  },
  orderBy: { publishedAt: 'desc' },
  skip: (page - 1) * pageSize,
  take: pageSize,
  select: {
    id: true, title: true, slug: true, excerpt: true, publishedAt: true,
    author: { select: { name: true } },
    _count: { select: { comments: true } }
  }
})

// Update with increment
await db.post.update({
  where: { id: postId },
  data: { views: { increment: 1 } }
})

// Upsert
await db.session.upsert({
  where: { token },
  update: { expiresAt: newExpiry },
  create: { token, expiresAt: newExpiry, userId }
})

Use select aggressively — never include full relations when you only need a few fields.

Transactions

// Simple batch (atomic)
const [post, log] = await db.$transaction([
  db.post.update({ where: { id: postId }, data: { views: { increment: 1 } } }),
  db.activityLog.create({ data: { type: 'POST_VIEW', postId } })
])

// Interactive transaction (read → decide → write)
const result = await db.$transaction(async (tx) => {
  const user = await tx.user.findUniqueOrThrow({ where: { id: userId } })

  if (user.credits < amount) throw new Error('Insufficient credits')

  const updated = await tx.user.update({
    where: { id: userId },
    data: { credits: { decrement: amount } }
  })

  await tx.creditTransaction.create({
    data: { userId, amount: -amount, balanceAfter: updated.credits }
  })

  return updated
})

Raw Queries

When Prisma can't express the SQL you need:

// Typed raw query
const result = await db.$queryRaw<{ month: string; count: bigint }[]>`
  SELECT
    TO_CHAR(created_at, 'YYYY-MM') as month,
    COUNT(*) as count
  FROM posts
  WHERE published = true
  GROUP BY month
  ORDER BY month DESC
  LIMIT 12
`

// Convert BigInt (Prisma returns BigInt for COUNT)
const formatted = result.map(r => ({ month: r.month, count: Number(r.count) }))

// Raw execute
await db.$executeRaw`UPDATE posts SET views = views + 1 WHERE slug = ${slug}`

Always use template literals — never string interpolation. Prisma parameterizes them, preventing SQL injection.

Type Utilities

import { Prisma } from '@prisma/client'

// Type for a post with specific relations
type PostWithRelations = Prisma.PostGetPayload<{
  include: {
    author: { select: { id: true; name: true } }
    _count: { select: { comments: true } }
  }
}>

// Reusable select object
const postSelect = Prisma.validator<Prisma.PostSelect>()({
  id: true, title: true, slug: true,
  author: { select: { name: true, avatar: true } }
})

type PostSummary = Prisma.PostGetPayload<{ select: typeof postSelect }>

Types narrow automatically based on your select/include — no manual interface maintenance.

Seeding

// prisma/seed.ts
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()

async function main() {
  const admin = await prisma.user.upsert({
    where: { email: 'admin@example.com' },
    update: {},
    create: { email: 'admin@example.com', name: 'Admin', role: 'ADMIN' }
  })

  await prisma.post.createMany({
    data: [{ title: 'First Post', slug: 'first-post', content: '...', authorId: admin.id }],
    skipDuplicates: true
  })
}

main().then(() => prisma.$disconnect()).catch(async (e) => {
  console.error(e)
  await prisma.$disconnect()
  process.exit(1)
})

// package.json
{ "prisma": { "seed": "ts-node --compiler-options {\"module\":\"CommonJS\"} prisma/seed.ts" } }

npx prisma db seed
npx prisma studio  # visual browser at localhost:5555

Common Mistakes

N+1 queries:

// ❌ N+1
const posts = await db.post.findMany()
for (const post of posts) {
  post.author = await db.user.findUnique({ where: { id: post.authorId } })
}

// ✅ Single query
const posts = await db.post.findMany({ include: { author: true } })

Missing indexes on foreign keys:

// ❌
model Post { authorId String }

// ✅
model Post {
  authorId String
  @@index([authorId])
}

Exposing full models in API responses:

// ❌ Exposes passwordHash
return db.user.findUnique({ where: { id } })

// ✅
return db.user.findUnique({
  where: { id },
  select: { id: true, name: true, avatar: true }
})