Prisma behaviour worth knowing before you assume it

Observed on Prisma 6.19.3 against PostgreSQL 16.14 unless a section says otherwise. Nearly everything here is client/engine internals rather than documented contract, so treat the version stamp as load-bearing: re-verify with a query log before relying on any of it under a different major.

The one habit that produces this whole page: log the SQL, do not argue from the annotation. Every wrong prediction below was reasoned from a decorator's name and settled in two minutes by a statement log.

const prisma = new PrismaClient({ log: [{ emit: "event", level: "query" }] });
prisma.$on("query", (e) => console.log(e.query, e.params));
class Rollback extends Error {}
try { await prisma.$transaction(async (tx) => { /* … */ throw new Rollback(); }); }
catch (e) { if (!(e instanceof Rollback)) throw e; }

Roll back so nothing persists. In a pnpm workspace on the isolated linker the probe must live inside a package that depends on @prisma/client; the root has no hoisted copy and the import will not resolve.

1. Prisma's defaults are client-side; the column's DEFAULT is decoration

@default(now()) does not let Postgres fill the column. The value is generated in Node and bound as a parameter, even though the migration wrote DEFAULT CURRENT_TIMESTAMP into the DDL:

INSERT INTO "public"."audit_events" (…,"created_at") VALUES ($1,…,$6)
params: [… "probe.first", {}, "2026-08-30 12:56:53.357 UTC"]

Three consequences:

1. clock_timestamp() as a fix for same-transaction ties is a no-op — the default never runs, so changing it changes nothing.
2. Widening to timestamp(6) is also a no-op — a JS Date is millisecond-precision before it ever reaches the column.
3. The stored value is the application host's clock, not the database's. So "the DB was ahead of the reader" explanations for time-window bugs need rethinking when both sides read Date.now().

Measured tie rate, two create() calls back-to-back in one transaction: 17/30 (57%) land in the same millisecond, and a sole-key ORDER BY created_at DESC returned the wrong newest row ~7% of the time. A single timestamp is not a safe sort key for rows written by one action.

@default(uuid(7)) is generated by the engine with a process-global monotonic counter — which makes v7 a single-writer fix, not a global one. Measured against a scratch database:

Same-millisecond pairs across processes share the timestamp prefix and differ only in random sub-millisecond bits. So ORDER BY created_at DESC, id DESC orders a burst from one process and says nothing about a web write against a worker write. Any table two services append to — an audit log, a delivery log — needs a database-side order: clock_timestamp() or a bigserial, because the database clock is the only one every writer process shares.

@updatedAt is client-side too, and is sent on create as well as update:

INSERT INTO "public"."sections" ("id","tenant_id","course_id","term_id",
  "capacity","created_at","updated_at") VALUES (...)

That breaks a common guard. updated_at NOT NULL with no column default is a standard trick for turning a lost trigger into a loud write failure instead of silent data loss — but it only fires for a writer that omits the column, and Prisma never omits it. The guard is real only against raw SQL: seed scripts, migrations, a psql session, usually a countable handful of sites. The actual guarantee has to be a structural assertion — enumerate pg_trigger and information_schema.columns in a test — not the constraint.

The same client-side fill is why a hand-written INSERT that omits updated_at dies on null value in column "updated_at" before reaching the CHECK you are drilling; if you are drilling a refusal, that reads as a pass.

But @updatedAt does not make an upsert write. upsert({ where, create, update: {} }) against an existing row emits three SELECTs and no UPDATE, so updated_at is unchanged, although the model declares @updatedAt. Against an absent row it emits the INSERT. So a "create on first access" read path built this way stays a read. The annotation makes the opposite answer look likely, which is why it needs a query log rather than an argument.

Reading loses the microseconds too. That is the read half of consequence 2. A timestamp(6) column comes back as a JS Date, truncated to milliseconds before app code sees it, even when the database stamped it with clock_timestamp(). The same row, measured: 2026-10-30 09:00:41.27802 as created_at::text, and 2026-10-30T09:00:41.278Z through the model. A keyset cursor that carries the timestamp through JavaScript, such as created_at < c OR (created_at = c AND seq < s), therefore compares against a truncated instant. It skips (or repeats, depending on direction) every row in the boundary millisecond. Resolve the cursor inside the query, (created_at, seq) < (SELECT created_at, seq FROM t WHERE id = $1::uuid), and check that the cursor row exists first. A missing row makes the comparison NULL, which returns an empty page that reads as "the end". The red proof is rows inside one millisecond spanning a page boundary: a cursor built from date_trunc('milliseconds', …) must fail it.

