4.8 KiB
4.8 KiB
Database Patterns - Prisma Best Practices
Complete guide to database access patterns using Prisma in backend microservices.
Table of Contents
- PrismaService Usage
- Repository Pattern
- Transaction Patterns
- Query Optimization
- N+1 Query Prevention
- Error Handling
PrismaService Usage
Basic Pattern
import { PrismaService } from '@project-lifecycle-portal/database';
// Always use PrismaService.main
const users = await PrismaService.main.user.findMany();
Check Availability
if (!PrismaService.isAvailable) {
throw new Error('Prisma client not initialized');
}
const user = await PrismaService.main.user.findUnique({ where: { id } });
Repository Pattern
Why Use Repositories
✅ Use repositories when:
- Complex queries with joins/includes
- Query used in multiple places
- Need caching layer
- Want to mock for testing
❌ Skip repositories for:
- Simple one-off queries
- Prototyping (can refactor later)
Repository Template
export class UserRepository {
async findById(id: string): Promise<User | null> {
return PrismaService.main.user.findUnique({
where: { id },
include: { profile: true },
});
}
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 });
}
}
Transaction Patterns
Simple Transaction
const result = await PrismaService.main.$transaction(async (tx) => {
const user = await tx.user.create({ data: userData });
const profile = await tx.userProfile.create({ data: { userId: user.id } });
return { user, profile };
});
Interactive Transaction
const result = await PrismaService.main.$transaction(
async (tx) => {
const user = await tx.user.findUnique({ where: { id } });
if (!user) throw new Error('User not found');
return await tx.user.update({
where: { id },
data: { lastLogin: new Date() },
});
},
{
maxWait: 5000,
timeout: 10000,
}
);
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,
profile: { select: { firstName: true, lastName: true } },
},
});
Use include Carefully
// ❌ Excessive includes
const user = await PrismaService.main.user.findUnique({
where: { id },
include: {
profile: true,
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 Queries
// ❌ N+1 Query Problem
const users = await PrismaService.main.user.findMany(); // 1 query
for (const user of users) {
// N queries (one per user)
const profile = await PrismaService.main.userProfile.findUnique({
where: { userId: user.id },
});
}
Solution: Use include or Batching
// ✅ Single query with include
const users = await PrismaService.main.user.findMany({
include: { profile: true },
});
// ✅ Or batch query
const userIds = users.map(u => u.id);
const profiles = await PrismaService.main.userProfile.findMany({
where: { userId: { in: userIds } },
});
Error Handling
Prisma Error Types
import { Prisma } from '@prisma/client';
try {
await PrismaService.main.user.create({ data });
} catch (error) {
if (error instanceof Prisma.PrismaClientKnownRequestError) {
// Unique constraint violation
if (error.code === 'P2002') {
throw new ConflictError('Email already exists');
}
// Foreign key constraint
if (error.code === 'P2003') {
throw new ValidationError('Invalid reference');
}
// Record not found
if (error.code === 'P2025') {
throw new NotFoundError('Record not found');
}
}
// Unknown error
Sentry.captureException(error);
throw error;
}
Related Files: