Database with Prisma ORM

Quick Guide: Use Prisma ORM for type-safe database queries with auto-generated TypeScript types. Schema-first design with declarative migrations. Use include for relations, $transaction for atomic operations. Singleton pattern required in development to avoid connection exhaustion. Always use tx (not prisma) inside interactive transaction callbacks.

<critical_requirements>

CRITICAL: Before Using This Skill

All code must follow project conventions in CLAUDE.md (kebab-case, named exports, import ordering, import type, named constants)

(You MUST use the singleton pattern for PrismaClient in development to prevent connection exhaustion from hot reloading)

(You MUST use `tx` parameter (NOT `prisma`) inside interactive transaction callbacks to ensure atomicity)

(You MUST use `include` or nested `select` for relational queries - avoid N+1 by fetching relations in the same query)

(You MUST define `@relation` with explicit `fields` and `references` for all foreign key relationships)

</critical_requirements>

Auto-detection: prisma, @prisma/client, PrismaClient, prisma.schema, prisma migrate, findUnique, findMany, include, $transaction

When to use:

• Type-safe database queries with auto-generated TypeScript types
• Schema-first development with declarative migrations
• Applications requiring strong relational data modeling
• Rapid prototyping with Prisma Studio GUI

When NOT to use:

• Need raw SQL performance for complex queries (Prisma adds overhead)
• Edge/serverless requiring minimal cold start (consider lighter ORMs)
• Non-TypeScript projects (lose primary benefit)
• Need fine-grained control over generated SQL

Key patterns covered:

• PrismaClient singleton (development hot reload safety)
• CRUD operations with type-safe filters and pagination
• Relational queries with include and nested select
• Transactions (nested writes, batch, interactive)
• Schema design (models, relations, enums, indexes)

Detailed Resources:

• examples/core.md - Singleton setup, CRUD, filtering, pagination
• examples/relations.md - Relational queries, includes, N+1 prevention
• examples/transactions.md - Atomic operations, interactive transactions, error handling
• reference.md - Decision frameworks, anti-patterns, performance

<philosophy>

Philosophy

Prisma ORM provides a declarative schema language that generates type-safe database clients. The schema serves as the single source of truth for your data model, TypeScript types, and migrations.

Core principles:

1. Schema-first design - Define models in schema.prisma, generate everything else
2. Type safety everywhere - All queries fully typed based on your schema
3. Declarative migrations - Schema changes automatically generate migration SQL
4. Intuitive API - Queries read like English (prisma.user.findMany())

</philosophy>

<patterns>

Core Patterns

Pattern 1: PrismaClient Singleton

Use singleton pattern to prevent connection pool exhaustion during development hot reloading. Without this, each hot reload creates a new PrismaClient with its own connection pool, quickly exhausting database connections.

// lib/db/client.ts
import { PrismaClient } from "@prisma/client";

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

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

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

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

Why good: globalThis persists across hot reloads, conditional logging avoids production noise

See examples/core.md for serverless connection patterns.

Pattern 2: Schema Design

Define models with relations, constraints, and defaults. The schema is the source of truth.

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  role      Role     @default(USER)
  posts     Post[]
  profile   Profile?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@map("users")
}

model Post {
  id        String   @id @default(cuid())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId  String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([authorId])
  @@map("posts")
}

Why good: cuid() for collision-resistant IDs, @updatedAt auto-tracks changes, @relation with onDelete: Cascade prevents orphans, @@index on foreign keys, @@map for snake_case DB tables with PascalCase in code

Pattern 3: CRUD with Type-Safe Filters

All queries are fully typed based on your schema. Key operations:

const DEFAULT_PAGE_SIZE = 20;
const MAX_PAGE_SIZE = 100;

// Find by unique field - returns T | null
const user = await prisma.user.findUnique({
  where: { email: "[email protected]" },
});

// Find many with filters + pagination
const users = await prisma.user.findMany({
  where: {
    role: { in: ["USER", "MODERATOR"] },
    createdAt: { gte: new Date("2024-01-01") },
  },
  orderBy: { name: "asc" },
  take: DEFAULT_PAGE_SIZE,
});