@default(dbgenerated(...)) is a default, not a constraint — the field stays writable in the generated create input. See §2.

But the DDL default is still mandatory, and that is not a contradiction. prisma migrate diff --from-schema-datasource --to-schema-datamodel --exit-code reads @default(now()) as a database default. A hand-written migration that omits it exits 2 with [*] Altered column `created_at` (default changed from `None` to `Some(Now)`) — a red CI run for a column that behaves perfectly. So a new @default(now()) column needs DEFAULT CURRENT_TIMESTAMP in the DDL and gets its value from Node: the default exists to satisfy the drift gate and fires only for a raw INSERT that omits the column. Do not "simplify" it away on the grounds that it never runs.

Cross-reference: postgres-behaviour §1 for what the database would have done with the column, and §6 for how a tie picks the wrong row.

2. The generated client's types: what compiles and still fails

Checked versus unchecked create — you cannot mix them. A payload is either checked (relations only: tenant: { connect: { id } }) or unchecked (scalar FKs: tenantId). Passing tenantId and a parent-side nested create like person: { create: … } fails to typecheck with a misleading Type `{ create: … }` is not assignable to type `undefined` .

Child-side nested creates (addresses: { create }, where the FK lives on the child) are allowed in the unchecked input, so the restriction looks inconsistent until you notice which side owns the foreign key. createMany cannot nest at all — create the parents first and set the scalar FK.

Which form to prefer depends on what you are writing, and the two answers look contradictory until you see why:

• Production writes with a required parent relation: use tenant: { connect: { id } }. Adding a required relation to an existing write means converting that create away from the scalar FK.
• Negative probes against a row-level security policy: use the unchecked form (tenantId: other). connect resolves the parent through RLS first and fails with "no record found" before the policy under test is ever reached, so the test passes for the wrong reason.

Excess-property checking does not see through three shapes. After a migration removes a field, tsc finds only the direct references:

• a spread — data: { email, ...grantedRoles(...) } kept writing a dropped roles key and compiled cleanly;
• a nested create — tenant.create({ users: { create: { … } } }) compiled for ~20 integration-test seeds, then failed at runtime with Unknown argument;
• an as const select fragment — a shared PROFILE_SELECT kept nationality: true on a dropped column and only failed when a query ran.

The failure surfaces as PrismaClientValidationError at runtime, so a green recursive typecheck after a contraction means nothing. In one contraction the typecheck was clean while ~23 test files were broken. After dropping a column, grep the column name and its camelCase form across packages, apps, e2e specs and seeds, then run the full integration suite — that is what actually catches it. Prefer destructuring obsolete keys away (const { fullName, roles, ...account } = row) over spreading a shape that still carries them, and strip them inside the shared helper.

Two hazards specific to shared select fragments:

1. as const breaks orderBy. Prisma types orderBy as a mutable array; a readonly tuple fails to assign, with an error naming the whole select object rather than the array. Declare the array separately as Prisma.<Model>OrderByWithRelationInput[] and reference it.
2. Spreading a fragment silently overrides an earlier where. { where: {...}, ...fragment } loses your where entirely when the fragment carries one — no type error, just a query returning the wrong rows, which can pass a test whose fixtures happen to have one matching row. Spread first and set where after, or do not bundle where with select/orderBy in a reusable fragment.

A generated column typechecks and then throws. @default(dbgenerated()) makes the field optional in the create input but leaves it present, so passing a value compiles and fails at runtime with 428C9 cannot insert a non-DEFAULT value. Strip the key explicitly in the repo mapper — callers spread objects, and excess-property checking does not see through a spread.

Omit<> never narrows what satisfies a type, so a "transaction client" type accepts a full client. Structural typing means removing members does not restrict what can be passed in:

type TenantClient = Omit<PrismaClient, "$connect" | "$transaction" | ...>;

