prisma-patterns

Original：🇺🇸 English

Translated

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.

9installs

Sourceaffaan-m/everything-claude-code

Added on2026-05-15

NPX Install

npx skill4agent add affaan-m/everything-claude-code prisma-patterns

SKILL.md Content

View Translation Comparison →

Prisma Patterns

Production patterns and non-obvious traps for Prisma ORM in TypeScript backends. Tested against Prisma 5.x and 6.x. Some behaviors differ from Prisma 4.

Check the Prisma version before applying version-specific patterns:

bash

npx prisma --version

Prisma 5 introduced

relationJoins

, which can load relations via JOIN rather than separate queries depending on query strategy and configuration. The

omit

field modifier and

prisma.$extends

Client Extensions API were also added. Note:

relationJoins

can cause row explosion on large 1:N relations or deep nested

include

— benchmark both approaches when relations may return many rows per parent.

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

prisma

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

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
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'] : ['error'],
  });

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

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';

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

bash

# .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 are controlled via DATABASE_URL
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.

bash

# 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:

bash

# 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 } });

bash

# 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 } });

// GOOD: findFirstOrThrow supports arbitrary where conditions
const user = await prisma.user.findFirstOrThrow({ where: { id, deletedAt: null } });

deleteMany

without

where

deletes every row

// BAD: silently wipes the table
await prisma.post.deleteMany();

// GOOD
await prisma.post.deleteMany({ where: { authorId: userId } });

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

NPX Install

Tags

SKILL.md Content

Prisma Patterns

When to Activate

Core Concepts

ID Strategy

Schema Defaults

`include`
vs
`select`

Transaction Form Selection

PrismaClient Singleton

N+1 Problem

Code Examples

Cursor Pagination (preferred for feeds and large datasets)

Soft Delete

Error Handling

Connection Pool — Serverless

Anti-Patterns

`updateMany`
returns a count, not records

`$transaction`
interactive form times out after 5 seconds

`migrate dev`
can reset the database

Manually editing a migration file breaks future deploys

Breaking schema changes require multi-step migration

`@updatedAt`
does not fire on
`updateMany`

Soft delete +
`findUniqueOrThrow`
leaks deleted records

`deleteMany`
without
`where`
deletes every row

Best Practices

Related Skills

prisma-patterns

NPX Install

Tags

SKILL.md Content

Prisma Patterns

When to Activate

Core Concepts

ID Strategy

Schema Defaults

include vs select

Transaction Form Selection

PrismaClient Singleton

N+1 Problem

Code Examples

Cursor Pagination (preferred for feeds and large datasets)

Soft Delete

Error Handling

Connection Pool — Serverless

Anti-Patterns

updateMany returns a count, not records

$transaction interactive form times out after 5 seconds

migrate dev can reset the database

Manually editing a migration file breaks future deploys

Breaking schema changes require multi-step migration

@updatedAt does not fire on updateMany

Soft delete + findUniqueOrThrow leaks deleted records

deleteMany without where deletes every row

Best Practices

Related Skills

`include`
vs
`select`

`updateMany`
returns a count, not records

`$transaction`
interactive form times out after 5 seconds

`migrate dev`
can reset the database

`@updatedAt`
does not fire on
`updateMany`

Soft delete +
`findUniqueOrThrow`
leaks deleted records

`deleteMany`
without
`where`
deletes every row