prisma-patterns

name	prisma-patterns
description	Production patterns and footgun avoidance for the Prisma ORM in TypeScript backends: schema and index design, ID strategy, include-vs-select and DTO mapping, transaction form selection, the PrismaClient singleton, cursor pagination, soft delete, typed error translation, and serverless connection pooling. Emphasises the non-obvious traps that silently corrupt data or exhaust connections — updateMany/deleteMany returning a count instead of rows, the interactive $transaction 5-second timeout, `migrate dev` resetting the database, @updatedAt skipping bulk writes, edited migration files breaking checksums, and where soft delete leaks through *OrThrow reads. Use when designing Prisma schema, writing queries or transactions, running migrations, or deploying to serverless runtimes.
version	1.0.0
metadata	{"activation_triggers":["prisma","schema\\.prisma","PrismaClient","\\$transaction","updateMany|deleteMany|findUniqueOrThrow|findFirstOrThrow","migrate (dev|deploy|diff)","prisma/migrations","P2002|P2025|P2003|P3006","connection_limit|pgbouncer"],"paths":["**/schema.prisma","**/prisma/**","**/prisma.config.ts"],"risk_class":"low","domain":"data-ml"}
source	affaan-m/ecc@81af4076 skills/prisma-patterns/
license	MIT

Prisma Patterns

Idiomatic patterns and the sharp edges of the Prisma ORM in TypeScript backends. Prisma is ergonomic on the happy path; most production incidents come from a handful of methods that behave differently than their names suggest. This skill front-loads those.

When to Activate

• Designing or changing Prisma schema models, relations, or indexes.
• Writing queries, transactions, or pagination.
• Reaching for any bulk operation (updateMany, deleteMany, createMany).
• Planning or running a migration against anything other than a solo local DB.
• Deploying to a serverless or edge runtime (functions, Lambda, workers).
• Implementing soft delete or per-tenant row filtering.

Version First

The Prisma surface has shifted across majors — check before applying any snippet here.

npx prisma --version

Things that move between versions, and how to stay portable:

• Relation loading. Newer clients can resolve relations with a single SQL JOIN instead of separate round trips. This is usually faster, but on wide 1:N relations or deep include it can inflate the result set (row fan-out). Benchmark both strategies on your real cardinality; do not assume.
• Client shape. Recent installs may expose the client as prisma rather than @prisma/client, may require a driver adapter in the constructor (e.g. a Postgres adapter), and may read the datasource URL from a config file rather than schema.prisma. Let the type checker tell you which shape you have instead of hardcoding one.
• Stable surface. The migration CLI verbs (migrate dev, migrate deploy, generate) are consistent across versions — the traps below apply regardless.

Schema & Indexing

Choosing an ID

Strategy	Reach for it when	Avoid when
collision-resistant string id (cuid-style)	Default. URL-safe, roughly sortable, no coordination needed	An external system demands strictly sequential ids
UUID	You must interoperate with non-Prisma systems that expect UUIDs	Very high write volume — random UUIDs fragment B-tree indexes and hurt insert locality
auto-increment integer	Internal join tables, append-only audit rows	Public-facing ids — a monotonic integer leaks your row count

Sensible defaults

model User {
  id        String    @id @default(cuid())
  email     String    @unique          // @unique already builds an index
  name      String
  role      Role      @default(USER)
  posts     Post[]
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  deletedAt DateTime?

  @@index([createdAt])
  @@index([deletedAt, createdAt])      // composite: soft-delete filter + sort
}

Guidance that pays off later:

• Put an index on every foreign key and on every column that appears in a WHERE or ORDER BY. Missing FK indexes are the most common cause of a query that was fast in dev and crawls in prod.
• Do not add a second @@index on a field that already has @unique — the unique constraint is an index.
• If soft delete is even plausibly coming, declare deletedAt DateTime? on day one. Adding a nullable column later is cheap; retrofitting it onto a large live table under load is not.
• @updatedAt is maintained by the Prisma Client, not the database: raw SQL and any non-Prisma writer bypass it, and older client versions also skipped it on updateMany — see Traps.

Reading Data

include vs select

	include	select
Returns	Every scalar column plus the listed relations	Only the fields you name
Reach for	You genuinely need most columns plus a relation	Hot paths, wide tables, anything user-facing
Cost	Can over-fetch on wide rows	Minimal payload

// include: all columns of user + a trimmed relation
const withPosts = await prisma.user.findUnique({
  where: { id },
  include: { posts: { select: { id: true, title: true } } },
});

// select: an explicit allowlist, nothing else crosses the wire
const lean = await prisma.user.findUnique({
  where: { id },
  select: { id: true, email: true, name: true },
});

Never hand a raw entity to a caller

A Prisma model is your storage shape, not your API contract. Returning it directly leaks whatever columns exist today — password hashes, internal flags, deletedAt — and re-leaks anything you add tomorrow.

// Leaks every column, including ones added after this line was written
return await prisma.user.findUniqueOrThrow({ where: { id } });