A bare PrismaClient satisfies that with no diagnostic — and so does Prisma.TransactionClient, which does not help either. The risk lands exactly where the mistake is easiest: a helper that has no transaction yet and is supposed to gain one. The type says "transaction client" and enforces nothing, while the SELECT set_config(…, true) such a helper issues is transaction-local and is discarded before the next statement.

Brand with an impossible optional member instead:

export type TransactionClient = TenantClient & { readonly $transaction?: never };

A $transaction callback's client has no $transaction, so it satisfies the optional property by absence; a real PrismaClient has it as a function, which is not assignable to undefined, and is rejected — Type 'PrismaClient<...>' is not assignable to type '{ readonly $transaction?: undefined; }'. Verified on TypeScript 5.9.3 / @prisma/client 6.19.3 with a green case and a red case in one tsc --strict probe. For any helper that must run inside a transaction, brand the parameter type and never accept an Omit<> of the full client — and assert the behaviour separately, because a type is not a proof about Postgres.

contains, startsWith and endsWith do not escape LIKE wildcards. The value is wrapped in % and bound as it is:

… WHERE "tenants"."name"::text LIKE $1   params ["%a_b%c%"]   -- contains: "a_b%c"
… WHERE "tenants"."name" ILIKE $1        params ["%a_b%c%"]   -- + mode: "insensitive"
… WHERE "tenants"."name"::text LIKE $1   params ["a_b%"]      -- startsWith: "a_b"

So a user-typed _ matches any one character and a % matches anything. The filter typechecks and returns extra rows without error. Escape user text (\, %, _) before passing it in. Prove the escape with a fixture name that contains _, and a search whose unescaped _ would also match a neighbouring row. This was believed the other way twice in one day, once in a design doc and once in a commit message, and a literal-match test caught it.

3. Raw SQL through Prisma

$queryRaw is a tagged template, and JS drops the backslash from unknown escape sequences in the cooked string. So regexp_replace(x, '\s+', ' ', 'g') written inline reaches Postgres as 's+' — a pattern that collapses the letter s instead of whitespace. It does not error; it quietly returns wrong rows. Double every backslash destined for Postgres — '\\s+', '\\d', '\\.', and LIKE 'foo\\_%' — and leave a comment saying why, because the doubled form looks like a mistake to the next reader. Where the pattern duplicates a domain function, add a test asserting the SQL and the TypeScript agree over a fixture built to make them disagree.

A bound parameter arrives as text. The driver does not send a type, and there is no uuid = text operator, so the planner refuses before any row is read:

// ERROR: operator does not exist: uuid = text
await db.$executeRawUnsafe(`UPDATE tenants SET name = $1 WHERE id = $2`, name, id);
// works
await db.$executeRawUnsafe(`UPDATE tenants SET name = $1 WHERE id = $2::uuid`, name, id);

The interpolated form right next to it works, and looks less safe rather than more — `SELECT … WHERE id = '${id}'` — because '…' is an untyped literal, which Postgres coerces to the column's type during parse. Typecheck cannot see the difference (both are string), and neither can a unit test that never reaches a database. If a raw query works interpolated and fails parameterised, look for the type, not the value: cast one per placeholder that is not text — $1::uuid, $1::timestamptz, $1::numeric.

A caught error inside $transaction poisons the next statement. A Postgres transaction aborts on the first error, so this harness fails on the assertion, not on the statement under test:

await db.$transaction(async (tx) => {
  await tx.$executeRawUnsafe(`SET LOCAL search_path=''`);
  try { await tx.$executeRawUnsafe(`INSERT …`); } catch { /* expected */ }
  await tx.$queryRawUnsafe(`SELECT count(*) …`);   // ← 25P02 here
});

Raw query failed. Code: 25P02 … current transaction is aborted reads as a broken harness rather than a caught defect. Let the $transaction reject, assert at the boundary, and verify row counts in a fresh transaction — the rollback is clean, nothing is left behind. Assert the error message (the missing function or constraint by name), never the code: a check on the code alone asserts 25P02, which is an artifact of the harness rather than of the defect. SET LOCAL search_path='' is otherwise safe here — Prisma's own raw traffic survives it.

Cross-reference: postgres-behaviour §7 for the other members of this family — psql variables that do not interpolate inside dollar quotes, and the two forms of SET search_path.

4. Prisma error codes and what they omit

