Prisma ORM patterns including Prisma Client usage, queries, mutations, relations, transactions, and schema management. Use when working with Prisma database operations or schema definitions.
apm install @squirrelfishcityhall150/prisma
[](https://apm-p1ls2dz87-atlamors-projects.vercel.app/packages/@squirrelfishcityhall150/prisma)---
name: prisma
description: Prisma ORM patterns including Prisma Client usage, queries, mutations, relations, transactions, and schema management. Use when working with Prisma database operations or schema definitions.
---
# 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
```typescript
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
```typescript
// 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
```typescript
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
```typescript
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
```typescript
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
```typescript
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
```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,
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
```typescript
// ❌ 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
```typescript
// ❌ 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
```typescript
// ✅ 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
```typescript
// ✅ 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
```typescript
// 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
```typescript
// 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
```typescript
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
```typescript
// 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
```typescript
// 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
```typescript
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
1. **Always Use PrismaService.main** - Never create new PrismaClient instances
2. **Use Repositories for Complex Queries** - Keep data access organized
3. **Select Only Needed Fields** - Improve performance with select
4. **Prevent N+1 Queries** - Use include or batch queries
5. **Use Transactions** - Ensure atomicity for multi-step operations
6. **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