Prisma ORM skill for Codex

Done well means: schema.prisma states every constraint and index the queries rely on, each migration is a reviewed SQL file that is safe to run on the production table sizes, and every hot query selects only the fields it returns with a known, constant number of round trips.

Inspect before changing anything

1. Read AGENTS.md for the package manager, test command and how migrations run in CI/CD.
2. Find the Prisma major version: npx prisma -v (or package.json). Prisma 6 and 7 differ in config, generator and client construction (table below). Never mix the two styles.
3. Locate prisma.config.ts (required in 7, optional in 6.x) and schema.prisma (or a prisma/schema/ folder of multiple .prisma files).
4. Read prisma/migrations/ and migration_lock.toml to learn the provider and history.
5. Check how the client is instantiated (one shared module, not one client per request).

Concern	Prisma 6	Prisma 7
Generator	prisma-client-js into node_modules (or prisma-client preview)	provider = "prisma-client" with required output
Import	import { PrismaClient } from "@prisma/client"	import { PrismaClient } from "./generated/prisma/client"
Connection URL	url = env("DATABASE_URL") in datasource	datasource.url in prisma.config.ts
Client constructor	URL from schema; adapters optional	Driver adapter required, e.g. @prisma/adapter-pg
.env loading	Automatic	Manual (import "dotenv/config"), except under Bun
Middleware	$use (deprecated)	Removed: use $extends query extensions
Seeding	Runs after migrate dev / migrate reset	Run prisma db seed explicitly
migrate dev / db push	Also run generate	Do not generate: run prisma generate yourself
MongoDB	Supported	Not supported yet: stay on 6

Prisma 7 minimums: Node 20.19+ and TypeScript 5.4+. It ships as ESM.

Prisma 7 setup

// prisma.config.ts (project root)
import "dotenv/config";
import { defineConfig, env } from "prisma/config";

export default defineConfig({
  schema: "prisma/schema.prisma",
  migrations: { path: "prisma/migrations", seed: "tsx prisma/seed.ts" },
  datasource: { url: env("DATABASE_URL") }, // the direct (non-pooled) URL for Migrate
});

// src/db.ts
import { PrismaClient } from "../generated/prisma/client";
import { PrismaPg } from "@prisma/adapter-pg";

const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL, max: 10 });
const globalForPrisma = globalThis as unknown as { prisma?: PrismaClient };
export const prisma = globalForPrisma.prisma ?? new PrismaClient({ adapter });
if (process.env.NODE_ENV !== "production") globalForPrisma.prisma = prisma;

Pool size and timeouts now come from the driver (pg has no connect timeout by default). Set them on the adapter instead of the old connection_limit URL parameter.

Schema rules

model Order {
  id         String      @id @default(uuid()) @db.Uuid
  customerId String      @map("customer_id") @db.Uuid
  customer   Customer    @relation(fields: [customerId], references: [id], onDelete: Restrict)
  status     OrderStatus @default(PENDING)
  totalCents Int         @map("total_cents")
  createdAt  DateTime    @default(now()) @map("created_at") @db.Timestamptz(6)
  items      OrderItem[]

  @@index([customerId, createdAt(sort: Desc)])
  @@map("orders")
}

• Add @@index on every foreign key scalar you filter or join on. Postgres does not index FKs automatically; with relationMode = "prisma" (PlanetScale-style) nothing does.
• Set onDelete explicitly on each relation. The default for required relations is Restrict; Cascade on a parent with millions of children is a long, locking delete.
• Use @map/@@map to keep snake_case tables with camelCase fields; decide once per repo.
• Money: integer minor units or Decimal @db.Decimal(12,2), never Float.
• DateTime on Postgres maps to timestamp(3) without time zone; prefer @db.Timestamptz(6).
• Enums: adding a value is cheap; renaming or removing one is a migration that needs care.
• Compound uniqueness (@@unique([tenantId, email])) is what enables upsert and findUnique on those fields; the constraint is the real guard against duplicates.

Migration workflow

• Edit schema.prisma.
• npx prisma migrate dev --create-only --name add_order_status_index to generate SQL without applying.
• Read the generated migration.sql. Hand-edit when needed (below).
• npx prisma migrate dev to apply locally; npx prisma generate on Prisma 7.
• Run tests, then commit schema and the migration folder together.
• Production and CI use only npx prisma migrate deploy.

Hand-edit the SQL when:

• Index on a large Postgres table: replace with CREATE INDEX CONCURRENTLY ... and put it in its own migration file containing only that statement. A multi-statement file is sent as one implicit transaction, and CONCURRENTLY fails inside a transaction block.
• Column rename: Prisma emits DROP + ADD (data loss). Rewrite as ALTER TABLE ... RENAME COLUMN.
• New NOT NULL column on existing rows: add nullable, backfill, then set NOT NULL in a later migration (expand/contract; see $zero-downtime-migrations).

Commands that touch data need explicit user confirmation before running anywhere except a disposable local database: prisma migrate reset (drops the database), prisma db push --accept-data-loss, and migrate dev when it reports drift and offers to reset.

