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

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

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