Overview
Prisma ORM is designed for performance, but understanding query patterns and optimization techniques can significantly improve your application’s speed and efficiency.
Query Optimization
Select Only What You Need
Use select to retrieve only required fields:
// Don't do this
const users = await prisma.user.findMany()
// Do this instead
const users = await prisma.user.findMany({
select: {
id: true,
email: true,
// Only select fields you actually need
},
})
Avoid N+1 Queries
Use include to load relations in a single query:
// Bad: N+1 query pattern
const users = await prisma.user.findMany()
for (const user of users) {
const posts = await prisma.post.findMany({
where: { userId: user.id }
})
}
// Good: Single query with include
const users = await prisma.user.findMany({
include: {
posts: true,
},
})
const page = 1
const pageSize = 20
const users = await prisma.user.findMany({
skip: (page - 1) * pageSize,
take: pageSize,
orderBy: { createdAt: 'desc' },
})
const total = await prisma.user.count()
Use cursor-based pagination for better performance with large datasets:const users = await prisma.user.findMany({
take: 20,
cursor: {
id: lastUserId,
},
orderBy: { id: 'asc' },
})
Batching and Transactions
Batch Operations
Batch operations automatically combine multiple queries:
// These queries are automatically batched
const [user1, user2, user3] = await Promise.all([
prisma.user.findUnique({ where: { id: '1' } }),
prisma.user.findUnique({ where: { id: '2' } }),
prisma.user.findUnique({ where: { id: '3' } }),
])
Batch Transactions
For atomic operations, use batch transactions:
const [user, posts] = await prisma.$transaction([
prisma.user.create({
data: { email: 'user@example.com' },
}),
prisma.post.findMany({ where: { published: true } }),
])
Batch transactions hold database connections. Keep them short to avoid connection exhaustion.
Interactive Transactions
Use interactive transactions for complex workflows:
const result = await prisma.$transaction(async (tx) => {
const user = await tx.user.create({
data: { email: 'user@example.com' },
})
const post = await tx.post.create({
data: {
title: 'First Post',
userId: user.id,
},
})
return { user, post }
})
Transaction Timeouts
Configure transaction timeouts based on your needs:
const prisma = new PrismaClient({
transactionOptions: {
maxWait: 5000, // Max time to wait for transaction to start (ms)
timeout: 10000, // Max time transaction can run (ms)
},
})
// Or per-transaction
await prisma.$transaction(
async (tx) => {
// Long-running transaction logic
},
{
maxWait: 10000,
timeout: 30000,
},
)
Default timeout is 5 seconds. Increase it for legitimate long-running transactions, but keep it as low as possible to prevent connection exhaustion.
Connection Management
Connection Pooling
Driver adapters provide built-in connection pooling:
import { PrismaPg } from '@prisma/adapter-pg'
import { Pool } from 'pg'
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
max: 10, // Maximum pool size
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 5000,
})
const adapter = new PrismaPg({ pool })
const prisma = new PrismaClient({ adapter })
Serverless Optimization
For serverless environments, reuse connections:
// Global singleton pattern
const globalForPrisma = globalThis as unknown as {
prisma: PrismaClient | undefined
}
export const prisma = globalForPrisma.prisma ?? new PrismaClient()
if (process.env.NODE_ENV !== 'production') {
globalForPrisma.prisma = prisma
}
Index Optimization
Query Analysis
Use database-specific tools to analyze query performance:
// Enable query logging
const prisma = new PrismaClient({
log: [
{ emit: 'event', level: 'query' },
],
})
prisma.$on('query', (e) => {
console.log('Query: ' + e.query)
console.log('Duration: ' + e.duration + 'ms')
})
Index Strategy
Add indexes for frequently queried fields:
model User {
id String @id
email String @unique // Automatically indexed
name String
createdAt DateTime
@@index([createdAt]) // Index for sorting/filtering
@@index([name, email]) // Composite index
}
Too many indexes slow down writes. Only index fields used in where, orderBy, or join conditions.
Caching Strategies
In-Memory Caching
Cache frequently accessed data:
import { LRUCache } from 'lru-cache'
const cache = new LRUCache<string, any>({
max: 500,
ttl: 1000 * 60 * 5, // 5 minutes
})
async function getUser(id: string) {
const cached = cache.get(id)
if (cached) return cached
const user = await prisma.user.findUnique({
where: { id },
})
if (user) cache.set(id, user)
return user
}
Query Result Caching
Use Prisma Accelerate for automatic query caching:
const users = await prisma.user.findMany({
cacheStrategy: {
ttl: 60, // Cache for 60 seconds
swr: 300, // Serve stale for 5 minutes
},
})
// Type-safe raw query
const users = await prisma.$queryRaw<User[]>`
SELECT * FROM "User"
WHERE age >= ${45} AND age <= ${60}
`
// With proper parameterization
const result = await prisma.$queryRaw`
SELECT ${Prisma.join([
Prisma.raw('id'),
Prisma.raw('email'),
])} FROM "User"
WHERE age IN (${Prisma.join([45, 60])})
`
Raw queries bypass Prisma’s type safety. Use them only when necessary and always use parameterized queries to prevent SQL injection.
Query Metrics
Track query performance:
const prisma = new PrismaClient({
log: [
{ emit: 'event', level: 'query' },
],
})
const queryMetrics: { count: number; totalDuration: number } = {
count: 0,
totalDuration: 0,
}
prisma.$on('query', (e) => {
queryMetrics.count++
queryMetrics.totalDuration += e.duration
})
// Log metrics periodically
setInterval(() => {
const avgDuration = queryMetrics.totalDuration / queryMetrics.count
console.log(`Avg query duration: ${avgDuration}ms`)
console.log(`Total queries: ${queryMetrics.count}`)
}, 60000)
Benchmarking
Prisma includes comprehensive benchmarks:
# Run performance benchmarks
pnpm bench
# Run specific benchmark
pnpm bench query-performance
Best Practices
- Select only required fields to reduce data transfer
- Use pagination for large result sets
- Avoid N+1 queries with
include or select
- Batch similar queries with
Promise.all()
- Configure connection pooling appropriately
- Add indexes for frequently queried fields
- Use transactions for atomic operations
- Monitor query performance with logging
- Cache frequently accessed data when appropriate
- Consider raw queries for performance-critical operations