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

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

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

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

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