A Prisma 7 model uses @default(uuid()) and @updatedAt; why do rows written by a plain SQL script fail on the id or keep a stale updatedAt?
answer
- who computes the value
- not visible in the database schema
- SERIAL and CURRENT_TIMESTAMP versus nothing
- dbgenerated moves it to the column
basics
~20 suuid(), cuid() and @updatedAt are implemented by Prisma ORM, not the database: the migration creates the columns with no DEFAULT or trigger, and Prisma Client fills them in. SQL that bypasses the client must supply them itself.
solid answer
~50 sPrisma's default functions live in two places. `autoincrement()` and `now()` reach the database: on PostgreSQL the migration emits `SERIAL` and `DEFAULT CURRENT_TIMESTAMP`, so any insert gets them. `uuid()`, `cuid()`, `ulid()` and `nanoid()` are Prisma-level: the docs call them "not visible in the underlying database schema", the column is created with no default, and Prisma Client generates the value before it sends the insert. `@updatedAt` is also "implemented at Prisma ORM level": the client sets it on create and update calls, and the column is a plain `NOT NULL` timestamp with no default and no trigger. So a SQL script that omits the id fails with a not-null violation, and a SQL `UPDATE` leaves `updatedAt` untouched; raw queries sent through Prisma Client behave the same way. The fix is `@default(dbgenerated("gen_random_uuid()")) @db.Uuid` for ids, and, for `updatedAt`, either setting it explicitly in SQL or adding a trigger in a hand-edited migration.
code
prisma · 8 linesmodel Post {
// generated by the database: every writer gets an id
id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
title String
createdAt DateTime @default(now())
// stamped by Prisma Client only; SQL writers must set it themselves
updatedAt DateTime @updatedAt
}go deeper
Know that some @default functions are run by Prisma Client, not the database: uuid() and cuid() leave the column without a default, while autoincrement() and now() do not.
Explain which defaults appear in the migration SQL and which do not, and how @updatedAt is set by the client on create and update but not by raw SQL.
Diagnose a failing data-fix script or a stale updatedAt by reading the migration, then move ids to dbgenerated() and decide how non-Prisma writers keep updatedAt correct.
Make it a rule that tables shared with other writers keep generated values in the database, and document which tables are written only through Prisma Client.
## Where a default actually lives A `@default(...)` in a Prisma schema reads the same whichever function it uses, but only some of them end up in the database. Interviewers probe this because the difference is invisible while every write goes through Prisma Client, and shows up the first time something else writes to the table: a data-fix script, an import job, a second service, or a raw query. | Schema declaration | What the PostgreSQL migration contains | An insert that bypasses Prisma Client | | --- | --- | --- | | `@default(autoincrement())` | a `SERIAL` column | gets the next sequence value | | `@default(now())` | `DEFAULT CURRENT_TIMESTAMP` | gets the current time | | `@default(dbgenerated("gen_random_uuid()"))` | `DEFAULT gen_random_uuid()` | gets a new uuid | | a static value such as `@default(false)` or an enum member | `DEFAULT false`, `DEFAULT 'READER'` | gets the static value | | `@default(uuid())`, `uuid(7)`, `cuid()`, `cuid(2)`, `ulid()`, `nanoid()` | no default at all | must supply the value itself | | `@updatedAt` | no default and no trigger | must supply it, and an update leaves it alone | The Prisma docs state it directly: `uuid()` and `cuid()` are "implemented by Prisma ORM and therefore not 'visible' in the underlying database schema", and `@updatedAt` is "implemented at Prisma ORM level". ## The failure in practice Consider this model and the SQL Prisma Migrate generates for it: ```prisma model Post { id String @id @default(uuid()) @db.Uuid title String createdAt DateTime @default(now()) updatedAt DateTime @updatedAt } ``` ```sql CREATE TABLE "Post" ( "id" UUID NOT NULL, "title" TEXT NOT NULL, "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updatedAt" TIMESTAMP(3) NOT NULL, CONSTRAINT "Post_pkey" PRIMARY KEY ("id") ); ``` Three symptoms follow: 1. **Inserts from SQL fail on `id`.** `INSERT INTO "Post" (title) VALUES ('Hello')` violates the not-null constraint on `id`, because the database has no way to generate it. 2. **Inserts from SQL also fail on `updatedAt`.** The column is `NOT NULL` with no default, so the script must supply it, while `createdAt` fills in on its own. 3. **Updates from SQL leave `updatedAt` stale.** An `UPDATE "Post" SET title = ...` does not touch `updatedAt`, so any cache, sync job or "recently changed" list that relies on it silently misses the change. The same applies to `$executeRaw` and `$queryRaw` through Prisma Client itself: raw SQL is sent as written, so the client adds no generated id and no `updatedAt`. ## Behaviour inside Prisma Client - On `create`, the client generates the `uuid()` value and sets `updatedAt` when the caller does not supply them; both are optional properties in the generated create input. - On `update`, the client sets `updatedAt` to the current time unless the caller passes a value. The docs note one edge: an update whose `data` is empty (`data: {}`) leaves `updatedAt` unchanged. - `@updatedAt` is accepted only on `DateTime` fields; `uuid()` and `cuid()` only on `String` fields. ## Fixes, by field - **Ids:** move generation into the column with `@default(dbgenerated("gen_random_uuid()")) @db.Uuid`. The migration then emits `DEFAULT gen_random_uuid()`, so every writer gets an id. Keep `uuid()` when every writer goes through Prisma Client. - **`updatedAt`:** there is no schema attribute that creates a database trigger. Either make every non-Prisma writer set the column explicitly, or add a `BEFORE UPDATE` trigger in the SQL of a hand-edited migration. A trigger and `@updatedAt` can coexist; the trigger simply overwrites the value the client sent. - **Clock source:** `@updatedAt` is stamped by the application process, so if application and database clocks differ, compare it with database-side timestamps with care. ## Choosing deliberately The question is less "which function is better" than "who writes to this table". A schema that will be shared with scripts, other services or bulk loads should keep its generated values in the database, where every writer sees them; a table written only through Prisma Client can use the client-side generators freely. Choosing UUID versions or surrogate versus natural keys is a schema-design subject; the Prisma-specific trap is that two identical-looking `@default` lines put the value in different places.
- When would you keep @default(uuid()) instead of dbgenerated("gen_random_uuid()") in Prisma 7?When every writer goes through Prisma Client, for example a table owned by one service, or when you want `uuid(7)` time-ordered values without relying on a database function for them. If scripts, other services or bulk loads also insert rows, a database default is safer, because those writers never pass through the client.
- Does $executeRaw through Prisma Client set an @updatedAt column in Prisma 7?No. Raw queries send the SQL as written, so Prisma adds nothing to them: no generated ids, no `updatedAt`. Set the column explicitly in the statement, for example `SET "updatedAt" = now()`, or rely on a database trigger.
- @default(uuid()) makes the database generate the id for every insert.
- @updatedAt creates a database trigger that fires on every UPDATE.
- An UPDATE run from a SQL console also refreshes an @updatedAt column.
- A SQL insert must also supply a column declared @default(now()).
- $executeRaw adds generated ids for columns with @default(cuid()).