Prisma Cheat Sheet: ORM for TypeScript and Node.js

Prisma is a next-generation ORM for TypeScript and Node.js that makes database access type-safe and intuitive. This cheat sheet covers everything from schema definition to advanced queries.

Quick reference

Schema syntax

Datasource and generator

datasource db {
  provider = "postgresql" // mysql | sqlite | sqlserver | mongodb | cockroachdb
  url      = env("DATABASE_URL")
}

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

Model definition

model User {
  id        Int      @id @default(autoincrement())
  uuid      String   @id @default(uuid())          // alternative: uuid PK
  email     String   @unique
  name      String?                                 // optional field
  role      Role     @default(USER)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  posts     Post[]                                  // relation

  @@index([email, role])                            // composite index
  @@map("users")                                    // custom table name
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

Field types

Field attributes

model Post {
  id        Int      @id @default(autoincrement())
  slug      String   @unique
  title     String   @db.VarChar(255)     // native type
  views     Int      @default(0)
  published Boolean  @default(false)
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  tags      Tag[]    @relation("PostToTag")
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@unique([authorId, slug])               // composite unique
  @@fulltext([title, content])             // MySQL full-text index
}

Relations

// One-to-many (User has many Posts)
model User {
  id    Int    @id @default(autoincrement())
  posts Post[]
}

model Post {
  id       Int  @id @default(autoincrement())
  authorId Int
  author   User @relation(fields: [authorId], references: [id])
}

// Many-to-many (implicit join table)
model Post {
  id   Int   @id @default(autoincrement())
  tags Tag[]
}

model Tag {
  id    Int    @id @default(autoincrement())
  posts Post[]
}

// Many-to-many (explicit join table)
model PostTag {
  postId    Int
  tagId     Int
  assignedBy String
  post      Post @relation(fields: [postId], references: [id])
  tag       Tag  @relation(fields: [tagId], references: [id])

  @@id([postId, tagId])
}

// One-to-one
model User {
  id      Int      @id @default(autoincrement())
  profile Profile?
}

model Profile {
  id     Int  @id @default(autoincrement())
  userId Int  @unique
  user   User @relation(fields: [userId], references: [id])
}

// Self-relation (categories with parent)
model Category {
  id       Int        @id @default(autoincrement())
  parentId Int?
  parent   Category?  @relation("CategoryTree", fields: [parentId], references: [id])
  children Category[] @relation("CategoryTree")
}

Prisma Client setup

// lib/prisma.ts — singleton pattern (important for dev hot-reload)
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', 'error', 'warn'] : ['error'],
})

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

CRUD operations

Create

// Create one
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    name: 'Alice',
    role: 'ADMIN',
  },
})

// Create with nested relation
const post = await prisma.post.create({
  data: {
    title: 'Hello World',
    author: {
      connect: { email: 'alice@example.com' },   // connect existing
      // create: { email: 'new@example.com' },    // or create new
    },
    tags: {
      connectOrCreate: [
        { where: { name: 'tech' }, create: { name: 'tech' } },
      ],
    },
  },
  include: { author: true, tags: true },
})

// Create many (no return by default)
await prisma.user.createMany({
  data: [
    { email: 'bob@example.com', name: 'Bob' },
    { email: 'carol@example.com', name: 'Carol' },
  ],
  skipDuplicates: true,
})

// createManyAndReturn (Prisma 5.14+)
const users = await prisma.user.createManyAndReturn({
  data: [{ email: 'dave@example.com' }],
})

Read

// Find unique (throws if not found)
const user = await prisma.user.findUniqueOrThrow({ where: { id: 1 } })

// Find unique (null if not found)
const user = await prisma.user.findUnique({ where: { email: 'alice@example.com' } })

// Find first matching
const post = await prisma.post.findFirst({
  where: { published: true },
  orderBy: { createdAt: 'desc' },
})

// Find many
const posts = await prisma.post.findMany({
  where: {
    published: true,
    author: { role: 'ADMIN' },          // filter via relation
  },
  orderBy: [
    { createdAt: 'desc' },
    { title: 'asc' },
  ],
  take: 10,
  skip: 20,
  include: {
    author: { select: { name: true, email: true } },
    tags: true,
    _count: { select: { comments: true } },   // count relation
  },
})

