Prisma ORM skill for Claude Code

Prisma work is done well when the schema maps to the database types you actually want (not Prisma’s defaults), every migration’s SQL has been read before it runs, queries fetch only the fields and relations the caller uses, and multi-step writes are atomic. Prisma generates SQL for you; you are still responsible for that SQL.

First, identify the setup

npx prisma --version                 # CLI + client versions; must match
cat package.json | grep -E '"(prisma|@prisma/[a-z-]+)"'
ls prisma/ prisma.config.ts 2>/dev/null

Version-sensitive facts (check against the installed major before applying):

• Prisma 7 (late 2025) defaults to the Rust-free client: the prisma-client generator with a required output path, a driver adapter passed to PrismaClient (e.g. @prisma/adapter-pg), connection URL configured in prisma.config.ts, and no automatic .env loading. Middleware ($use) is gone; use client extensions.
• Prisma 5/6 use the prisma-client-js generator and the url = env("DATABASE_URL") datasource field; driver adapters and the query-compiler engine were opt-in preview features in late 6.x.

Follow the project’s existing generator and import paths. Do not mix @prisma/client imports with a custom generated output path.

// Prisma 7 style client setup (Postgres)
import { PrismaClient } from "./generated/prisma/client";
import { PrismaPg } from "@prisma/adapter-pg";

const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL });
export const prisma = new PrismaClient({ adapter });

Instantiate one client per process (a module-level singleton). In Next.js dev, cache it on globalThis to survive hot reload.

Schema

model User {
  id        String   @id @default(uuid(7)) @db.Uuid
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now()) @map("created_at") @db.Timestamptz(3)
  updatedAt DateTime @updatedAt @map("updated_at") @db.Timestamptz(3)

  @@map("users")
}

model Post {
  id          BigInt   @id @default(autoincrement())
  authorId    String   @map("author_id") @db.Uuid
  author      User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  title       String
  status      PostStatus @default(DRAFT)
  priceCents  Int      @map("price_cents")
  publishedAt DateTime? @map("published_at") @db.Timestamptz(3)

  @@index([authorId])
  @@index([status, publishedAt(sort: Desc)])
  @@map("posts")
}

enum PostStatus {
  DRAFT
  PUBLISHED
  ARCHIVED
}

Rules:

• On Postgres, DateTime defaults to timestamp(3) without time zone. Add @db.Timestamptz(3) to every DateTime.
• Map to snake_case tables/columns with @@map / @map so raw SQL and other tools stay sane; keep camelCase in TypeScript.
• Index every relation scalar (authorId). Postgres does not index FK columns, and with relationMode = "prisma" there are no FKs at all, so indexes are mandatory.
• Always set onDelete explicitly on required relations. Decide Cascade vs Restrict deliberately; the default for required relations is Restrict.
• Money: Int cents or Decimal @db.Decimal(19, 4), never Float.
• Prisma enums become native Postgres enums; removing a value later needs a migration that rewrites the type. Use a String + CHECK (added in SQL) when values churn.
• Run npx prisma format and npx prisma validate after edits.

Migrations

Situation	Command
Local development: change schema, create + apply migration	npx prisma migrate dev --name add_post_status
Need to edit SQL before applying (CONCURRENTLY, backfill, data fixes)	npx prisma migrate dev --create-only --name x, edit migration.sql, then npx prisma migrate dev
CI / staging / production	npx prisma migrate deploy (applies pending, never generates or resets)
Check state	npx prisma migrate status
Migration failed in prod and was fixed manually	npx prisma migrate resolve --applied <migration_name> or --rolled-back <migration_name>
Existing DB adopting Prisma Migrate (baseline)	Generate 0_init SQL with prisma migrate diff from empty to the current schema, then prisma migrate resolve --applied 0_init
Prototyping a throwaway schema	npx prisma db push (no migration history; never on shared/prod DBs)
Introspect an existing DB	npx prisma db pull

Hard rules:

• Never run migrate dev or migrate reset against a shared, staging, or production database: migrate dev can prompt to reset (drop all data) on drift. migrate reset is destructive and needs explicit user confirmation even locally if the DB has data the user cares about.
• Read every generated migration.sql before applying. Look for: DROP COLUMN (data loss on renames, Prisma cannot detect renames), ALTER COLUMN ... TYPE (table rewrite), CREATE INDEX without CONCURRENTLY on big tables, NOT NULL added to populated columns.
• For renames, edit the generated SQL to ALTER TABLE ... RENAME COLUMN instead of the drop/add Prisma emits, or follow expand/contract (see schema-migrations).
• CREATE INDEX CONCURRENTLY cannot run in a transaction block. If Prisma’s run fails with that error, keep the statement alone in its own migration, or run it out-of-band and mark the migration applied with migrate resolve --applied.
• Never edit a migration that has already been applied anywhere; add a new one.
• Commit prisma/migrations/ including migration_lock.toml.
• Drift detection in CI: use prisma migrate diff between the migrations directory and the schema, failing if the diff is non-empty. Flag names differ across versions; check npx prisma migrate diff --help.

