Prisma ORM Skill

Scalar types: String, Int, BigInt, Float, Decimal, Boolean, DateTime, Json, Bytes. Key attributes: @id, @unique, @@unique([a,b]), @default(autoincrement()|uuid()|cuid()|now()), @updatedAt, @map("col"), @@map("table"), @db.VarChar(255), @@index([field]). Prisma 6: Min Node 18.18, TS 5.1. Bytes → Uint8Array. NotFoundError removed — use P2025.

Relations

One-to-One

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

One-to-Many

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

Many-to-Many (Implicit) — Prisma auto-creates join table

model Post     { id Int @id @default(autoincrement()); categories Category[] }
model Category { id Int @id @default(autoincrement()); name String @unique; posts Post[] }

Many-to-Many (Explicit) — custom join table with extra fields

model PostCategory {
  postId Int; categoryId Int; assignedAt DateTime @default(now())
  post Post @relation(fields: [postId], references: [id])
  category Category @relation(fields: [categoryId], references: [id])
  @@id([postId, categoryId]) }

Self-Relation

model Employee { id Int @id @default(autoincrement()); managerId Int?
  manager Employee? @relation("Mgmt", fields: [managerId], references: [id])
  reports Employee[] @relation("Mgmt") }

Rules: Define both sides. Use @relation(name:) for multiple relations between same models. Add @@index on FK fields. Referential actions (onDelete/onUpdate): Cascade, Restrict, NoAction, SetNull, SetDefault.

Client — CRUD

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

// CREATE
const user = await prisma.user.create({
  data: { email: '[email protected]', name: 'Ada', posts: { create: { title: 'Hello' } } },
  include: { posts: true }
}) // => { id: 1, email: '[email protected]', posts: [{ id: 1, title: 'Hello' }] }

// CREATE MANY
await prisma.user.createMany({ data: [...users], skipDuplicates: true }) // => { count: N }

// READ
await prisma.user.findUnique({ where: { email: '[email protected]' } })
await prisma.user.findUniqueOrThrow({ where: { id: 1 } })  // throws P2025 if missing
await prisma.user.findFirst({ where: { active: true }, orderBy: { createdAt: 'desc' } })
await prisma.user.findMany({ where: { role: 'ADMIN' } })

// UPDATE — numeric ops: increment, decrement, multiply, divide, set
await prisma.user.update({ where: { id: 1 }, data: { score: { increment: 10 } } })

// UPSERT
await prisma.user.upsert({
  where: { email: '[email protected]' },
  update: { name: 'Ada Lovelace' },
  create: { email: '[email protected]', name: 'Ada Lovelace' }
})

// DELETE
await prisma.user.delete({ where: { id: 1 } })
await prisma.user.deleteMany({ where: { active: false } }) // => { count: N }

Filtering

await prisma.user.findMany({
  where: {
    email: { contains: 'prisma', mode: 'insensitive' },
    score: { gte: 10, lt: 100 },
    role: { in: ['ADMIN', 'MODERATOR'] },
    name: { not: null },
    OR: [{ active: true }, { role: 'ADMIN' }],
    posts: { some: { title: { startsWith: 'Hello' } } }  // relation filter
  }
})

Operators: equals, not, in, notIn, lt, lte, gt, gte, contains, startsWith, endsWith, mode. Relation filters: some, every, none, is, isNot.

Select & Include

// select — pick specific fields (reduces payload)
await prisma.user.findUnique({
  where: { id: 1 },
  select: { id: true, email: true, posts: { select: { title: true } } }
})
// include — load relations with filtering
await prisma.user.findUnique({
  where: { id: 1 },
  include: { posts: { where: { published: true }, take: 5, orderBy: { createdAt: 'desc' } } }
})

Never mix select and include at the same query level.

Pagination & Ordering

// offset pagination
await prisma.user.findMany({ skip: 20, take: 10, orderBy: [{ role: 'asc' }, { createdAt: 'desc' }] })
// cursor pagination (preferred for large datasets)
await prisma.user.findMany({ cursor: { id: lastId }, skip: 1, take: 10, orderBy: { id: 'asc' } })

Aggregation & GroupBy

const stats = await prisma.user.aggregate({
  _count: true, _avg: { score: true }, _sum: { score: true },
  _min: { createdAt: true }, _max: { score: true }, where: { active: true }
}) // => { _count: 42, _avg: { score: 85.3 }, _sum: { score: 3583 }, ... }