Drift and history repair:

npx prisma migrate status                        # applied vs pending vs failed
npx prisma migrate resolve --rolled-back 20250101_x   # after fixing a failed migration by hand
npx prisma migrate resolve --applied 0_init      # baseline an existing database
npx prisma migrate diff --from-migrations prisma/migrations --to-schema prisma/schema.prisma --script

db push is for prototypes and throwaway branches only; it writes no migration history.

Query patterns

// Return only what the caller needs. select and include cannot be combined at one level.
const orders = await prisma.order.findMany({
  where: { customerId, status: { in: ["PENDING", "PAID"] } },
  select: {
    id: true, totalCents: true, createdAt: true,
    items: { select: { sku: true, qty: true } },
    _count: { select: { items: true } },
  },
  orderBy: [{ createdAt: "desc" }, { id: "desc" }],
  take: 50,
  cursor: lastId ? { id: lastId } : undefined,
  skip: lastId ? 1 : 0,
});

• Fetch relations in the same call (include/nested select), never findUnique in a loop.
• relationLoadStrategy: "join" (one SQL query with JSON aggregation) versus "query" (one query per relation level). On versions where it is still a preview, enable the relationJoins preview feature. Measure both for deep or wide includes.
• Offset pagination (skip: 10000) scans and discards rows; use cursor pagination with a unique, indexed sort key.
• Bulk work: createMany (add skipDuplicates where supported), createManyAndReturn, updateMany, deleteMany. Chunk to a few thousand rows per call.
• Counting and sums: _count, aggregate, groupBy push work to SQL.

Transactions

await prisma.$transaction(async (tx) => {
  const acct = await tx.account.update({
    where: { id: from }, data: { balanceCents: { decrement: amount } },
  });
  if (acct.balanceCents < 0) throw new Error("insufficient funds"); // rolls back
  await tx.account.update({ where: { id: to }, data: { balanceCents: { increment: amount } } });
}, { isolationLevel: "Serializable", timeout: 10_000 });

• Keep interactive transactions short: no HTTP calls or queue publishes inside.
• Use atomic increment/decrement instead of read-modify-write.
• With Serializable, retry on P2034 (write conflict or deadlock) with backoff.
• The array form $transaction([a, b]) runs independent writes atomically with fewer round trips.

Raw SQL

const rows = await prisma.$queryRaw<{ day: Date; n: bigint }[]>`
  SELECT date_trunc('day', created_at) AS day, count(*) AS n
  FROM orders WHERE customer_id = ${customerId}::uuid GROUP BY 1 ORDER BY 1`;

The tagged template parameterizes values. $queryRawUnsafe with string concatenation is SQL injection; never pass user input to it. For reusable typed SQL, use TypedSQL (prisma/sql/*.sql plus prisma generate --sql) where the project already enables it. count(*) returns bigint: convert before JSON.stringify.

Diagnosing slow Prisma code

1. Log queries with timing: new PrismaClient({ adapter, log: [{ emit: "event", level: "query" }] }) then prisma.$on("query", (e) => console.log(e.duration, e.query)).
2. Count queries per request. More than a handful that grow with page size means N+1 ($n-plus-one-queries).
3. Copy the slowest SQL with its parameters and run EXPLAIN (ANALYZE, BUFFERS) in psql; fix with an index in the schema ($postgres-indexing).
4. Too many connections in serverless: one client per instance, small adapter pool, and a pooled URL at runtime with the direct URL kept for Migrate ($serverless-postgres).

Gotchas

• Forgetting prisma generate after a schema change (Prisma 7 no longer runs it for you) gives stale types that compile against the old schema.
• Prisma 7 reads no .env automatically: a missing import "dotenv/config" surfaces as an undefined connection string.
• Running migrate dev against a shared or production database can propose a reset. Only migrate deploy belongs outside local development.
• Editing an already-applied migration file changes its checksum and causes drift errors everywhere. Write a new migration instead.
• updateMany/deleteMany with an empty where: {} affects every row. Guard filters built from optional input.
• findFirst without orderBy returns an arbitrary row.
• Query extensions that call prisma (not the passed query) recurse or escape the transaction.

Verification

• npx prisma validate and npx prisma format pass.
• npx prisma migrate diff --from-migrations prisma/migrations --to-schema prisma/schema.prisma --exit-code exits 0 (schema and migrations agree); this needs a shadow database URL configured.
• npx prisma migrate deploy on a fresh database applies the full history.
• Typecheck and tests pass; logged query count for the changed endpoint is constant.

Output

When delivering a schema or migration change, report:

**Schema change:** <models/fields/indexes>
**Migration:** prisma/migrations/<timestamp_name>/migration.sql (hand-edited: yes/no, why)
**Locking risk:** <table, approximate rows, statements that lock, mitigation>
**Deploy:** prisma migrate deploy; backfill or follow-up migration needed: <yes/no>
**Verified:** validate, migrate diff, tests, query count before/after