// Map to an explicit response shape — the allowlist is the contract
const u = await prisma.user.findUniqueOrThrow({ where: { id } });
return { id: u.id, name: u.name, email: u.email };

The N+1 trap

Loading a relation inside a loop fires one query per parent row.

// One query, then one MORE query per user
const users = await prisma.user.findMany();
for (const u of users) {
  const posts = await prisma.post.findMany({ where: { authorId: u.id } });
}

// Fetch the relation in the same call
const users = await prisma.user.findMany({ include: { posts: true } });

On newer clients the include form may collapse to a single JOIN. Watch the result-set size when each parent can own many children — the fix for N+1 can trade query count for payload size.

Cursor Pagination

Prefer cursor (keyset) pagination for feeds and any large or frequently mutated dataset. Offset pagination (skip/take) is fine only when a user must jump to an arbitrary page number, e.g. an admin table.

async function getPosts(cursor?: string, limit = 20) {
  const rows = await prisma.post.findMany({
    where: { published: true },
    orderBy: [
      { createdAt: "desc" },
      { id: "desc" },            // tiebreaker: stable order on equal timestamps
    ],
    take: limit + 1,             // one extra row is the "is there more?" probe
    ...(cursor && { cursor: { id: cursor }, skip: 1 }),
  });

  const hasNextPage = rows.length > limit;
  if (hasNextPage) rows.pop();   // drop the probe row before returning

  return {
    items: rows,
    nextCursor: hasNextPage ? rows[rows.length - 1].id : null,
  };
}

Two things make this correct: fetching limit + 1 lets you detect a next page without a second count query, and a unique secondary sort key (id) keeps pagination stable when many rows share the same timestamp — without it, rows can be skipped or repeated across pages.

Transactions

Pick the form by the shape of the work, not by habit.

Situation	Form
Independent writes with no data dependency between them	Array form — one round trip
A later step needs the result of an earlier one	Interactive callback
Any external call (email, HTTP, queue) is involved	No transaction — do it outside

// Array form: batched atomically in a single 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 ONLY the tx client inside — never the outer prisma
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 } });
});

Inside an interactive transaction, reaching for the outer prisma client instead of tx runs that statement on a different connection, outside the transaction — it will not roll back with the rest.

The Client Is a Singleton

Every PrismaClient opens its own connection pool. Construct it once per process and reuse it. Under a hot-reloading dev server (framework dev mode, nodemon, ts-node-dev) module re-evaluation will spawn a new client on every reload and exhaust the database's connection slots — pin it on globalThis.

// lib/prisma.ts
import { PrismaClient } from "@prisma/client"; // or your generated client path

function createClient() {
  return new PrismaClient({
    log: process.env.NODE_ENV === "development" ? ["query", "error"] : ["error"],
    // If your install requires a driver adapter, pass `adapter: <pgAdapter>` here.
  });
}

const g = globalThis as unknown as { prisma?: PrismaClient };
export const prisma = g.prisma ?? createClient();
if (process.env.NODE_ENV !== "production") g.prisma = prisma;

If new PrismaClient() errors demanding an adapter, construct the adapter from DATABASE_URL and pass it in; if it works bare, keep it bare. The compiler is the source of truth for which your version needs.

Serverless Connection Pooling

Each concurrent function invocation is its own process with its own pool. A few hundred simultaneous invocations, each opening a default-sized pool, will blow past the database's connection limit. Cap each instance to one connection and route through an external pooler.

Put the parameters inside the URL — building the connection string by concatenation breaks when it already carries query params like ?schema=.

# One connection per instance, bounded wait for it
DATABASE_URL="postgresql://user:pass@host/db?connection_limit=1&pool_timeout=20"

# Through an external pooler (transaction-mode poolers need pgbouncer=true)
DATABASE_URL="postgresql://user:pass@host/db?pgbouncer=true&connection_limit=1"

Error Handling

Catch Prisma's typed errors at the service boundary and translate them into your own domain errors. Never surface a raw Prisma message to an API consumer — it exposes schema internals and constraint names.

import { Prisma } from "@prisma/client"; // or your generated client path

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 missing");
  }
  throw e;
}

The codes worth memorising: P2002 unique-constraint violation, P2025 record not found, P2003 foreign-key violation, P3006 migration failed to apply cleanly to the shadow database.

Traps (each has bitten someone in production)

updateMany / deleteMany return a count, not rows

// result is { count: 2 } — result[0] is undefined, not a user
const result = await prisma.user.updateMany({
  where: { role: "GUEST" },
  data: { role: "USER" },
});

// If you need the affected rows: capture ids, mutate, then re-read
const ids = (
  await prisma.user.findMany({ where: { role: "GUEST" }, select: { id: true } })
).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 } } });

The interactive transaction times out at ~5 seconds

The interactive $transaction callback holds a connection and has a default timeout (about 5s). Put a network call inside it and it will throw "Transaction already closed" while also pinning a connection the whole time.

