Learn essential Prisma best practices for building scalable production applications. Covers connection pooling, query optimization, migrations, soft deletes, and performance tips.
Prisma has become the go-to ORM for TypeScript applications, but using it effectively in production requires understanding its nuances. Here are the best practices I've learned from building production applications.
One of the most common issues is connection exhaustion. Always use a singleton pattern:
// 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"
? ["query", "error", "warn"]
: ["error"],
});
if (process.env.NODE_ENV !== "production") {
globalForPrisma.prisma = prisma;
}
For serverless environments, use connection pooling with PgBouncer or Prisma Accelerate:
# Direct connection for migrations
DATABASE_URL="postgresql://user:pass@host:5432/db"
# Pooled connection for queries
DATABASE_URL_POOLED="postgresql://user:pass@host:6543/db?pgbouncer=true"
Never truly delete data in production. Implement soft deletes:
model User {
id String @id @default(cuid())
email String @unique
name String
deletedAt DateTime?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
Create middleware to automatically filter deleted records:
prisma.$use(async (params, next) => {
if (params.action === "findMany" || params.action === "findFirst") {
if (!params.args) params.args = {};
if (!params.args.where) params.args.where = {};
// Only add filter if not explicitly querying deleted records
if (params.args.where.deletedAt === undefined) {
params.args.where.deletedAt = null;
}
}
return next(params);
});
// ❌ Bad - fetches all columns
const users = await prisma.user.findMany();
// ✅ Good - fetches only needed columns
const users = await prisma.user.findMany({
select: {
id: true,
name: true,
email: true,
},
});
async function getUsers(page: number, pageSize: number = 20) {
const [users, total] = await prisma.$transaction([
prisma.user.findMany({
skip: (page - 1) * pageSize,
take: pageSize,
orderBy: { createdAt: "desc" },
}),
prisma.user.count(),
]);
return {
users,
total,
pages: Math.ceil(total / pageSize),
};
}
// ❌ Bad - N+1 query
const posts = await prisma.post.findMany();
for (const post of posts) {
const author = await prisma.user.findUnique({
where: { id: post.authorId },
});
}
// ✅ Good - Single query with include
const posts = await prisma.post.findMany({
include: {
author: {
select: { id: true, name: true },
},
},
});
Use transactions for related operations:
async function createOrder(userId: string, items: OrderItem[]) {
return prisma.$transaction(async (tx) => {
// Create order
const order = await tx.order.create({
data: { userId, status: "PENDING" },
});
// Create order items
await tx.orderItem.createMany({
data: items.map((item) => ({
orderId: order.id,
productId: item.productId,
quantity: item.quantity,
})),
});
// Update inventory
for (const item of items) {
await tx.product.update({
where: { id: item.productId },
data: { stock: { decrement: item.quantity } },
});
}
return order;
});
}
npx prisma migrate dev --name add_user_roles# Development
npx prisma migrate dev --name your_migration_name
# Production (CI/CD)
npx prisma migrate deploy
Add indexes for frequently queried columns:
model Post {
id String @id @default(cuid())
slug String @unique
title String
published Boolean @default(false)
authorId String
createdAt DateTime @default(now())
author User @relation(fields: [authorId], references: [id])
@@index([authorId])
@@index([published, createdAt])
@@index([slug])
}
When you need raw SQL, stay type-safe:
const result = await prisma.$queryRaw<{ id: string; count: bigint }[]>`
SELECT author_id as id, COUNT(*) as count
FROM posts
WHERE published = true
GROUP BY author_id
ORDER BY count DESC
LIMIT 10
`;
Prisma is powerful, but production readiness requires attention to connection management, query optimization, and proper patterns. Follow these best practices to build scalable, maintainable applications.
Remember: always profile your queries in development and monitor them in production!
Get the latest articles, tutorials, and updates delivered straight to your inbox. No spam, unsubscribe at any time.