Prisma Best Practices: Production Patterns
Connection pooling, N+1 prevention, typed error handling, transactions, soft deletes, and observability — the production patterns 4Byte applies to every Prisma codebase we ship.
The 6 Most Critical Prisma Production Practices
Most Prisma issues in production fall into six categories: connection pool exhaustion from missing singleton patterns, N+1 query problems from fetching relations in loops, untyped error handling that exposes database internals, non-atomic multi-table writes that leave data in inconsistent states, missing soft delete patterns that cause data loss, and no query monitoring that hides performance degradation until it becomes an outage.
Every pattern in this guide addresses one of these six issues — based on what 4Byte has encountered across 45+ shipped SaaS products using Prisma in production.
▸ Best Practices
6 Prisma Production Patterns — With Code
Always use the singleton pattern
In Next.js, hot module reloading creates new PrismaClient instances on every file change — exhausting the database connection pool. The singleton pattern prevents this.
// lib/prisma.ts
import { PrismaClient } from '@prisma/client'
const globalForPrisma = globalThis as unknown as {
prisma: PrismaClient | undefined
}
export const prisma =
globalForPrisma.prisma ??
new PrismaClient({
log:
process.env.NODE_ENV === 'development'
? [{ level: 'query', emit: 'event' }, 'error', 'warn']
: ['error'],
})
if (process.env.NODE_ENV !== 'production') {
globalForPrisma.prisma = prisma
}Never instantiate PrismaClient directly in a component or API route file — always import from this singleton. Multiple clients = multiple connection pools = database connection exhaustion.
Fix N+1 queries with eager loading
Fetching related data inside a loop creates N+1 queries — one per record. Always use include or select to load relations in a single query.
// ❌ N+1 — 1 query for projects + 1 per project for owner
const projects = await prisma.project.findMany()
for (const project of projects) {
const owner = await prisma.user.findUnique({
where: { id: project.ownerId }
})
}
// ✓ Single query — all projects with owners
const projects = await prisma.project.findMany({
include: {
owner: {
select: { id: true, name: true, email: true }
}
},
orderBy: { createdAt: 'desc' },
take: 50,
})Even with include, always add a take limit on one-to-many relations — without it, include fetches every related record, which becomes a full table scan on large datasets.
Handle errors with typed Prisma exceptions
Map Prisma error codes to meaningful HTTP responses. P2002 (unique constraint), P2025 (record not found), and P2003 (foreign key) cover the majority of production database errors.
import { Prisma } from '@prisma/client'
export function handlePrismaError(error: unknown) {
if (error instanceof Prisma.PrismaClientKnownRequestError) {
switch (error.code) {
case 'P2002':
// Unique constraint violation
return { status: 409, message: 'Record already exists' }
case 'P2025':
// Record not found
return { status: 404, message: 'Record not found' }
case 'P2003':
// Foreign key constraint failed
return { status: 400, message: 'Related record does not exist' }
default:
return { status: 500, message: 'Database error' }
}
}
return { status: 500, message: 'Internal server error' }
}
// Usage in API route
try {
const project = await prisma.project.create({ data })
return Response.json(project)
} catch (error) {
const { status, message } = handlePrismaError(error)
return Response.json({ error: message }, { status })
}Never expose raw Prisma error messages to the client — they contain database schema details. Always map to generic user-facing messages in production.
Use $transaction for atomic multi-table writes
When multiple records must be created or updated atomically — such as creating an org and its admin member in one operation — use $transaction to guarantee all-or-nothing behaviour.
// Sequential transaction (use when operations depend on each other)
const result = await prisma.$transaction(async (tx) => {
const org = await tx.organisation.create({
data: { name: 'Acme Inc', slug: 'acme' },
})
const member = await tx.member.create({
data: {
orgId: org.id,
userId: currentUserId,
role: 'admin',
},
})
// If anything throws here, the entire transaction rolls back
await tx.auditLog.create({
data: {
action: 'org.created',
orgId: org.id,
userId: currentUserId,
},
})
return { org, member }
})Long-running transactions lock rows and degrade concurrent write performance. Keep transactions short — fetch data before the transaction, then only write inside it.
Implement soft deletes with Prisma Extensions
Use a Prisma Extension to transparently implement soft deletion — automatically filtering out deleted records from all queries and setting deletedAt instead of hard-deleting.
// lib/prisma.ts — extended client
import { PrismaClient } from '@prisma/client'
const prismaBase = new PrismaClient()
export const prisma = prismaBase.$extends({
query: {
project: {
async findMany({ args, query }) {
// Automatically exclude soft-deleted records
args.where = { ...args.where, deletedAt: null }
return query(args)
},
async delete({ args, query }) {
// Soft delete instead of hard delete
return prismaBase.project.update({
where: args.where,
data: { deletedAt: new Date() },
})
},
},
},
})
// All findMany calls now exclude deleted records automatically
const projects = await prisma.project.findMany({ where: { orgId } })Soft delete extensions must be applied consistently across all query methods — findFirst, findUnique, count, and aggregate — or deleted records will leak through in those queries.
Log slow queries to catch performance issues early
Configure Prisma to log query durations and route slow queries to your monitoring system. Catching slow queries before they become outages is far cheaper than debugging in production.
// lib/prisma.ts — with query logging
export const prisma = new PrismaClient({
log: [{ level: 'query', emit: 'event' }],
})
// Log queries slower than 100ms
prisma.$on('query', (e) => {
if (e.duration > 100) {
console.warn({
message: 'Slow Prisma query',
query: e.query,
params: e.params,
duration: `${e.duration}ms`,
})
// Send to Sentry, Datadog, or your observability platform
}
})Logging every query in production generates enormous log volume. Log only slow queries (> 100ms threshold) or use Prisma Accelerate's built-in analytics for production observability.
▸ Connection Pooling
Connection Pooling — Pick the Right Setup
The right pooling setup depends on where your Next.js app is deployed.
Each function invocation opens a new connection — a pooler is mandatory to avoid exhausting PostgreSQL's connection limit
Long-running processes reuse connections — the singleton pattern is sufficient, but PgBouncer adds resilience
Supabase includes a built-in PgBouncer at port 6543 — use the pooled connection string for all Prisma queries
▸ Checklist
Prisma Production Launch Checklist
Every item 4Byte verifies before shipping a Prisma-powered SaaS to production.
▸ Why 4Byte
Every Pattern Here Is From Real Production Code
4Byte has shipped Prisma in production across 45+ SaaS products. Every best practice in this guide came from hitting the problem in a real codebase — not from reading Prisma documentation.
If you're building a SaaS and want a Prisma setup that won't cause problems at scale, our free strategy call is where we start.
Currently accepting new SaaS projects
Free strategy call · Response in ≤ 4 hours · No obligation
▸ FAQ
Prisma Best Practices — Common Questions
What is the most common Prisma performance mistake in production?+
The most common performance mistake is the N+1 query problem — fetching a list of records and then making a separate database query for each record's related data in a loop. Fix it by using Prisma's include or select to load all required relations in a single query. The second most common is not using connection pooling in serverless or edge environments, which exhausts database connections under load.
How do I handle connection pooling with Prisma in Next.js?+
In serverless Next.js deployments (Vercel), each function invocation can create a new database connection. Use Prisma Accelerate (Prisma's connection pooler) or PgBouncer in front of PostgreSQL to pool connections. Also use the singleton pattern for PrismaClient in development to avoid creating multiple instances during hot reloads.
How do I handle Prisma errors in production?+
Import PrismaClientKnownRequestError and PrismaClientUnknownRequestError from @prisma/client for typed error handling. Known errors have a code property — P2002 for unique constraint violations, P2025 for record not found, P2003 for foreign key constraint failures. Wrap database calls in try-catch blocks and map Prisma error codes to appropriate HTTP responses.
Should I use Prisma middleware or extensions?+
Prisma Extensions (the modern API) are preferred over the older middleware API. Extensions let you add custom methods to Prisma models, implement soft deletes transparently, add audit logging, or apply automatic filters — without modifying every query call site. Use extensions for cross-cutting concerns like soft deletes, tenant scoping, and audit trails.
How do I implement soft deletes with Prisma?+
Add a deletedAt DateTime? field to models that need soft deletion. Use a Prisma Extension to automatically filter out soft-deleted records from all findMany, findFirst, and findUnique queries, and to set deletedAt instead of actually deleting on delete operations. This keeps the implementation transparent across the entire codebase.
How do I log and monitor Prisma queries in production?+
Configure PrismaClient with log: [{ level: "query", emit: "event" }] to capture query events. Pipe slow queries (duration > 100ms) to your observability platform — Sentry, Datadog, or a custom logger. Prisma Accelerate also provides built-in query analytics. Log the query, duration, and parameters for every slow query to identify optimisation opportunities.
▸ Continue Learning
Related Technology Stack Resources
Let's Build Your SaaS With Prisma Done Right.
Book a free 30-minute call with 4Byte. We'll set up a production-grade Prisma layer with every best practice applied from day one — no pressure, no obligation.
Type Safe
Full TypeScript coverage
28-Day MVP
Avg. delivery time
No Obligation
Zero pressure call