// Upsert - atomic create-or-update
const upserted = await prisma.user.upsert({
  where: { email: "[email protected]" },
  create: { email: "[email protected]", name: "Alice" },
  update: { name: "Alice Updated" },
});

Why good: Type-safe operations catch errors at compile time, findUnique returns T | null forcing null handling, upsert is atomic

See examples/core.md for complete CRUD operations, filtering, and pagination patterns.

Pattern 4: Relational Queries

Fetch related data efficiently using include or nested select to avoid N+1 queries.

// Include related records - single query
const userWithPosts = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: "desc" },
      take: 10,
    },
    profile: true,
  },
});

// Select specific fields only - smaller payload
const userSummary = await prisma.user.findUnique({
  where: { id: userId },
  select: {
    id: true,
    name: true,
    posts: { select: { id: true, title: true } },
  },
});

Why good: Single query avoids N+1, include fetches all fields, select reduces payload. Never loop queries per record — use include or select instead.

See examples/relations.md for relation filters, many-to-many, self-relations, include vs select, and N+1 anti-patterns.

Pattern 5: Transactions

Ensure atomic operations across multiple writes. Three types available:

// Nested writes - implicit transaction (cleanest for related records)
const user = await prisma.user.create({
  data: {
    email: "[email protected]",
    name: "Alice",
    profile: { create: { bio: "Developer" } },
    posts: { create: [{ title: "First Post", published: true }] },
  },
  include: { profile: true, posts: true },
});

// Interactive transaction - ALWAYS use tx, never prisma
return await prisma.$transaction(async (tx) => {
  const sender = await tx.account.update({
    where: { id: fromId },
    data: { balance: { decrement: amount } },
  });
  if (sender.balance < MINIMUM_BALANCE) throw new Error("Insufficient funds");
  return await tx.account.update({
    where: { id: toId },
    data: { balance: { increment: amount } },
  });
});

Why good: Nested writes for related records, interactive transactions enable business logic with automatic rollback. Using prisma instead of tx inside the callback bypasses transaction context.

See examples/transactions.md for batch transactions, error handling, optimistic concurrency, and transaction options.

Pattern 6: Connection Management

Handle $disconnect() on beforeExit to prevent connection leaks. For serverless environments, use a connection pooler (PgBouncer, Prisma Accelerate) via a separate DATABASE_URL_WITH_POOLER environment variable.

See examples/core.md for graceful shutdown and serverless connection patterns.

</patterns>

<red_flags>

RED FLAGS

High Priority Issues:

• Creating PrismaClient on every import - exhausts database connections during hot reload
• Using prisma instead of tx in interactive transactions - bypasses transaction context
• N+1 queries in loops - use include or select instead
• Missing @relation attributes - ambiguous foreign keys cause migration errors

Medium Priority Issues:

• No indexes on frequently filtered columns - slow queries as data grows
• Offset pagination on large tables - performance degrades linearly
• Missing onDelete cascade - orphaned records when parent deleted
• Fetching all fields with include when only some needed - use select

Gotchas & Edge Cases:

• createMany doesn't return created records (use createManyAndReturn on PostgreSQL/CockroachDB/SQLite)
• updateMany and deleteMany don't automatically update @updatedAt fields
• Implicit many-to-many tables can't have extra fields - use explicit join model
• Json fields are typed as JsonValue - need runtime validation at parse boundary
• Decimal fields return Prisma.Decimal type - convert with .toNumber()
• Interactive transactions have default 5s timeout - increase with timeout option
• Interactive transactions hold database connections - keep them short
• findFirst without orderBy returns non-deterministic results
• Enum changes require a migration to add/remove values

</red_flags>

<critical_reminders>

CRITICAL REMINDERS

All code must follow project conventions in CLAUDE.md

(You MUST use the singleton pattern for PrismaClient in development to prevent connection exhaustion from hot reloading)

(You MUST use `tx` parameter (NOT `prisma`) inside interactive transaction callbacks to ensure atomicity)

(You MUST use `include` or nested `select` for relational queries - avoid N+1 by fetching relations in the same query)

(You MUST define `@relation` with explicit `fields` and `references` for all foreign key relationships)

Failure to follow these rules will exhaust database connections, break transaction atomicity, cause N+1 performance problems, and create unclear relation definitions.

</critical_reminders>