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:

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

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

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

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

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

// 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