// Select specific fields
const users = await prisma.user.findMany({
  select: { id: true, email: true, name: true },
})

Update

// Update one (by unique field)
const user = await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Alice Smith' },
})

// Update many
const { count } = await prisma.post.updateMany({
  where: { published: false, createdAt: { lt: new Date('2024-01-01') } },
  data: { published: true },
})

// Upsert (update or create)
const user = await prisma.user.upsert({
  where: { email: 'alice@example.com' },
  update: { name: 'Alice Updated' },
  create: { email: 'alice@example.com', name: 'Alice' },
})

// Atomic number updates
await prisma.post.update({
  where: { id: 1 },
  data: { views: { increment: 1 } },    // also: decrement, multiply, divide, set
})

// Nested update
await prisma.user.update({
  where: { id: 1 },
  data: {
    posts: {
      create: { title: 'New Post' },             // create nested
      update: { where: { id: 5 }, data: { title: 'Updated' } },
      disconnect: { id: 3 },                     // remove relation
      deleteMany: { published: false },          // delete nested records
    },
  },
})

Delete

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

// Delete many
const { count } = await prisma.post.deleteMany({
  where: { createdAt: { lt: new Date('2023-01-01') } },
})

Filtering

// Comparison operators
where: {
  age: { gt: 18, lte: 65 },         // gt, gte, lt, lte
  name: { not: null },              // not null
  email: { in: ['a@b.com', 'c@d.com'] },
  role: { notIn: ['BANNED'] },
}

// String filters
where: {
  email: { contains: '@gmail.com' },
  name:  { startsWith: 'Al' },
  slug:  { endsWith: '-draft' },
  title: { contains: 'prisma', mode: 'insensitive' },  // case-insensitive
}

// Logical operators
where: {
  AND: [
    { published: true },
    { createdAt: { gte: new Date('2024-01-01') } },
  ],
  OR: [
    { role: 'ADMIN' },
    { email: { contains: '@company.com' } },
  ],
  NOT: { banned: true },
}

// Relation filters
where: {
  // has at least one post
  posts: { some: { published: true } },
  // all posts are published
  posts: { every: { published: true } },
  // has no posts
  posts: { none: {} },
  // nested scalar filter
  posts: { some: { views: { gt: 100 } } },
}

// Null checks
where: {
  deletedAt: null,        // IS NULL
  deletedAt: { not: null }, // IS NOT NULL
}

Pagination

// Offset pagination
const PAGE_SIZE = 10

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

// Cursor pagination (better performance for large tables)
async function getNextPage(cursor?: number) {
  const items = await prisma.post.findMany({
    take: 10,
    ...(cursor && { skip: 1, cursor: { id: cursor } }),
    orderBy: { id: 'asc' },
  })
  const nextCursor = items.length === 10 ? items[9].id : null
  return { items, nextCursor }
}

Aggregations

// Count
const count = await prisma.user.count({ where: { role: 'ADMIN' } })

// Aggregate
const stats = await prisma.post.aggregate({
  where: { published: true },
  _count: true,
  _sum: { views: true },
  _avg: { views: true },
  _min: { createdAt: true },
  _max: { views: true },
})
// stats._avg.views -> number | null

// Group by
const groups = await prisma.post.groupBy({
  by: ['authorId', 'published'],
  _count: { id: true },
  _sum: { views: true },
  having: { views: { _sum: { gt: 100 } } },
  orderBy: { _sum: { views: 'desc' } },
})

Transactions

// Sequential transaction
const [payment, order] = await prisma.$transaction([
  prisma.payment.create({ data: { amount: 100 } }),
  prisma.order.update({ where: { id: 1 }, data: { status: 'PAID' } }),
])

// Interactive transaction (with business logic)
await prisma.$transaction(async (tx) => {
  const sender = await tx.account.update({
    where: { id: senderId },
    data: { balance: { decrement: amount } },
  })

  if (sender.balance < 0) {
    throw new Error('Insufficient funds')   // auto-rollback on throw
  }

  await tx.account.update({
    where: { id: receiverId },
    data: { balance: { increment: amount } },
  })
})

