Prisma ORM Patterns
Purpose
Complete patterns for using Prisma ORM effectively, including query optimization, transaction handling, and the repository pattern for clean data access.
When to Use This Skill
- Working with Prisma Client for database queries
- Creating repositories for data access
- Using transactions
- Query optimization and N+1 prevention
- Handling Prisma errors
Basic Prisma Usage
Core Query Patterns
import { PrismaService } from '@project-lifecycle-portal/database';
// Always use PrismaService.main
if (!PrismaService.isAvailable) {
throw new Error('Prisma client not initialized');
}
// Find one
const user = await PrismaService.main.user.findUnique({
where: { id: userId },
});
// Find many with filters
const users = await PrismaService.main.user.findMany({
where: { isActive: true },
orderBy: { createdAt: 'desc' },
take: 10,
});
// Create
const newUser = await PrismaService.main.user.create({
data: {
email: 'user@example.com',
name: 'John Doe',
},
});
// Update
const updated = await PrismaService.main.user.update({
where: { id: userId },
data: { name: 'Jane Doe' },
});
// Delete
await PrismaService.main.user.delete({
where: { id: userId },
});Complex Filtering
// Multiple conditions
const users = await PrismaService.main.user.findMany({
where: {
email: { contains: '@example.com' },
isActive: true,
createdAt: { gte: new Date('2024-01-01') },
},
});
// AND/OR conditions
const posts = await PrismaService.main.post.findMany({
where: {
AND: [
{ published: true },
{ author: { isActive: true } },
],
OR: [
{ title: { contains: 'prisma' } },
{ content: { contains: 'prisma' } },
],
},
});Repository Pattern
When to Use Repositories
✅ Use repositories when:
- Complex queries with joins/includes
- Query used in multiple places
- Need to mock for testing
❌ Skip repositories for:
- Simple one-off queries
- Prototyping
Repository Template
import { PrismaService } from '@project-lifecycle-portal/database';
import type { User, Prisma } from '@prisma/client';
export class UserRepository {
async findById(id: string): Promise<User | null> {
return PrismaService.main.user.findUnique({
where: { id },
include: { profile: true },
});
}
async findByEmail(email: string): Promise<User | null> {
return PrismaService.main.user.findUnique({
where: { email },
});
}
async findActive(): Promise<User[]> {
return PrismaService.main.user.findMany({
where: { isActive: true },
orderBy: { createdAt: 'desc' },
});
}
async create(data: Prisma.UserCreateInput): Promise<User> {
return PrismaService.main.user.create({ data });
}
async update(id: string, data: Prisma.UserUpdateInput): Promise<User> {
return PrismaService.main.user.update({ where: { id }, data });
}
async delete(id: string): Promise<void> {
await PrismaService.main.user.delete({ where: { id } });
}
}Using in Service
export class UserService {
private userRepository: UserRepository;
constructor() {
this.userRepository = new UserRepository();
}
async getById(id: string): Promise<User> {
const user = await this.userRepository.findById(id);
if (!user) {
throw new Error('User not found');
}
return user;
}
}Transaction Patterns
Simple Transaction
const result = await PrismaService.main.$transaction(async (tx) => {
const user = await tx.user.create({
data: { email: 'user@example.com', name: 'John' },
});
const profile = await tx.userProfile.create({
data: { userId: user.id, bio: 'Developer' },
});
return { user, profile };
});Interactive Transaction
const result = await PrismaService.main.$transaction(
async (tx) => {
const user = await tx.user.findUnique({ where: { id: userId } });
if (!user) throw new Error('User not found');
const updated = await tx.user.update({
where: { id: userId },
data: { lastLogin: new Date() },
});
await tx.auditLog.create({
data: { userId, action: 'LOGIN', timestamp: new Date() },
});
return updated;
},
{
maxWait: 5000, // Wait max 5s to start
timeout: 10000, // Timeout after 10s
}
);Query Optimization
Use select to Limit Fields
// ❌ Fetches all fields
const users = await PrismaService.main.user.findMany();
// ✅ Only fetch needed fields
const users = await PrismaService.main.user.findMany({
select: {
id: true,
email: true,
name: true,
},
});
// ✅ Select with relations
const users = await PrismaService.main.user.findMany({
select: {
id: true,
email: true,
profile: {
select: { firstName: true, lastName: true },
},
},
});Use include Carefully
// ❌ Excessive includes
const user = await PrismaService.main.user.findUnique({
where: { id },
include: {
posts: { include: { comments: true } },
workflows: { include: { steps: { include: { actions: true } } } },
},
});
// ✅ Only include what you need
const user = await PrismaService.main.user.findUnique({
where: { id },
include: { profile: true },
});N+1 Query Prevention
Problem
// ❌ N+1 Query Problem
const users = await PrismaService.main.user.findMany(); // 1 query
for (const user of users) {
// N additional queries
const profile = await PrismaService.main.userProfile.findUnique({
where: { userId: user.id },
});
}Solution 1: Use include
// ✅ Single query with include
const users = await PrismaService.main.user.findMany({
include: { profile: true },
});
for (const user of users) {
console.log(user.profile.bio);
}Solution 2: Batch Query
// ✅ Batch query
const users = await PrismaService.main.user.findMany();
const userIds = users.map(u => u.id);
const profiles = await PrismaService.main.userProfile.findMany({
where: { userId: { in: userIds } },
});
const profileMap = new Map(profiles.map(p => [p.userId, p]));Relations
One-to-Many
// Get user with posts
const user = await PrismaService.main.user.findUnique({
where: { id: userId },
include: {
posts: {
where: { published: true },
orderBy: { createdAt: 'desc' },
take: 10,
},
},
});Nested Writes
// Create user with profile
const user = await PrismaService.main.user.create({
data: {
email: 'user@example.com',
name: 'John Doe',
profile: {
create: {
bio: 'Developer',
avatar: 'avatar.jpg',
},
},
},
include: { profile: true },
});
// Update with nested updates
const user = await PrismaService.main.user.update({
where: { id: userId },
data: {
name: 'Jane Doe',
profile: {
update: { bio: 'Senior developer' },
},
},
});Error Handling
Prisma Error Codes
import { Prisma } from '@prisma/client';
try {
await PrismaService.main.user.create({
data: { email: 'user@example.com' },
});
} catch (error) {
if (error instanceof Prisma.PrismaClientKnownRequestError) {
// P2002: Unique constraint violation
if (error.code === 'P2002') {
throw new ConflictError('Email already exists');
}
// P2003: Foreign key constraint failed
if (error.code === 'P2003') {
throw new ValidationError('Invalid reference');
}
// P2025: Record not found
if (error.code === 'P2025') {
throw new NotFoundError('Record not found');
}
}
Sentry.captureException(error);
throw error;
}Common Error Codes
| Code | Meaning |
|---|---|
| P2002 | Unique constraint violation |
| P2003 | Foreign key constraint failed |
| P2025 | Record not found |
| P2014 | Relation violation |
Advanced Patterns
Aggregations
// Count
const count = await PrismaService.main.user.count({
where: { isActive: true },
});
// Aggregate
const stats = await PrismaService.main.post.aggregate({
_count: true,
_avg: { views: true },
_sum: { likes: true },
where: { published: true },
});
// Group by
const postsByAuthor = await PrismaService.main.post.groupBy({
by: ['authorId'],
_count: { id: true },
});Upsert
// Update if exists, create if not
const user = await PrismaService.main.user.upsert({
where: { email: 'user@example.com' },
update: { lastLogin: new Date() },
create: {
email: 'user@example.com',
name: 'John Doe',
},
});TypeScript Patterns
import type { User, Prisma } from '@prisma/client';
// Create input type
const createUser = async (data: Prisma.UserCreateInput): Promise<User> => {
return PrismaService.main.user.create({ data });
};
// Include type
type UserWithProfile = Prisma.UserGetPayload<{
include: { profile: true };
}>;
const user: UserWithProfile = await PrismaService.main.user.findUnique({
where: { id },
include: { profile: true },
});Best Practices
- Always Use PrismaService.main - Never create new PrismaClient instances
- Use Repositories for Complex Queries - Keep data access organized
- Select Only Needed Fields - Improve performance with select
- Prevent N+1 Queries - Use include or batch queries
- Use Transactions - Ensure atomicity for multi-step operations
- Handle Errors - Check for specific Prisma error codes
Related Skills:
- backend-dev-guidelines - Complete backend architecture guide
- nodejs - Core Node.js patterns and async handling
- express - Express.js routing and middleware