Prisma

User preferences and memory live in ~/Clawic/data/prisma/ (see setup.md on first use, memory-template.md for the file format). If you have data at an old location (~/prisma/ or ~/clawic/prisma/), move it to ~/Clawic/data/prisma/.

When To Use

• Writing or reviewing schema.prisma: models, relations, referential actions, indexes, enums, JSON, money and time columns
• Getting a schema change into a database safely: db push vs migrate dev vs migrate deploy, renames, backfills, baselining, drift, failed migrations
• Writing Prisma Client queries: filters, pagination, nested writes, upserts, aggregation, and the raw escape hatch
• Diagnosing runtime failures by error code (P1xxx connectivity, P2xxx query, P3xxx migrate) or by symptom (slow, too many queries, pool exhausted, transaction timeout)
• Deploying Prisma: generate in CI and Docker, engine targets, serverless and edge, poolers, test databases
• Extending the client: soft delete, audit logs, tenant scoping, computed fields — and porting existing $use middleware
• Not for tuning the database server itself (EXPLAIN plans, vacuum, index internals) or hand-written SQL optimization — see Related Skills

Quick Reference

Depth on demand, by phase:

• Model — schema.md relations, referential actions, keys, indexes, types, multi-tenancy · providers.md what PostgreSQL, MySQL, SQLite, SQL Server, MongoDB and PlanetScale each refuse to do
• Change — migrations.md push vs migrate, renames, baselining, drift, zero-downtime sequences · testing.md test databases, isolation, seeding, mocking · deployment.md generate in CI and Docker, engines, serverless, edge, monorepos
• Query — queries.md filters, nested writes, upsert, pagination, aggregation · performance.md relation loading, indexes, counts, logging, payload size · transactions.md batch vs interactive, isolation, retries, optimistic locking · raw-sql.md $queryRaw, TypedSQL, safe interpolation
• Extend and type — extensions.md $extends for soft delete, audit, RLS, computed fields · typescript.md generated types, payload types, JSON, Decimal, BigInt
• Operate — connections.md pool sizing, PgBouncer, serverless, directUrl · errors.md every P-code to cause and fix

Core Rules

1. One migration command per environment, and never the other one. db push for a throwaway prototype (no history, silently drops columns to converge); migrate dev in development only (generates SQL, needs a shadow database, may reset); migrate deploy in CI and production (applies existing files, never generates, never resets). Check: grep the deploy pipeline — a migrate dev or a db push there is an incident waiting for its trigger.
2. The client is generated code, not a schema reader. Every schema edit needs npx prisma generate, and every install path needs it too (postinstall script plus an explicit step in the build). The signature failure: types compile locally, production throws "Unknown argument" or a model that exists in the schema is missing on the client, because a cached node_modules shipped a client generated from an older schema.
3. Rename with @map, never by renaming the field. Prisma diffs by name, so renaming fullName to name emits DROP COLUMN + ADD COLUMN: every row loses that value. Keep the column and rename only in Prisma (name String @map("fullName")), or hand-edit the generated migration to ALTER TABLE ... RENAME COLUMN before it is applied. Same rule for models (@@map).
4. Index the foreign key yourself on PostgreSQL. Prisma migrate creates indexes for @id and @unique only. MySQL auto-indexes FK columns; PostgreSQL does not — so @@index([authorId]) is your job, and without it a where: { authorId } filter or a cascading parent delete scans the child table. Under relationMode = "prisma" (PlanetScale and friends) there are no FK constraints at all and the index is mandatory on every relation scalar.
5. include costs one query per relation; loops cost one per row. Queries = 1 + one per distinct relation at each nesting level: findMany with three includes is 4 round trips whether it returns 10 rows or 10,000. The N+1 you actually have comes from a loop or a GraphQL resolver — with one exception: findUnique/findUniqueOrThrow calls on the same model in the same event-loop tick are batched into a single WHERE id IN (...). Nothing else batches.
6. undefined means "ignore this filter", null means "match NULL". deleteMany({ where: { tenantId: undefined } }) is a full-table delete, and findFirst({ where: { email: undefined } }) returns a stranger's row. Rule: never let a possibly-undefined variable reach a where. Validate first, or make the skip explicit with Prisma.skip under the strictUndefinedChecks preview (prisma >=5.20), which turns implicit undefined into an error.
7. An interactive transaction holds a pooled connection for its entire body. Defaults: timeout 5000 ms, maxWait 2000 ms. No HTTP calls, no queues, no user input inside it. Concurrency ceiling is the pool: with connection_limit=5, the sixth concurrent interactive transaction waits and then fails P2028 after maxWait (2 s by default) — the app looks "deadlocked" while the database is idle. Ordinary queries queueing for the same pool fail P2024 instead, at pool_timeout (10 s).
8. Size the pool against the database, not against hope. Prisma's default connection_limit is num_physical_cpus * 2 + 1 per client instance. Budget: connection_limit ≤ (max_connections − 3 reserved − other consumers) / expected instances. PostgreSQL ships max_connections = 100 with 3 reserved, so on a 4-core runtime (9 connections each) eleven instances ask for 99 against the 97 available, and the eleventh gets P1001. Serverless: 1-2 plus an external pooler (connections.md).
9. Retry only the codes that are retryable. P2034 (write conflict / deadlock) and P2024 (pool timeout) deserve a retry of the whole transaction with jitter, capped at 3 attempts; P2002 deserves exactly one retry when it came from an upsert race, and zero when the duplicate is real. Never retry P2003, P2025 or any P1012 — nothing about a second attempt changes them.