Query performance

Problem	Fix
Fetching whole rows for a list view	select: { id: true, title: true } instead of default all scalars
Deep include producing many queries or huge payloads	select nested fields; on supported versions try relationLoadStrategy: "join" (single query with LATERAL joins) and compare
Loop of findUnique / findFirst per item (N+1)	One findMany({ where: { id: { in: ids } } }) and map in memory; findUnique calls in the same tick are auto-batched, findFirst are not
skip: 10000 pagination	Cursor pagination: cursor: { id: lastId }, skip: 1, take: 20, orderBy: { id: "asc" }
Counting for every page	Count once, or estimate; _count in select for relation counts
Bulk inserts in a loop	createMany({ data, skipDuplicates: true }); createManyAndReturn when you need rows back (Postgres, CockroachDB, SQLite)
Filtering on JSON paths in hot queries	Promote to a column with an index
Case-insensitive search	mode: "insensitive" (Postgres ILIKE) plus a trigram or lower() index added in SQL

Measure, do not guess:

const prisma = new PrismaClient({
  adapter,
  log: [{ emit: "event", level: "query" }],
});
prisma.$on("query", (e) => {
  if (e.duration > 100) console.warn(`${e.duration}ms ${e.query}`);
});

Take the logged SQL to EXPLAIN (ANALYZE, BUFFERS) (see sql-query-tuning).

Transactions

// Independent writes that must all succeed: sequential (batch) transaction
await prisma.$transaction([
  prisma.account.update({ where: { id: from }, data: { balance: { decrement: amt } } }),
  prisma.account.update({ where: { id: to },   data: { balance: { increment: amt } } }),
]);

// Logic between reads and writes: interactive transaction
await prisma.$transaction(
  async (tx) => {
    const acct = await tx.account.findUniqueOrThrow({ where: { id: from } });
    if (acct.balance < amt) throw new Error("insufficient funds");   // throws -> rollback
    await tx.account.update({ where: { id: from }, data: { balance: { decrement: amt } } });
    await tx.ledger.create({ data: { accountId: from, amount: -amt } });
  },
  { isolationLevel: "Serializable", maxWait: 5000, timeout: 10000 },
);

• Interactive transactions hold a connection and DB locks for their whole duration. Never call external APIs, send emails, or await user-facing work inside one.
• Default interactive timeout is 5 s and maxWait 2 s; raise deliberately, not reflexively.
• Use the tx client inside the callback. Calls on the outer prisma run outside the transaction.
• With Serializable, retry on serialization failures (Prisma error P2034) with backoff.
• Prefer atomic operations (increment, conditional updateMany) over read-modify-write. Optimistic concurrency:

const { count } = await prisma.doc.updateMany({
  where: { id, version },
  data: { body, version: { increment: 1 } },
});
if (count === 0) throw new ConflictError();

Raw SQL

• prisma.$queryRaw / $executeRaw with tagged templates are parameterized. Build dynamic fragments with Prisma.sql, Prisma.join, Prisma.raw (the last only for trusted identifiers).
• Never pass user input to $queryRawUnsafe / $executeRawUnsafe.
• For complex reporting queries, TypedSQL (.sql files under prisma/sql, enabled via the typedSql preview feature on versions that require it) gives typed results.

Connections and serverless

• Each serverless instance opens its own pool. Put a pooler in front (PgBouncer in transaction mode, Supabase/Neon poolers, Prisma Accelerate) and keep per-instance pool size small (1-5).
• With driver adapters, pool size is configured on the adapter/driver (e.g. pg Pool max). With the legacy engine, use connection_limit and pool_timeout URL params, and pgbouncer=true behind PgBouncer transaction mode.
• Migrations need a direct (non-pooled) connection; configure a separate direct URL for the CLI.

Gotchas

• BigInt fields break JSON.stringify; convert at the API boundary.
• Decimal values are Decimal.js objects, not numbers; do not compare with ===.
• upsert is not a native ON CONFLICT in every case; under concurrency it can throw unique violations (P2002). Catch and retry, or use raw INSERT ... ON CONFLICT.
• deleteMany({}) with an accidentally empty filter deletes everything. Guard filters built from optional inputs.
• Implicit many-to-many tables (_CategoryToPost) are hard to extend later; use an explicit join model when the relation may get attributes.
• prisma generate must run after every schema change and in CI/build (postinstall or build script); stale clients cause “unknown field” runtime errors.

Verification

1. npx prisma validate && npx prisma format --check (or format then confirm no diff).
2. npx prisma migrate status shows no pending or failed migrations; generated SQL reviewed and attached to the PR.
3. Type-check the app (tsc --noEmit) after prisma generate.
4. Query-log the changed endpoints: number of queries per request and their durations match expectations; EXPLAIN anything over 100 ms.