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: 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)

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

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())

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

// BAD: New client every import
export const prisma = new PrismaClient(); // New instance on every hot reload

Why bad: Exhausts database connections (default 100 for PostgreSQL) after repeated hot reloads

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: "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 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

// BAD: N+1 query pattern
const users = await prisma.user.findMany();
for (const user of users) {
  const posts = await prisma.post.findMany({
    where: { authorId: user.id },
  }); // N additional queries!
}

Why bad: 1 query for users + N queries for posts, extremely slow at scale

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

Pattern 5: Transactions

Ensure atomic operations across multiple writes.

// Nested writes - implicit transaction
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 - complex logic with rollback
const MINIMUM_BALANCE = 0;

const transferFunds = async (fromId: string, toId: string, amount: number) => {
  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"); // Rolls back everything
    }

    return await tx.account.update({
      where: { id: toId },
      data: { balance: { increment: amount } },
    });
  });
};

Why good: Nested writes are cleanest for related records, interactive transactions enable business logic with automatic rollback

// BAD: Using prisma instead of tx
await prisma.$transaction(async (tx) => {
  await prisma.post.create({ data: { title: "Post" } }); // Uses prisma, not tx!
});

Why bad: Operations using prisma bypass transaction context, won't rollback on failure

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

Pattern 6: Connection Management

Handle connections properly for different environments.

// Graceful shutdown
process.on("beforeExit", async () => {
  await prisma.$disconnect();
});

// Serverless: use connection pooler (PgBouncer, Prisma Accelerate)
export const prisma = new PrismaClient({
  datasources: {
    db: { url: process.env.DATABASE_URL_WITH_POOLER },
  },
});

Why good: Graceful shutdown prevents connection leaks, connection pooler handles serverless connection management

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 trigger @updatedAt hooks
• 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

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.