Error Codes

Codes are stable across versions; message text is not. Match on e.code after narrowing with e instanceof Prisma.PrismaClientKnownRequestError. Full catalog with causes and fixes: errors.md.

Relation Loading

• Nothing is loaded implicitly. A relation you did not include or select is undefined at runtime and absent from the type — which is why the type error and the runtime bug appear together.
• include returns the full scalar set of the relation; select inside include prunes it. On wide rows the difference is bytes over the wire per row, and it is the cheapest optimization in the list.
• select and include are mutually exclusive at the same level. Nest them instead: include: { posts: { select: { id: true, title: true } } }.
• Filtered relations (include: { posts: { where: { published: true }, take: 5 } }) push the filter into the relation query — do this instead of loading everything and filtering in JavaScript.
• Relation counts belong to the same round trip: select: { _count: { select: { posts: true } } }. A posts.length after loading every post is the same answer with the whole table in memory.
• Relation load strategy is selectable where supported (relationLoadStrategy: "join" | "query", prisma >=5.7 with the relationJoins preview on PostgreSQL and MySQL): join is one round trip with JSON aggregation, query is one query per relation with a smaller, simpler payload. Measure both on a real dataset — deep nesting favors join on a distant database, wide relations favor query. Details and current status: performance.md.

Connection Budget

Formula, applied before touching any other performance knob:

total connections = client instances × connection_limit
must satisfy: total ≤ max_connections − reserved − other consumers

• Client instances are processes, not requests: one Node server = 1; a clustered server = 1 per worker; serverless = 1 per warm sandbox, and the count is set by your traffic, not by you.
• connection_limit is a URL parameter: ?connection_limit=10&pool_timeout=20. Raising it does not create database capacity — it decides who queues where.
• Serverless without a pooler is the classic outage: every cold start opens its own pool and nothing gives them back. Use PgBouncer, a provider pooler, or Prisma Accelerate, and keep connection_limit at 1-2 (→ connections.md).
• Transaction-mode poolers require ?pgbouncer=true (Prisma stops using named prepared statements) and a separate directUrl for migrations, which need a real session.

Configuration

User-dependent variables. Defaults apply until the user states a preference; store them in ~/Clawic/data/prisma/config.yaml.

Preference areas — customizable dimensions; a stated preference is recorded in config.yaml and applied from then on:

• Tooling — package manager and runner, prisma-client-js vs the newer generator, validation library paired with Prisma (Zod, Valibot), seeding tool
• Thresholds — default page size, transaction timeout and maxWait, retry attempts and backoff, the query duration worth flagging in a review
• Conventions — model and field naming, singular vs plural tables, soft-delete field name, timestamp columns, enum vs lookup table, schema file layout (single file vs folder)
• Platform — provider and version, hosting target, region distance to the database (which decides how much round-trip count matters), monorepo layout and client output path
• Risk posture — whether migrations are applied directly or handed back as SQL for review, whether raw SQL is allowed at all, how strict the ban on deleteMany without where is
• Output format — schema plus explanation vs schema only, whether emitted queries carry the equivalent SQL in a comment, how much of the migration plan to narrate
• Work order — schema-first vs introspection-first, whether tests and seed data are updated in the same change as the migration
• Integrations — pooler and database host (Neon, Supabase, PlanetScale, RDS, Turso), Accelerate or a self-managed cache, observability stack for query logs
• Restrictions — tables Prisma must not manage (@@ignore), compliance rules that forbid raw SQL or require audit logging, columns that must never be selected by default
• Cadence — how often to re-run introspection against production, when to prune old migrations, review cycle for unused indexes

Output Gates

Before emitting a schema, a migration, or a query:

• Every relation scalar indexed (@@index) unless the provider already indexes it, and every relationMode = "prisma" relation indexed without exception?
• Every rename expressed as @map/@@map, or the generated SQL hand-edited to a real RENAME?
• The destructive step (drop column, drop table) split into a later migration, after the code that stopped using it shipped?
• onDelete/onUpdate stated explicitly on every relation instead of inherited by default?
• Money as Decimal @db.Decimal(12,2), timestamps as DateTime @db.Timestamptz(3) where the provider has it, never Float for money?
• Does every findMany have a take, and every where a value that cannot be undefined?
• Does the emitted command match the environment (migrate deploy in CI, never migrate dev or db push)?
• Is prisma generate guaranteed to run in this deployment path?

Traps

Where Experts Disagree

• cuid vs uuid vs bigint primary keys. Random v4 UUIDs fragment the B-tree and widen every secondary index; sequential integers leak volume and complicate multi-writer merges. Boundary: externally visible IDs → cuid/UUIDv7 (time-ordered, still opaque); internal high-write tables → autoincrement() bigint. Switch on measured index bloat, not on aesthetics.
• Schema-first vs introspection-first. Prisma's default is schema-first, and it is right when the application owns the database. When the database is shared with other systems, DBAs, or hand-written SQL, db pull plus baselined migrations avoids fighting for ownership — the boundary is who is allowed to change the schema, not which is more modern.
• How much raw SQL is acceptable. One camp keeps everything in the client for type safety and extension coverage; the other drops to $queryRaw at the first CTE or window function. Workable line: raw for read-only analytics and DDL-adjacent work, client for anything that writes rows other code reads back — because raw writes bypass @updatedAt, defaults, and every extension.
• Prisma Migrate vs a plain SQL migration tool. Migrate is excellent at diffing and terrible at expressing operations SQL has and Prisma does not (concurrent indexes, partitioning, triggers). Teams past a certain size run Prisma for the schema and hand-written SQL for the change; that is a legitimate configuration (migration_workflow: sql-first), not a defeat.

Related Skills

More Clawic skills, get them at https://clawic.com/skills/prisma (install if the user confirms):

• pg — the PostgreSQL server underneath: plans, vacuum, locks, replication
• sql — hand-written SQL, cross-engine portability, index and query design
• typescript — type-system depth beyond Prisma's generated types
• nodejs — process lifecycle, memory, and shutdown around the client
• nextjs — where the client lives in App Router, server actions, and build-time generation

Feedback

• If useful, star it: https://clawic.com/skills/prisma
• Latest version: https://clawic.com/skills/prisma

Part of Clawic, the verified skill library. Get this skill: https://clawic.com/skills/prisma.