Prisma ORM Patterns with Claude Code: N+1 Prevention and Transaction Design

#claudecode #prisma #typescript #database

Prisma pairs well with TypeScript, but N+1 queries, transaction design, and migration strategy mistakes cost you in production. Claude Code generates correct patterns when you define the rules.

CLAUDE.md for Prisma Standards

## Prisma Rules

### Basic setup
- PrismaClient singleton: src/lib/prisma.ts (single source of truth)
- Schema: prisma/schema.prisma
- Migrations: always `prisma migrate deploy` (no raw SQL in production)

### Query design
- No N+1: use include to fetch relations (no queries inside loops)
- select only needed fields (no SELECT * equivalent)
- Avoid count on large tables (use _count or counter caches)

### Transactions
- Multiple table updates: always use transactions
- Use `prisma.$transaction()`
- Timeout: 10 seconds

### Migrations
- Column deletion: 3-phase (deprecate → update code → delete)
- Adding foreign keys causes locking — plan for downtime

### Security
- Never pass user input directly to $queryRaw (SQL injection risk)
- Trust Prisma's type safety for standard operations

Generating the Prisma Singleton

Generate a PrismaClient singleton.

Requirements:
- Development: prevent Client proliferation on hot reload (use global variable)
- Production: normal instance
- Logging: development = query+error, production = error only
- Connection pool: DATABASE_URL includes ?connection_limit=10

Save to: src/lib/prisma.ts

Generated:

// src/lib/prisma.ts
import { PrismaClient } from '@prisma/client';

const prismaClientSingleton = () => {
  return new PrismaClient({
    log: process.env.NODE_ENV === 'development'
      ? ['query', 'error', 'warn']
      : ['error'],
  });
};

type PrismaClientSingleton = ReturnType<typeof prismaClientSingleton>;

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClientSingleton | undefined;
};

export const prisma = globalForPrisma.prisma ?? prismaClientSingleton();

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}

Fixing N+1 Queries

Fix the N+1 problem in this code:

// PROBLEM: separate query per post
const posts = await prisma.post.findMany();
for (const post of posts) {
  post.author = await prisma.user.findUnique({ where: { id: post.authorId } });
  post.comments = await prisma.comment.findMany({ where: { postId: post.id } });
}

Requirements:
- Use include to fetch in one query
- Select only needed fields
- Add cursor-based pagination (take + cursor)

Fixed:

const posts = await prisma.post.findMany({
  select: {
    id: true,
    title: true,
    publishedAt: true,
    author: {
      select: { id: true, name: true },
    },
    comments: {
      select: { id: true, content: true, createdAt: true },
      orderBy: { createdAt: 'desc' },
      take: 5,
    },
    _count: { select: { comments: true } }, // Total count without loading all
  },
  where: { publishedAt: { not: null } },
  orderBy: { publishedAt: 'desc' },
  take: 20,
  ...(cursor && { skip: 1, cursor: { id: cursor } }),
});

Transaction Design

Generate an order processing transaction.

Operations:
1. Create Order
2. Bulk create OrderItems
3. Decrement stock for each item (optimistic lock)
4. Deduct user points

Requirements:
- All or nothing — rollback on any failure
- Timeout: 15 seconds
- Include stock validation in the transaction

Generated:

const result = await prisma.$transaction(async (tx) => {
  // 1. Create order
  const order = await tx.order.create({
    data: { userId, status: 'pending', total },
  });

  // 2. Bulk create items
  await tx.orderItem.createMany({
    data: items.map(item => ({
      orderId: order.id,
      productId: item.productId,
      quantity: item.quantity,
      price: item.price,
    })),
  });

  // 3. Decrement stock with optimistic lock
  for (const item of items) {
    const updated = await tx.product.updateMany({
      where: {
        id: item.productId,
        stock: { gte: item.quantity }, // Only update if enough stock
      },
      data: { stock: { decrement: item.quantity } },
    });
    if (updated.count === 0) {
      throw new Error(`Insufficient stock: ${item.productId}`);
    }
  }

  // 4. Deduct points
  await tx.user.update({
    where: { id: userId },
    data: { points: { decrement: pointsToUse } },
  });

  return order;
}, { timeout: 15000 });

Safe Migration Strategy

Generate a safe migration plan for removing a column from a large table.

Column: users.legacy_profile_url (no longer used)
Table size: 10M rows

Requirements:
- Zero downtime
- 3-phase approach: deprecate → remove from code → delete column
- Include rollback procedure

3-phase approach prevents the table lock that dropping a column on a large table causes.