const groups = await prisma.user.groupBy({
  by: ['role'], _count: true, _avg: { score: true },
  having: { score: { _avg: { gt: 50 } } }, orderBy: { _count: { role: 'desc' } }
}) // => [{ role: 'ADMIN', _count: 5, _avg: { score: 92.1 } }, ...]

Transactions

// batch — all-or-nothing array, single round-trip
const [user, post] = await prisma.$transaction([
  prisma.user.create({ data: { email: '[email protected]', name: 'A' } }),
  prisma.post.create({ data: { title: 'First', authorId: 1 } })
])
// interactive — full control, auto-rollback on throw
await prisma.$transaction(async (tx) => {
  const sender = await tx.account.update({ where: { id: 1 }, data: { balance: { decrement: 100 } } })
  if (sender.balance < 0) throw new Error('Insufficient funds')
  await tx.account.update({ where: { id: 2 }, data: { balance: { increment: 100 } } })
}, { maxWait: 5000, timeout: 10000 })

Raw Queries

// tagged template — parameterized, injection-safe
const users = await prisma.$queryRaw`SELECT * FROM "User" WHERE role = ${role}`
const affected = await prisma.$executeRaw`UPDATE "User" SET active = false WHERE "createdAt" < ${cutoff}`
// => 12 (rows affected)

// unsafe variant (only for dynamic SQL)
await prisma.$queryRawUnsafe('SELECT * FROM "User" WHERE id = $1', userId)

TypedSQL (Prisma 5.19+) — fully typed raw SQL

Create prisma/sql/getActiveUsers.sql:

-- @param {Int} $1:minScore
SELECT id, email, score FROM users WHERE active = true AND score > $1

npx prisma generate --sql

import { getActiveUsers } from '@prisma/client/sql'
const users = await prisma.$queryRawTyped(getActiveUsers(50)) // fully typed result

Migrations

npx prisma migrate dev --name init       # create + apply (dev)
npx prisma migrate deploy                # apply pending (production)
npx prisma migrate reset                 # reset DB + reapply + seed
npx prisma migrate status                # check state
npx prisma db push                       # push without migration files (prototyping)
npx prisma db pull                       # introspect DB → schema

Seeding — prisma/seed.ts

import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()
async function main() {
  await prisma.user.upsert({
    where: { email: '[email protected]' }, update: {},
    create: { email: '[email protected]', name: 'Admin', role: 'ADMIN' }
  })
}
main().catch(console.error).finally(() => prisma.$disconnect())

Add "prisma": { "seed": "tsx prisma/seed.ts" } to package.json. Run: npx prisma db seed.

Client Extensions ($extends)

Extensions replace legacy $use middleware. Composable, type-safe, isolated.

// Computed fields
const xprisma = prisma.$extends({
  result: { user: { fullName: {
    needs: { firstName: true, lastName: true },
    compute(user) { return `${user.firstName} ${user.lastName}` }
  }}}
})
await xprisma.user.findFirst() // => { ..., fullName: "Ada Lovelace" }

// Custom model methods
const xprisma = prisma.$extends({
  model: { user: { async findActive() { return prisma.user.findMany({ where: { active: true } }) } } }
})

// Query interception (soft delete)
const xprisma = prisma.$extends({
  query: { $allModels: {
    async findMany({ args, query }) { args.where = { ...args.where, deleted: false }; return query(args) }
  }}
})

Error Handling

import { Prisma } from '@prisma/client'
try { await prisma.user.create({ data: { email: '[email protected]' } }) }
catch (e) {
  if (e instanceof Prisma.PrismaClientKnownRequestError) {
    if (e.code === 'P2002') console.log('Unique violation on:', e.meta?.target)
    if (e.code === 'P2025') console.log('Record not found')
    if (e.code === 'P2003') console.log('FK constraint failed')
  }
  if (e instanceof Prisma.PrismaClientValidationError) console.log('Invalid query')
}

Key codes: P2002 unique, P2003 FK, P2014 required relation, P2025 not found.

Connection Management — Singleton Pattern

const globalForPrisma = globalThis as unknown as { prisma: PrismaClient }
export const prisma = globalForPrisma.prisma ?? new PrismaClient({ log: ['query', 'warn', 'error'] })
if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma

Never instantiate per request. Use $connect() / $disconnect() for explicit lifecycle. For serverless: use pooler (PgBouncer) or Prisma Accelerate. Set connection_limit in URL: postgresql://user:pass@host/db?connection_limit=10&pool_timeout=30

Performance Checklist