Asserting a unique constraint takes both halves of one rule. Because P2002 carries no index name, a regex on the index name can never match — assert err.code === "P2002" plus err.meta.target. But meta.target is null whenever the write goes through a role that reads the table through an RLS policy: Postgres withholds the offending key's description from such a role, so Prisma has no columns to report. The same insert through the owner client returns target: ["user_account_id", "purpose"]. It is the role, not the transaction — an interactive $transaction preserves meta.target fine.

So: run a constraint-by-columns assertion through the owner client, wrapped in whatever sets the tenant GUC so the owner still satisfies the policy's WITH CHECK on a FORCE-RLS table, and comment why — it reads like an oversight otherwise.

An RLS refusal surfaces as Postgres 42501 through the connector wrapper, with the quotes escaped, hence the loose character class:

.rejects.toThrow(/row-level security policy for table [^a-z]*<table>/)

Cross-reference: postgres-behaviour §3 and §4 for who RLS applies to and why its usual failure mode is an empty result rather than an error. proof-and-verification for why a bare .rejects.toThrow() passes on any of the client-side errors above and therefore proves nothing.

5. What the schema cannot express, and what the drift gate sees

Prisma models none of these, so they stay hand-written in the migration and the drift gate is blind to them:

• partial / filtered unique indexes — @@unique/@@index have no where clause. The tempting workaround (a total unique index) is wrong: it forbids re-granting a revoked role or re-flagging a former primary contact. Write CREATE UNIQUE INDEX … WHERE … by hand and leave a doc comment on the model naming the index and saying it lives in SQL only, so the next reader does not "fix" the schema.
• column comments — /// doc comments produce no COMMENT ON COLUMN. A design that says "say in the schema why this column is nullable" therefore produces no artefact in the database at all; it takes an explicit COMMENT ON COLUMN in the migration plus an assertion, or it does not exist. Census it with col_description(attrelid, attnum) IS NOT NULL.
• RLS policies, GRANTs, CHECK constraints, functions, generated columns.

What prisma migrate diff --from-schema-datasource --to-schema-datamodel --exit-code does see — and will fail on, with exit 2:

• foreign keys, including composite ones. An FK written in SQL and left out of the schema reads as drift on every future run ([-] Removed foreign key on columns (relationship_code, kind)), and the way that eventually gets resolved is by someone dropping the constraint. Model every FK Prisma can express, even when the relation field is useless to readers. Prisma does permit two relations sharing one scalar field.
• index and constraint renames.
• @default(now()) as a database default (§1).

A rename carries dependent objects by OID, not by name, so the gate is what catches the ones you forget: ALTER TABLE … RENAME CONSTRAINT for inline or ADD CONSTRAINT constraints, ALTER INDEX … RENAME TO for anything Prisma emitted as CREATE [UNIQUE] INDEX from @@unique/@@index, and ALTER POLICY … ON <new_table> RENAME TO for the policy — which the gate cannot see, so a forgotten policy rename, or a table that silently lost FORCE ROW LEVEL SECURITY, passes it clean. The two rename forms are not interchangeable: RENAME CONSTRAINT cannot find an index-backed @@unique, and renaming a constraint does rename its backing index. Inbound FK constraint names derive from the child table, so they are fine.

Never let migrate dev generate a @@map change — it renders as DROP TABLE

• CREATE TABLE. Hand-write the migration, then use the diff only to confirm no drift remains. migrate deploy does not diff at all.

Anything the gate cannot see needs a DO $$ … RAISE EXCEPTION $$ guard in the migration itself. That is the only mechanism that notices a missing policy, a lost FORCE, or a CHECK that was never created.

Cross-reference: postgres-behaviour §2 — a partial unique index cannot be DEFERRABLE at all, which constrains how you promote and demote one; and its generated-column material, including the fact that concat_ws/concat/format are STABLE and so rejected in a generated column.

6. _prisma_migrations, and the rollback drill that passes vacuously

Dropping the objects is only half a rollback drill. prisma migrate deploy reads _prisma_migrations and treats an already-recorded migration as applied, so the re-apply is a silent no-op and the drill passes having proved nothing. Delete the ledger row too:

