| name | prisma-patterns |
| description | 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. |
| origin | ECC |
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 |
const user = await prisma.user.findUnique({
where: { id },
include: { posts: { select: { id: true, title: true } } },
});
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:
return await prisma.user.findUniqueOrThrow({ where: { id } });
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 |
const [user, post] = await prisma.$transaction([
prisma.user.update({ where: { id }, data: { name } }),
prisma.post.create({ data: { title, authorId: id } }),
]);
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.
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.
const users = await prisma.user.findMany();
for (const user of users) {
const posts = await prisma.post.findMany({ where: { authorId: user.id } });
}
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' },
],
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
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 } });
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):
DATABASE_URL="postgresql://user:pass@host/db?connection_limit=1&pool_timeout=20"
DATABASE_URL="postgresql://user:pass@host/db?pgbouncer=true&connection_limit=1"
const prisma = new PrismaClient();
Anti-Patterns
updateMany returns a count, not records
const users = await prisma.user.updateMany({ where: { role: 'GUEST' }, data: { role: 'USER' } });
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
await prisma.$transaction(async (tx) => {
const user = await tx.user.findUniqueOrThrow({ where: { id } });
await sendWelcomeEmail(user.email);
await tx.user.update({ where: { id }, data: { emailSent: true } });
});
const user = await prisma.user.findUniqueOrThrow({ where: { id } });
await sendWelcomeEmail(user.email);
await prisma.user.update({ where: { id }, data: { emailSent: true } });
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.
npx prisma migrate dev --name add_column
npx prisma migrate deploy
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:
npx prisma migrate dev --name add_new_column
npx prisma migrate deploy
await prisma.user.updateMany({ data: { newColumn: derivedValue } });
npx prisma migrate dev --name make_new_column_required
npx prisma migrate deploy
@updatedAt does not fire on updateMany
@updatedAt is set automatically only on update and upsert. Bulk writes leave it stale.
await prisma.post.updateMany({ where: { authorId }, data: { published: true } });
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.
const user = await prisma.user.findUniqueOrThrow({ where: { id } });
const user = await prisma.user.findUniqueOrThrow({ where: { id, deletedAt: null } });
const user = await prisma.user.findFirstOrThrow({ where: { id, deletedAt: null } });
deleteMany without where deletes every row
await prisma.post.deleteMany();
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