• Use select over include — fetch only needed fields.
• Add @@index on where/orderBy/FK fields.
• Cursor pagination for large datasets.
• Batch with createMany/updateMany/deleteMany.
• $transaction([...]) batches multiple ops into one round-trip.
• Avoid N+1: use include/select with nested relations, not query loops.

Testing

// Integration: use separate test DB, reset before suite
import { execSync } from 'child_process'
execSync('npx prisma migrate reset --force', { env: { ...process.env, DATABASE_URL: testUrl } })

// Unit: mock with jest-mock-extended
import { mockDeep } from 'jest-mock-extended'
const prismaMock = mockDeep<PrismaClient>()
prismaMock.user.findMany.mockResolvedValue([{ id: 1, email: '[email protected]', name: 'A' }])

Next.js Integration

Use singleton above. Server Components query directly:

// app/users/page.tsx
import { prisma } from '@/lib/prisma'
export default async function UsersPage() {
  const users = await prisma.user.findMany({ select: { id: true, name: true } })
  return <ul>{users.map(u => <li key={u.id}>{u.name}</li>)}</ul>
}

NestJS Integration

@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit, OnModuleDestroy {
  async onModuleInit() { await this.$connect() }
  async onModuleDestroy() { await this.$disconnect() }
}

Multi-Schema (PostgreSQL)

datasource db { provider = "postgresql"; url = env("DATABASE_URL"); schemas = ["public", "auth"] }
model User { id Int @id @default(autoincrement()); @@schema("auth") }
model Post { id Int @id @default(autoincrement()); @@schema("public") }

Prisma Accelerate — Global Pooling & Caching

npm install @prisma/extension-accelerate

import { withAccelerate } from '@prisma/extension-accelerate'
const prisma = new PrismaClient().$extends(withAccelerate())
await prisma.user.findMany({ cacheStrategy: { ttl: 3600, swr: 600 } })

Set DATABASE_URL to Accelerate proxy. Keep DIRECT_DATABASE_URL for migrations.

Prisma 6 Changes

• Min versions: Node 18.18+, TypeScript 5.1+
• Client generator: provider = "prisma-client", custom output (e.g. ../src/generated/prisma)
• Adapter pattern: @prisma/adapter-pg for PostgreSQL; new PrismaClient({ adapter })
• M-N tables (PostgreSQL): implicit join tables now use primary key instead of unique index
• Bytes → Uint8Array, NotFoundError removed (use P2025)
• Nested create optimization: bulk inserts in single DB roundtrip
• relationLoadStrategy: choose 'join' (default) or 'query' per query
• prisma.config.ts: new centralized config file for drivers, paths, custom setup

Additional Resources

Reference Docs (references/)

• advanced-patterns.md — Client extensions deep-dive ($extends: result, model, client, query), computed fields, soft delete, audit logging, multi-tenancy (row-level, schema-level, DB-level), polymorphic relation workarounds, full-text search, JSON field queries, composite types, views, database functions, relation load strategies
• troubleshooting.md — Error codes (P2002, P2003, P2025, P2014, P2021, P2034), migration conflicts, drift detection, failed production migrations, shadow database errors, N+1 performance, connection pool exhaustion, slow query diagnosis, memory issues, client generation, edge runtime compatibility, long-running transactions, database-specific issues
• api-reference.md — Complete Prisma Client API: all model operations (findUnique/findFirst/findMany/create/update/upsert/delete/createMany/updateMany/deleteMany/count/aggregate/groupBy), filter operators, relation queries (select/include/nested writes/fluent API), raw queries ($queryRaw/$executeRaw/TypedSQL), transaction API, event system, logging, client lifecycle

Scripts (scripts/)

• init-prisma.sh — Initialize Prisma in existing project: install deps, configure datasource, generate client, create first migration. Usage: ./init-prisma.sh [provider] [migration-name]
• migration-ops.sh — All migration operations: create, apply, deploy, reset, status, resolve, diff, seed, push, pull, validate, generate, baseline. Usage: ./migration-ops.sh <command> [args]

Templates (assets/)

• schema.template.prisma — Production schema: User/Profile/Post/Comment/Tag/Category/Session/AuditLog models, enums, all relation types, indexes, soft delete, JSON fields, full-text search
• prisma-service.template.ts — Singleton PrismaService: NestJS-compatible (OnModuleInit/OnModuleDestroy), standalone/Next.js global singleton, query logging, slow query warnings, health check, graceful shutdown
• seed.template.ts — Database seeding with @faker-js/faker: idempotent upserts, production essentials (admin user, categories), development fake data (users, posts, tags, comments), configurable counts