prisma-patterns

name prisma-patterns description Prisma ORM patterns for TypeScript backends — schema design, query optimization, transactions, pagination, and critical traps like updateMany returning count not records, $transaction timeouts, migrate dev resetting the DB, @updatedAt skipped on bulk writes, and serverless connection exhaustion. Use when writing a Prisma schema or query, or debugging transactions, migrations, or serverless connection limits. metadata {"origin":"ECC"} Prisma Patterns Production patterns and non-obvious traps for Prisma ORM in TypeScript backends. Check your version before applying patterns. The Prisma API surface has evolved across major releases: npx prisma --version Notable API differences across versions: relationJoins can load relations via JOIN rather than separate queries, but may cause row explosion on large 1:N relations or deep include — benchmark both approaches omit field modifier and prisma.$extends Client Extensions API were added Newer installs : the package may be named prisma instead of @prisma/client ; PrismaClient may require a driver adapter (e.g. @prisma/adapter-pg ); datasource.url may live in prisma.config.ts instead of schema.prisma CLI commands ( migrate dev , migrate deploy , generate ) are unchanged across versions When to Activate Designing or modifying Prisma schema models and relations Writing queries, transactions, or pagination logic Using updateMany , deleteMany , or any bulk operation Running or planning database migrations Deploying to serverless environments (Vercel, Lambda, Cloudflare Workers) Implementing soft delete or multi-tenant row filtering Core Concepts ID Strategy Strategy Use When Avoid When @default(cuid()) Default choice — URL-safe, sortable, no collisions Sequential IDs needed for external systems @default(uuid()) Interoperability with non-Prisma systems required High-write tables (random UUIDs fragment B-tree indexes) @default(autoincrement()) Internal join tables, audit logs Public-facing IDs (exposes record count) Schema Defaults model User { id String @id @default(cuid()) email String @unique // @unique already creates an index — no @@index needed name String role Role @default(USER) posts Post[] createdAt DateTime @default(now()) updatedAt DateTime @updatedAt deletedAt DateTime? @@index([createdAt]) @@index([deletedAt, createdAt]) // composite for soft-delete + sort queries } Add @@index on every foreign key and column used in WHERE or ORDER BY . Declare deletedAt DateTime? upfront when soft delete is a foreseeable requirement — adding it later requires a migration on a live table. updatedAt @updatedAt is set automatically by Prisma on update and upsert only (see Anti-Patterns for bulk update trap). include vs select include select Returns All scalar fields + specified relations Only specified fields Use when You need most fields plus a relation Hot paths, large tables, avoiding over-fetch Performance May over-fetch on wide tables Minimal payload, faster on large datasets Prisma 5 note Uses JOIN by default ( relationJoins ) Same // include — all columns + relation const user = await prisma. user . findUnique ({ where : { id }, include : { posts : { select : { id : true , title : true } } }, }); // select — explicit allowlist const user = await prisma. user . findUnique ({ where : { id }, select : { id : true , email : true , name : true }, }); Never return raw Prisma entities from API responses — map to response DTOs to control exposed fields: // BAD: leaks passwordHash, deletedAt, internal fields return await prisma. user . findUniqueOrThrow ({ where : { id } }); // GOOD: explicit DTO mapping const user = await prisma. user . findUniqueOrThrow ({ where : { id } }); return { id : user. id , name : user. name , email : user. email }; Transaction Form Selection Situation Use Independent operations, no inter-dependency Array form Later step depends on earlier result Interactive form External calls (email, HTTP) involved Outside transaction entirely // Array form — batched in one round trip const [user, post] = await prisma.$transaction([ prisma. user . update ({ where : { id }, data : { name } }), prisma. post . create ({ data : { title, authorId : id } }), ]); // Interactive form — use tx client only, never the outer prisma client const post = await prisma.$transaction( async (tx) => { const user = await tx. user . findUniqueOrThrow ({ where : { id } }); if (user. role !== 'ADMIN' ) throw new Error ( 'Forbidden' ); return tx. post . create ({ data : { title, authorId : user. id } }); }); PrismaClient Singleton Each PrismaClient instance opens its own connection pool. Instantiate once. // lib/prisma.ts // Option A — adapter-based initialization (required by newer Prisma installs) import { PrismaClient } from '@prisma/client' ; // or the generated client path for your setup import { PrismaPg } from '@prisma/adapter-pg' ; function createPrismaClient ( ) { const adapter = new PrismaPg ({ connectionString : process. env . DATABASE_URL !, }); return new PrismaClient ({ adapter, log : process. env . NODE_ENV === 'development' ? [ 'query' , 'error' ] : [ 'error' ], }); } const globalForPrisma = globalThis as unknown as { prisma ?: PrismaClient }; export const prisma = globalForPrisma. prisma ?? createPrismaClient (); if (process. env . NODE_ENV !== 'production' ) globalForPrisma. prisma = prisma; // Option B — direct initialization (older installs, no adapter needed) // import { PrismaClient } from '@prisma/client'; // export const prisma = globalForPrisma.prisma ?? new PrismaClient({ ... }); Use Option A if your Prisma install requires an adapter argument in the PrismaClient constructor. Use Option B if new PrismaClient() works without arguments. Let the compiler tell you which is correct. The globalThis pattern prevents duplicate instances during hot reload (Next.js, nodemon, ts-node-dev). N+1 Problem Loading relations inside a loop issues one query per row. // BAD: N+1 — one extra query per user const users = await prisma. user . findMany (); for ( const user of users) { const posts = await prisma. post . findMany ({ where : { authorId : user. id } }); } // GOOD: single query const users = await prisma. user . findMany ({ include : { posts : true } }); With Prisma 5+ relationJoins , the include form uses a single JOIN. On large 1:N sets this may increase result set size — benchmark both approaches if the relation can return many rows per parent. Code Examples Cursor Pagination (preferred for feeds and large datasets) async function getPosts ( cursor ?: string , limit = 20 ) { const items = await prisma. post . findMany ({ where : { published : true }, orderBy : [ { createdAt : 'desc' }, { id : 'desc' }, // secondary sort prevents unstable pagination on duplicate timestamps ], take : limit + 1 , ...(cursor && { cursor : { id : cursor }, skip : 1 }), }); const hasNextPage = items. length > limit; if (hasNextPage) items. pop (); return { items, nextCursor : hasNextPage ? items[items. length - 1 ]. id : null }; } Fetch limit + 1 and pop — canonical way to detect hasNextPage without an extra count query. Always include a unique field (e.g. id ) as a secondary orderBy to prevent unstable pagination when multiple rows share the same timestamp. Use offset pagination only when users need to jump to arbitrary pages (admin tables). Soft Delete // Always filter explicitly — do not rely on middleware (hides behavior, hard to debug) const activeUsers = await prisma. user . findMany ({ where : { deletedAt : null } }); await prisma. user . update ({ where : { id }, data : { deletedAt : new Date () } }); await prisma. user . update ({ where : { id }, data : { deletedAt : null } }); // restore Error Handling import { Prisma } from '@prisma/client' ; // or the generated client path for your setup try { await prisma. user . create ({ data : { email } }); } catch (e) { if (e instanceof Prisma . PrismaClientKnownRequestError ) { if (e. code === 'P2002' ) throw new ConflictError ( 'Email already exists' ); if (e. code === 'P2025' ) throw new NotFoundError ( 'Record not found' ); if (e. code === 'P2003' ) throw new BadRequestError ( 'Referenced record does not exist' ); } throw e; } Common codes: P2002 unique violation · P2025 not found · P2003 foreign key violation. Catch at the service boundary and translate to domain errors. Never expose raw Prisma messages to API consumers. Connection Pool — Serverless Embed connection params directly in DATABASE_URL — string concatenation breaks if the URL already has query parameters (e.g. ?schema=public ): # .env — preferred: embed params in the URL DATABASE_URL= "postgresql://user:pass@host/db?connection_limit=1&pool_timeout=20" # With an external pooler (PgBouncer, Supabase pooler) DATABASE_URL= "postgresql://user:pass@host/db?pgbouncer=true&connection_limit=1" // Vercel, AWS Lambda, and similar serverless runtimes: // cap pool to 1 per instance; connection_limit and pool_timeout controlled via DATABASE_URL // Adapter-based setup (if your Prisma install requires an adapter): import { PrismaClient } from '@prisma/client' ; import { PrismaPg } from '@prisma/adapter-pg' ; const prisma = new PrismaClient ({ adapter : new PrismaPg ({ connectionString : process. env . DATABASE_URL }), }); // Direct setup (if your Prisma install does not require an adapter): // const prisma = new PrismaClient(); Anti-Patterns updateMany returns a count, not records // BAD: result is { count: 2 } — users[0] is undefined const users = await prisma. user . updateMany ({ where : { role : 'GUEST' }, data : { role : 'USER' } }); // GOOD: capture IDs first, then update, then fetch only the affected rows const targets = await prisma. user . findMany ({ where : { role : 'GUEST' }, select : { id : true }, }); const ids = targets. map ( ( u ) => u. id ); await prisma. user . updateMany ({ where : { id : { in : ids } }, data : { role : 'USER' } }); const updated = await prisma. user . findMany ({ where : { id : { in : ids } } }); Same applies to deleteMany — returns { count: n } , never the deleted rows. $transaction interactive form times out after 5 seconds // BAD: external call inside transaction exceeds 5s default → "Transaction already closed" await prisma.$transaction( async (tx) => { const user = await tx. user . findUniqueOrThrow ({ where : { id } }); await sendWelcomeEmail (user. email ); // external call await tx. user . update ({ where : { id }, data : { emailSent : true } }); }); // GOOD: external calls outside the transaction const user = await prisma. user . findUniqueOrThrow ({ where : { id } }); await sendWelcomeEmail (user. email ); await prisma. user . update ({ where : { id }, data : { emailSent : true } }); // Only raise timeout when bulk processing genuinely needs it await prisma.$transaction( async (tx) => { ... }, { timeout : 30_000 }); migrate dev can reset the database migrate dev detects schema drift and may prompt to reset the DB, dropping all data. # NEVER on shared dev, staging, or production npx prisma migrate dev --name add_column # Safe everywhere except local solo dev npx prisma migrate deploy # Check drift without applying npx prisma migrate diff \ --from-migrations ./prisma/migrations \ --to-schema-datamodel ./prisma/schema.prisma \ --shadow-database-url " $SHADOW_DATABASE_URL " Manually editing a migration file breaks future deploys Prisma checksums every migration file. Editing after apply causes P3006 checksum mismatch on every environment where the original already ran. Create a new migration instead. Breaking schema changes require multi-step migration Adding NOT NULL to an existing column or renaming a column in one migration will lock the table or drop data. Use expand-and-contract: # Step 1: create migration locally, then deploy npx prisma migrate dev --name add_new_column # local only npx prisma migrate deploy # staging / production // Step 2: backfill data (run in a script or migration job, not in the shell) await prisma. user . updateMany ({ data : { newColumn : derivedValue } }); # Step 3: create the NOT NULL constraint migration locally, then deploy npx prisma migrate dev --name make_new_column_required # local only npx prisma migrate deploy # staging / production @updatedAt does not fire on updateMany @updatedAt is set automatically only on update and upsert . Bulk writes leave it stale. // BAD: updatedAt stays at its old value await prisma. post . updateMany ({ where : { authorId }, data : { published : true } }); // GOOD await prisma. post . updateMany ({ where : { authorId }, data : { published : true , updatedAt : new Date () }, }); Soft delete + findUniqueOrThrow leaks deleted records findUniqueOrThrow throws P2025 only when the row does not exist in the DB. Soft-deleted rows still exist and are returned without error. findUniqueOrThrow requires a unique constraint field in where — adding deletedAt: null alongside id breaks the type because { id, deletedAt } is not a compound unique constraint. Use findFirstOrThrow instead. // BAD: returns soft-deleted user const user = await prisma. user . findUniqueOrThrow ({ where : { id } }); // BAD: Prisma type error — { id, deletedAt } is not a unique constraint const user = await prisma. user . findUniqueOrThrow ({ where : { id, deletedAt : null } });