# Database Patterns - Prisma Best Practices
Complete guide to database access patterns using Prisma in backend microservices.
## Table of Contents
- [PrismaService Usage](#prismaservice-usage)
- [Repository Pattern](#repository-pattern)
- [Transaction Patterns](#transaction-patterns)
- [Query Optimization](#query-optimization)
- [N+1 Query Prevention](#n1-query-prevention)
- [Error Handling](#error-handling)
---
## PrismaService Usage
### Basic Pattern
```typescript
import { PrismaService } from '@project-lifecycle-portal/database';
// Always use PrismaService.main
const users = await PrismaService.main.user.findMany();
```
### Check Availability
```typescript
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
```typescript
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
```typescript
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
```typescript
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
```typescript
// ❌ 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
```typescript
// ❌ 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
```typescript
// ❌ 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
```typescript
// ✅ 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
```typescript
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:**
- [SKILL.md](SKILL.md)
- [services-and-repositories.md](services-and-repositories.md)
- [async-and-errors.md](async-and-errors.md)
