name: prisma_orm
router_kit: FullStackKit
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.
metadata:
skillport:
category: auto-healed
tags: [big data, cleaning, client, csv, data analysis, data engineering, data science, database, etl pipelines, export, import, json, machine learning basics, migration, migrations, nosql, numpy, pandas, prisma orm, python data stack, query optimization, reporting, schema, schema design, sql, statistics, transformation, typescript, visualization]

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: '[email protected]',
        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: '[email protected]', 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: '[email protected]',
        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: '[email protected]' },
    });
} 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: '[email protected]' },
    update: { lastLogin: new Date() },
    create: {
        email: '[email protected]',
        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
Prisma ORM v1.1 - Enhanced

🔄 Workflow

Source: Prisma Best Practices & The Guild - ORM Patterns

Phase 1: Schema First Design

• [ ] Modeling: Use schema.prisma file as "Source of Truth". Define relations (1-1, 1-n, m-n) and indexes (@index) here.
• [ ] Migration: Always make DB changes with prisma migrate dev, never run manual SQL (Drift occurs).
• [ ] Generators: Prevent code repetition by using generators like zod-prisma or prisma-nestjs-graphql besides prisma-client-js.

Phase 2: Query Implementation

• [ ] Selection: Never do findMany() (aka SELECT *). Always fetch only needed fields with select:.
• [ ] Filtering: Do not put user inputs directly into where, pass them through validation. Optimize filters for index usage.
• [ ] Relations: Be careful when using include: (Nested query performance). Use fluent api or raw query if necessary.

Phase 3: Optimization & Safety

• [ ] N+1 Problem: Do not call findUnique inside a loop. Collect ids array and fetch in a single query using where: { id: { in: ids } }.
• [ ] Transactions: Wrap multi-write operations requiring consistency with $transaction([]) or $transaction(async tx => ...).
• [ ] Connection Pooling: Use Prisma Accelerate or PgBouncer to not exceed connection limit in Serverless environment (Lambda/Vercel).

Checkpoints