prisma

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&#x3C;User | null> {
    return PrismaService.main.user.findUnique({
        where: { email },
    });
}

async findActive(): Promise&#x3C;User[]> {
    return PrismaService.main.user.findMany({
        where: { isActive: true },
        orderBy: { createdAt: 'desc' },
    });
}

async create(data: Prisma.UserCreateInput): Promise&#x3C;User> {
    return PrismaService.main.user.create({ data });
}

async update(id: string, data: Prisma.UserUpdateInput): Promise&#x3C;User> {
    return PrismaService.main.user.update({ where: { id }, data });
}

async delete(id: string): Promise&#x3C;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&#x3C;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

Safety Notice

Copy this and send it to your AI assistant to learn

Source Transparency

Related Skills

tailwindcss

shadcn

nextjs

express