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