// External call inside the transaction — blows the timeout
await prisma.$transaction(async (tx) => {
  const user = await tx.user.findUniqueOrThrow({ where: { id } });
  await sendWelcomeEmail(user.email);           // don't
  await tx.user.update({ where: { id }, data: { emailSent: true } });
});

// Keep external work outside the transaction boundary
const user = await prisma.user.findUniqueOrThrow({ where: { id } });
await sendWelcomeEmail(user.email);
await prisma.user.update({ where: { id }, data: { emailSent: true } });

// Raise the timeout only for genuine long-running bulk work
await prisma.$transaction(async (tx) => { /* ... */ }, { timeout: 30_000 });

migrate dev can reset the database

On detecting schema drift, migrate dev may offer to reset the DB — which drops every row. It is a local, solo-developer command.

npx prisma migrate dev --name add_column   # LOCAL only — can drop data on drift
npx prisma migrate deploy                   # CI/CD, staging, prod — never resets

# Inspect drift without touching anything
npx prisma migrate diff \
  --from-migrations ./prisma/migrations \
  --to-schema-datamodel ./prisma/schema.prisma \
  --shadow-database-url "$SHADOW_DATABASE_URL"

Editing an applied migration breaks every downstream environment

Prisma checksums each migration file. Change one after it has been applied anywhere and every environment that already ran the original fails with P3006 checksum mismatch. To change already-shipped SQL, add a new migration.

Breaking column changes need expand-and-contract

Adding NOT NULL to an existing column, or renaming one, in a single migration will lock the table or drop data. Split it across deploys:

# 1. add the new nullable column
npx prisma migrate dev --name add_new_column      # author locally
npx prisma migrate deploy                          # ship

// 2. backfill in a job/script (not an ad-hoc shell one-liner)
await prisma.user.updateMany({ data: { newColumn: derivedValue } });

# 3. only now tighten the constraint
npx prisma migrate dev --name make_new_column_required
npx prisma migrate deploy

@updatedAt depends on the writer

@updatedAt is client-side: raw SQL ($executeRaw) and any non-Prisma writer always bypass it, and older client versions skipped it on updateMany (verify against your pinned version). When in doubt, set it explicitly — redundant on new clients, harmless everywhere.

await prisma.post.updateMany({ where: { authorId }, data: { published: true } }); // updatedAt frozen
await prisma.post.updateMany({
  where: { authorId },
  data: { published: true, updatedAt: new Date() },
});

Soft delete leaks through findUniqueOrThrow

findUniqueOrThrow throws P2025 only when the row is absent from the database. A soft-deleted row still exists, so it comes back with no error. On Prisma < 5.0 you also cannot simply add deletedAt: null to the where — findUnique* required a genuinely unique key (5.0's extendedWhereUnique GA lifted that restriction, so the second line below compiles there). Either way, findFirstOrThrow accepts arbitrary filters and works everywhere.

const user = await prisma.user.findUniqueOrThrow({ where: { id } });               // returns deleted rows
const user2 = await prisma.user.findUniqueOrThrow({ where: { id, deletedAt: null } }); // type error < 5.0
const user3 = await prisma.user.findFirstOrThrow({ where: { id, deletedAt: null } });  // correct

Filter soft deletes explicitly at each call site. Client middleware/extensions can inject the filter globally, but hiding it makes "where did this row go?" debugging much harder — prefer the explicit where: { deletedAt: null }.

deleteMany with no where empties the table

await prisma.post.deleteMany();                          // wipes every post, silently
await prisma.post.deleteMany({ where: { authorId } });   // scoped

Best Practices

Rule	Why
migrate deploy in CI/CD; migrate dev local only	migrate dev can reset on drift
Map entities to explicit response shapes	Storage shape is not an API contract
Translate PrismaClientKnownRequestError at the boundary	Hide constraint internals; return domain errors
Prefer *OrThrow, and findFirstOrThrow when filtering non-unique fields	Automatic P2025; survives soft-delete filters
connection_limit=1 + external pooler in serverless	Prevents connection exhaustion
Always pass where to deleteMany/updateMany	Prevents whole-table mutations
Set updatedAt: new Date() in bulk writes	@updatedAt skips them
Index every FK and every WHERE/ORDER BY column	The prod-slowness class of bug

Related Skills

• postgres-patterns — index internals, EXPLAIN, connection tuning at the DB layer.
• database-migrations — sequencing zero-downtime schema changes.
• backend-patterns — service-layer and API design around the ORM.

Changelog

• 1.0.0 — Initial clean-room authoring. Covers schema/index design, ID strategy, read patterns and DTO mapping, cursor pagination, transaction form selection, the client singleton, serverless pooling, typed error translation, and the eight production traps (bulk-write count returns, interactive-transaction timeout, migrate dev reset, migration checksum, expand-and-contract, @updatedAt on bulk writes, soft-delete leakage through *OrThrow, unscoped deleteMany). Skill-Import-Attestation: reviewed-by=AE9B236FDAF0462874060C6BCFCFACF00335DC74; sha256=8849c85ad2dd728af1dc69dcd7bfb0905252608382bca5c2aa959ceeaee80018