Prisma patterns

Prisma Patterns

schema.prisma Baseline

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)
  posts     Post[]
  profile   Profile?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([email])
  @@map("users")
}

model Post {
  id          String   @id @default(cuid())
  title       String
  content     String?
  published   Boolean  @default(false)
  authorId    String
  author      User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  tags        Tag[]    @relation("PostTags")
  publishedAt DateTime?
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  @@index([authorId])
  @@index([published, publishedAt(sort: Desc)])
  @@map("posts")
}

model Profile {
  id     String  @id @default(cuid())
  bio    String?
  userId String  @unique
  user   User    @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("profiles")
}

model Tag {
  id    String @id @default(cuid())
  name  String @unique
  posts Post[] @relation("PostTags")

  @@map("tags")
}

enum Role {
  USER
  ADMIN
}

Prisma Client Singleton

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

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

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

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

CRUD Patterns

// Create
const user = await prisma.user.create({
  data: { email: '[email protected]', name: 'Alice', role: 'USER' },
})

// Read with relations
const userWithPosts = await prisma.user.findUniqueOrThrow({
  where: { email: '[email protected]' },
  include: { posts: { where: { published: true }, orderBy: { publishedAt: 'desc' }, take: 5 } },
})

// Selective fields
const users = await prisma.user.findMany({
  select: { id: true, email: true, name: true },
  where: { role: 'ADMIN' },
  orderBy: { createdAt: 'desc' },
})

// Update
const updated = await prisma.user.update({
  where: { id: userId },
  data: { name: 'Alice Smith' },
})

// Upsert
const tag = await prisma.tag.upsert({
  where: { name: 'typescript' },
  create: { name: 'typescript' },
  update: {},
})

// Delete
await prisma.user.delete({ where: { id: userId } })

Pagination

// Offset pagination
async function getUsers(page: number, limit = 20) {
  const [users, total] = await prisma.$transaction([
    prisma.user.findMany({
      skip: (page - 1) * limit,
      take: limit,
      orderBy: { createdAt: 'desc' },
    }),
    prisma.user.count(),
  ])
  return { users, total, pages: Math.ceil(total / limit) }
}

// Cursor pagination (better for large datasets)
async function getUsersCursor(cursor?: string, limit = 20) {
  const users = await prisma.user.findMany({
    take: limit + 1,
    cursor: cursor ? { id: cursor } : undefined,
    orderBy: { id: 'asc' },
  })
  const hasMore   = users.length > limit
  const items     = hasMore ? users.slice(0, -1) : users
  const nextCursor = hasMore ? items[items.length - 1].id : null
  return { items, nextCursor }
}

Transactions

// Interactive transaction (safe for complex logic)
const result = await prisma.$transaction(async (tx) => {
  const from = await tx.account.findUniqueOrThrow({ where: { id: fromId } })
  if (from.balance < amount) throw new Error('Insufficient funds')

  const [debit, credit] = await Promise.all([
    tx.account.update({ where: { id: fromId }, data: { balance: { decrement: amount } } }),
    tx.account.update({ where: { id: toId },   data: { balance: { increment: amount } } }),
  ])

  await tx.transaction.create({ data: { fromId, toId, amount } })
  return { debit, credit }
}, { timeout: 5000 })

Filtering & Search

// Full-text search (PostgreSQL)
const posts = await prisma.post.findMany({
  where: {
    OR: [
      { title:   { contains: query, mode: 'insensitive' } },
      { content: { contains: query, mode: 'insensitive' } },
    ],
    AND: { published: true },
  },
})

// Date range
const recent = await prisma.post.findMany({
  where: {
    createdAt: {
      gte: new Date(Date.now() - 7 * 24 * 60 * 60 * 1000),
    },
  },
})

// Relation filter
const usersWithPosts = await prisma.user.findMany({
  where: { posts: { some: { published: true } } },
})

Raw Queries

// Use for complex queries that Prisma can't express efficiently
const result = await prisma.$queryRaw<{ id: string; postCount: number }[]>`
  SELECT u.id, COUNT(p.id)::int AS "postCount"
  FROM users u
  LEFT JOIN posts p ON p.author_id = u.id AND p.published = true
  GROUP BY u.id
  HAVING COUNT(p.id) > ${minPosts}
  ORDER BY "postCount" DESC
  LIMIT ${limit}
`

Migrations

# Development workflow
npx prisma migrate dev --name add_post_tags

# Production deployment
npx prisma migrate deploy

# Reset dev database
npx prisma migrate reset

# Generate client after schema change
npx prisma generate

Seed Script

// prisma/seed.ts
import { prisma } from '../lib/prisma'

async function main() {
  await prisma.user.upsert({
    where: { email: '[email protected]' },
    create: { email: '[email protected]', name: 'Admin', role: 'ADMIN' },
    update: {},
  })
}

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