`@updatedAt` should use database time

[clearwaterstream]

Problem

As per documentation @updatedAt is "Implemented at Prisma level" which implies server time, whereas for createdAt I would use "@default(now())" which uses database time.

The infrastructure Prisma code is running on (let's say servers for simplicity) may not have the time synced with the DB (NTP server may not be employed, etc) and the servers (if say web farm) may not be synced with themselves. This would result in times landing on DB which do not portray an accurate sequence.

Suggested solution

add an option to @updatedAt to use DB time. Something like @updatedAt(now()) which would signal the use of database time (ie CURRENT_TIMESTAMP) when setting the field value.

Alternatives

For now, I'm setting the value via ON BEFORE UPDATE trigger which requires manually altering migration files to add the trigger code

Additional context

Thank you!!

[nikolasburk]

Just quickly popping in here with a quick note for a workaround: You can customize your migration and include the native SQL command yourself for the "updated at"-logic using the --create-only option, e.g.:

npx prisma migrate dev --create-only

Then inside the SQL, add the respective command for your DB, e.g. for MySQL:

ALTER TABLE mytable
ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

Afterwards, run prisma migrate dev again to apply the migration 🙌

[wmtrinu]

Could we handle this directly on our database? (we use introspection heavily)

postgresql

create table account (
	id uuid not null primary key default(gen_random_uuid())
,       first_name varchar(64) not null
, 	last_name varchar(64) not null
,	email varchar(64) not null unique
,	password varchar(128) not null
,	is_active boolean default (false)
,	create_date timestamp with time zone not null default(now())
,	update_date timestamp with time zone not null default(now())
);

create or replace function update_account()
returns trigger as $$
begin
    new.update_date = now();
    return new;
end;
$$ language plpgsql;

create trigger account_update_trigger
before update on account
for each row
execute function update_account();

and this is the generated prisma schema

model account {
  id          String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  first_name  String   @db.VarChar(64)
  last_name   String   @db.VarChar(64)
  email       String   @unique @db.VarChar(64)
  password    String   @db.VarChar(128)
  is_active   Boolean? @default(false)
  create_date DateTime @default(now()) @db.Timestamptz(6)
  update_date DateTime @default(now()) @db.Timestamptz(6)
}