psql -d <db> \
  -c 'DROP TABLE "<new_table>";' \
  -c "DELETE FROM _prisma_migrations WHERE migration_name = '<timestamp>_<name>';"
prisma migrate deploy            # must genuinely re-run, not skip

Run the drill against a throwaway database, not the working one — a database with no _prisma_migrations table makes migrate deploy refuse with P3005 before the drill starts, and a database whose ledger you are editing is a bad place to learn that:

psql -d postgres -c 'CREATE DATABASE drill OWNER <role>;'
DATABASE_URL=postgresql://…/drill prisma migrate deploy   # whole history, ledger included
# … drop the objects, delete the ledger row, re-apply, assert …
psql -d postgres -c 'DROP DATABASE drill;'

Three things only the drill finds:

1. A backfill that is not idempotent. Make it INSERT … ON CONFLICT DO NOTHING or guard it with NOT EXISTS, so the second apply is safe.
2. The rollback needs its own statement order. It is not "the forward steps reversed" whenever a backfill invented rows the old shape cannot hold — the DELETE of backfill-invented rows has to come before restoring the old constraints.
3. Whether the RLS objects came back. The drift gate cannot see them (§5): SELECT polname, relforcerowsecurity FROM pg_policy JOIN pg_class ON pg_class.oid = polrelid WHERE relname = '<table>'.

Checksum drift, when a migration file was edited after it was applied. migrate dev reports "was modified after it was applied" and offers only migrate reset, while migrate status still says "up to date". Compare the stored checksum against the file's — shasum -a 256 …/migration.sql versus SELECT migration_name, checksum FROM _prisma_migrations — verify the objects the migration creates actually exist, then UPDATE _prisma_migrations SET checksum = '<file sha256>' WHERE migration_name = '…'. A local-record fix: no schema change, no data loss.

7. Prisma.dmmf as the schema oracle, and a column's blast radius

Prisma.dmmf.datamodel.models carries model names, dbName, and every field's dbName, kind, isRequired and relationFromFields. Reach for the DMMF whenever the question is what the schema declares rather than what the rows are — it answers with no database running, which also means the assertion cannot be silently skipped in an environment that has no connection string. Measured against a freshly migrated database, filtering models on a tenant_id field returns the identical 42 table names information_schema does, with nothing started.

const columnsOf = new Map(
  Prisma.dmmf.datamodel.models.map((m) => [tbl(m), new Set(m.fields.map((f) => f.dbName ?? f.name))]),
);

Two caveats: a hand-written FK with no Prisma relation is invisible to it, so a schema-derived ordering check cannot see those edges at all (§5); and deriving a parent-first order must filter to f.isRequired, because one nullable back-edge makes the relation graph cyclic and no such order exists.

A new column joins every table-generic consumer without a code change. A design can be careful about a column's API exposure and still publish it, because a SELECT * consumer needs no edit to start carrying it. When adding a column, grep for SELECT * and for findMany with no select over that table — and for export, backup and NDJSON registries specifically, because they are written to be table-generic on purpose, which is exactly what makes them blind. Fix by adding the column to the registry's redact list and asserting it in a test, since the next added column has the same problem.

Version dependence

• Prisma 6.19.3: @default(uuid(7)) engine-generated with a process-global monotonic counter (and uuid(7) has to exist in your version at all before any of §1's v7 advice is reachable); a bound $N arriving as text; a timestamp(6) read back truncated to milliseconds (§1); contains / startsWith sent to LIKE unescaped (§2).
• PostgreSQL 16.14: the uuid = text refusal; 428C9; no in-place ALTER COLUMN … SET GENERATED.
• Version-sensitive but unstamped — re-verify with a query log before relying on them under another major: @default(now()) bound client-side; upsert with update: {} emitting no UPDATE; @updatedAt sent on create; P2002 carrying no index name; meta.target: null under a policy role; the Some(Now) drift-gate output string; the DMMF field shape.

Related

• postgres-behaviour — what the database would have done with the column: the write path, DDL versus DML, RLS and who bypasses it, privileges, planner and ordering, psql and GUC mechanics.
• proof-and-verification — why several traps above pass vacuously as tests: a bare .rejects.toThrow() satisfied by a client-side error, a rollback drill whose re-apply was a no-op, a green typecheck after a contraction, and a negative probe that failed for the wrong reason.