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

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: 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 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 Never return raw Prisma entities from API responses — map to response DTOs to control exposed fields: 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 PrismaClient Singleton Each PrismaClient instance opens its own connection pool. Instantiate once. 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. 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) 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 Error Handling 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 ): Anti Patterns updateMany returns a count, not records Same applies to deleteMany — returns { count: n } , never the deleted rows. $transaction interactive form times out after 5 seconds migrate dev can reset the database migrate dev detects schema drift and may prompt to reset the DB, dropping all data. 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: @updatedAt does not fire on updateMany @updatedAt is set automatically only on update and upsert . Bulk writes leave it stale. 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. deleteMany without where deletes every row Best Practices Rule Reason migrate deploy in CI/CD, migrate dev only locally migrate dev can reset the DB on drift Map entities to response DTOs Prevents leaking internal fields Catch PrismaClientKnownRequestError at service boundary Translate to domain errors Prefer OrThrow methods over manual null checks Throws P2025 automatically; use findFirstOrThrow when filtering non unique fields connection limit=1 + external pooler in serverless Prevents connection exhaustion Always provide where on deleteMany Prevents accidental table wipe Set updatedAt: new Date() manually in updateMany @updatedAt skips bulk writes Related Skills nestjs patterns — NestJS service layer that integrates Prisma postgres patterns — PostgreSQL level indexing and connection tuning database migrations — multi step migration planning for production backend patterns — general API and service layer design

prisma-patterns