api-database-prisma

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 T | null forcing null handling, upsert is atomic

See [examples/core.md](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](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: "alice@example.com",
    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 log