// Transaction with timeout and isolation level
await prisma.$transaction(
  async (tx) => { /* ... */ },
  { timeout: 10000, isolationLevel: 'Serializable' }
)

Raw queries

// Raw SQL query
const users = await prisma.$queryRaw<User[]>`
  SELECT * FROM users
  WHERE created_at > ${new Date('2024-01-01')}
  ORDER BY name ASC
`

// Raw execute (INSERT/UPDATE/DELETE)
const { count } = await prisma.$executeRaw`
  UPDATE posts SET views = views + 1 WHERE id = ${postId}
`

// Unsafe raw (when you need dynamic SQL — avoid if possible)
const tableName = 'users'
const result = await prisma.$queryRawUnsafe(
  `SELECT * FROM ${tableName} LIMIT 10`
)

Middleware and extensions (Prisma 4.7+)

// Soft delete extension
const xprisma = prisma.$extends({
  model: {
    $allModels: {
      async softDelete<T>(this: T, id: number) {
        const context = Prisma.getExtensionContext(this)
        return (context as any).update({
          where: { id },
          data: { deletedAt: new Date() },
        })
      },
    },
  },
  query: {
    $allModels: {
      async findMany({ args, query }) {
        args.where = { ...args.where, deletedAt: null }   // auto-filter soft-deleted
        return query(args)
      },
    },
  },
})

await xprisma.user.softDelete(1)

Common mistakes

Migrations workflow

# Development: create and apply migration
npx prisma migrate dev --name add_user_role

# Review generated SQL before applying
cat prisma/migrations/20240101_add_user_role/migration.sql

# Production: apply pending migrations only (no dev features)
npx prisma migrate deploy

# Check migration status
npx prisma migrate status

# Reset database (dev only — drops all data)
npx prisma migrate reset

# Mark a migration as applied without running it (when manually applied)
npx prisma migrate resolve --applied 20240101_add_user_role

Prisma with Next.js

// app/api/users/route.ts
import { prisma } from '@/lib/prisma'
import { NextResponse } from 'next/server'

export async function GET() {
  const users = await prisma.user.findMany({
    select: { id: true, name: true, email: true },
    take: 50,
    orderBy: { createdAt: 'desc' },
  })
  return NextResponse.json(users)
}

export async function POST(request: Request) {
  const body = await request.json()
  const user = await prisma.user.create({
    data: { email: body.email, name: body.name },
  })
  return NextResponse.json(user, { status: 201 })
}

Seeding

// 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', published: true },
          { title: 'Getting started with Prisma', published: false },
        ],
      },
    },
  })
  console.log({ alice })
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect())

// package.json
{
  "prisma": {
    "seed": "ts-node prisma/seed.ts"
  }
}

npx prisma db seed

FAQ

Q: Should I use findUnique or findFirst when filtering by a non-unique field? Use findFirst for non-unique fields — findUnique only works with fields marked @unique or @id. findFirst accepts any where clause but returns only one result.

Q: How do I handle the N+1 problem in Prisma? Use include or select with nested relations instead of looping and fetching in N queries. For deeply nested or computed relations, use prisma.$transaction with multiple queries or raw SQL with a JOIN.

Q: What's the difference between connect, connectOrCreate, and create in nested writes?

• create — creates a new related record
• connect — links to an existing record by unique identifier
• connectOrCreate — links if exists, creates if not (upsert for relations)

Q: Can I use Prisma with an existing database? Yes — run npx prisma db pull to introspect the existing schema into schema.prisma, then npx prisma generate to generate the client. Use prisma migrate diff to manage future changes.

Q: How do I handle optional vs required relations in Prisma? A required relation (no ?) means the foreign key is NOT NULL. An optional relation (Model?) allows NULL. Use onDelete: SetNull with optional relations to avoid constraint errors when the parent is deleted.

Q: Is Prisma suitable for production at scale? Yes — Prisma is used in production by many large companies. For high-throughput scenarios, tune the connection pool (connection_limit in the URL), use Prisma Accelerate for edge caching, and avoid N+1 patterns. For very complex queries, drop to raw SQL with $queryRaw.