High-performance database operations with Prisma ORM
Table of Contents
- Query Batching
- Relation Loading
- Fluent API
- Bulk Operations
- Raw Queries
- Connection Pool Configuration
- Prisma Accelerate
- Transactions
- Middleware
- Quick Reference
Setup & Schema Quick Reference
Install & Initialize
npm install prisma --save-dev
npm install @prisma/client
npx prisma initDatasource & Generator
// schema.prisma
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}Model Definition
model User {
id Int @id @default(autoincrement())
name String
email String @unique
}Field types: Int, String, Boolean, DateTime, Float, Decimal, Json.
Attributes: @id (primary key), @default (default value), @unique (unique constraint), @relation (model relations).
Relationships
// One-to-One
model User {
id Int @id @default(autoincrement())
profile Profile?
}
model Profile {
id Int @id @default(autoincrement())
userId Int @unique
user User @relation(fields: [userId], references: [id])
}
// One-to-Many
model User {
id Int @id @default(autoincrement())
posts Post[]
}
model Post {
id Int @id @default(autoincrement())
authorId Int
author User @relation(fields: [authorId], references: [id])
}
// Many-to-Many
model Post {
id Int @id @default(autoincrement())
categories Category[]
}
model Category {
id Int @id @default(autoincrement())
posts Post[]
}Migrate & Generate
npx prisma migrate dev --name init
npx prisma migrate deploy
npx prisma generateClient Query Quick Reference
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();// Read with filter, relations, pagination, ordering
const users = await prisma.user.findMany({
where: {
isActive: true,
AND: [{ email: { contains: '@example.com' } }],
},
include: { posts: true },
orderBy: { createdAt: 'desc' },
skip: 10,
take: 5,
});
// Aggregation
const roleStats = await prisma.user.groupBy({
by: ['role'],
_count: { id: true },
_avg: { age: true },
_sum: { salary: true },
});
// Upsert
const user = await prisma.user.upsert({
where: { email: 'alice@example.com' },
update: { name: 'Alice Updated' },
create: { email: 'alice@example.com', name: 'Alice' },
});1. Query Batching
Prisma automatically batches queries in the same tick.
// Sent as single round-trip when possible
const [users, posts, comments] = await Promise.all([
prisma.user.findMany(),
prisma.post.findMany(),
prisma.comment.findMany(),
]);
// DataLoader pattern built-in
const users = await Promise.all(
userIds.map(id => prisma.user.findUnique({ where: { id } }))
);
// Batched into: SELECT * FROM users WHERE id IN (1, 2, 3, ...)2. Relation Loading
Avoiding N+1
// BAD: N+1 (Lazy loading)
const users = await prisma.user.findMany();
for (const user of users) {
const posts = await prisma.post.findMany({
where: { authorId: user.id }
});
}
// GOOD: include (Eager load relations)
const users = await prisma.user.findMany({
include: {
posts: true,
profile: true,
},
});
// GOOD: select with relations (only needed fields)
const users = await prisma.user.findMany({
select: {
id: true,
name: true,
posts: {
select: {
id: true,
title: true,
},
take: 5,
},
},
});Nested Includes
const users = await prisma.user.findMany({
include: {
posts: {
include: {
comments: {
include: {
author: true,
},
},
},
},
},
});3. Fluent API
// BAD: Causes N+1 if accessing posts later
const user = await prisma.user.findUnique({
where: { id: 1 },
});
const posts = await prisma.post.findMany({
where: { authorId: user.id },
});
// GOOD: Fluent API chains in single query
const posts = await prisma.user
.findUnique({ where: { id: 1 } })
.posts();
// With filtering
const publishedPosts = await prisma.user
.findUnique({ where: { id: 1 } })
.posts({ where: { published: true } });4. Bulk Operations
createMany
const users = await prisma.user.createMany({
data: [
{ name: 'User1', email: 'u1@example.com' },
{ name: 'User2', email: 'u2@example.com' },
{ name: 'User3', email: 'u3@example.com' },
],
skipDuplicates: true, // Ignore conflicts
});updateMany
await prisma.user.updateMany({
where: { active: false },
data: { deletedAt: new Date() },
});deleteMany
await prisma.post.deleteMany({
where: {
authorId: userId,
published: false,
},
});Upsert
const user = await prisma.user.upsert({
where: { email: 'user@example.com' },
update: { name: 'Updated Name' },
create: { email: 'user@example.com', name: 'New User' },
});5. Raw Queries
// Raw query with type
interface UserWithCount {
id: number;
name: string;
post_count: bigint;
}
const users = await prisma.$queryRaw<UserWithCount[]>`
SELECT u.id, u.name, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.author_id
GROUP BY u.id
HAVING COUNT(p.id) > 5
`;
// Raw execute (for INSERT, UPDATE, DELETE)
await prisma.$executeRaw`
UPDATE users SET last_login = NOW() WHERE id = ${userId}
`;
// Parameterized queries
const email = 'user@example.com';
const user = await prisma.$queryRaw`
SELECT * FROM users WHERE email = ${email}
`;6. Connection Pool Configuration
Schema Configuration
// schema.prisma
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
// Connection URL with pool settings
// postgresql://user:pass@host:5432/db?connection_limit=20&pool_timeout=10Programmatic Configuration
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient({
datasources: {
db: {
url: process.env.DATABASE_URL,
},
},
// Log slow queries
log: [
{ level: 'query', emit: 'event' },
{ level: 'warn', emit: 'stdout' },
{ level: 'error', emit: 'stdout' },
],
});
// Log slow queries
prisma.$on('query', (e) => {
if (e.duration > 100) { // > 100ms
console.log(`Slow query (${e.duration}ms): ${e.query}`);
}
});7. Prisma Accelerate
Edge caching with Prisma Accelerate.
// schema.prisma
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
directUrl = env("DIRECT_DATABASE_URL")
}// Caching with stale-while-revalidate
const users = await prisma.user.findMany({
cacheStrategy: {
ttl: 60, // Cache for 60 seconds
swr: 120, // Serve stale for 120s while revalidating
},
});
// Invalidate cache
await prisma.$accelerate.invalidate({
tags: ['users'],
});8. Transactions
Interactive Transaction
const result = await prisma.$transaction(async (tx) => {
const user = await tx.user.create({
data: { name: 'New User', email: 'new@example.com' },
});
await tx.post.create({
data: { title: 'Welcome', authorId: user.id },
});
return user;
});Batch Transaction
// Single round-trip
const [users, posts] = await prisma.$transaction([
prisma.user.findMany(),
prisma.post.findMany(),
]);Transaction Options
await prisma.$transaction(
async (tx) => {
// ...
},
{
maxWait: 5000, // Max time to acquire connection
timeout: 10000, // Max transaction duration
isolationLevel: 'Serializable',
}
);9. Middleware
Logging/Metrics
prisma.$use(async (params, next) => {
const before = Date.now();
const result = await next(params);
const after = Date.now();
console.log(`${params.model}.${params.action} took ${after - before}ms`);
return result;
});Soft Delete
prisma.$use(async (params, next) => {
if (params.action === 'delete') {
params.action = 'update';
params.args.data = { deletedAt: new Date() };
}
if (params.action === 'deleteMany') {
params.action = 'updateMany';
params.args.data = { deletedAt: new Date() };
}
return next(params);
});10. Quick Reference
N+1 Prevention
// Include relations
prisma.user.findMany({
include: {
posts: true,
profile: true,
},
});
// Fluent API
prisma.user.findUnique({ where: { id: 1 } }).posts();ORM Comparison
┌─────────────────┬─────────────────────────────────────────────────┐
│ Feature │ Prisma │
├─────────────────┼─────────────────────────────────────────────────┤
│ N+1 Prevention │ include: {} │
│ Partial Select │ select: {} │
│ Bulk Insert │ createMany() │
│ Bulk Update │ updateMany() │
│ Prepared Stmt │ Automatic │
│ Raw SQL │ $queryRaw`` │
│ Connection Pool │ URL params │
│ Type Safety │ Full │
│ Bundle Size │ ~300KB │
└─────────────────┴─────────────────────────────────────────────────┘Optimization Checklist
PRISMA PERFORMANCE:
├── [ ] Use include/select to avoid N+1
├── [ ] Use select for partial field loading
├── [ ] Use bulk operations (createMany, updateMany)
├── [ ] Configure connection pool in URL
├── [ ] Log slow queries for monitoring
├── [ ] Use transactions for multi-step operations
├── [ ] Consider Prisma Accelerate for caching
├── [ ] Use fluent API for relation queries
└── [ ] Add indexes on filtered/joined columnsQuery Time Budget
TARGET: 5ms per query
BREAKDOWN:
├── Connection acquisition: 0.1ms (from pool)
├── Query parsing: 0.1ms
├── Planning: 0.2ms
├── Execution: 4.0ms
├── Result transfer: 0.6ms
└── TOTAL: 5.0msConnection URL Parameters
postgresql://user:pass@host:5432/db?
connection_limit=20& # Max pool connections
pool_timeout=10& # Wait time for connection
connect_timeout=5& # Connection timeout
statement_timeout=30000 # Query timeout (ms)