/api-database-prisma

name: api-database-prisma
description: Prisma ORM, type-safe queries, migrations, relations

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](examples/core.md) - Singleton setup, CRUD, filtering, pagination
• [examples/relations.md](examples/relations.md) - Relational queries, includes, N+1 prevention
• [examples/transactions.md](examples/transactions.md) - Atomic operations, interactive transactions, error handling
• [reference.md](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](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: "alice@example.com" },
});

// 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: "alice@example.com" },
  create: { email: "alice@example.com", name: "Alice" },
  update: { name: "Alice Updated" },
});

**Why good:** Type-safe operations catch errors at compile time, `findUnique` returns