Use database triggers instead of engine-level code for @updatedAt

[AlaaZorkane]

Problem

Would be nice if while migrating, prisma generates a trigger for @updatedAt fields, this way if accessing the db with something other than prisma, we get the same behavior.

Suggested solution

From this

model User {
  id        String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  createdAt DateTime @default(now())
  updatedAt DateTime @default(now()) @updatedAt

  email    String    @unique
  name     String?
}

to this

-- CreateTable
CREATE TABLE "User" (
    "id" UUID NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "email" TEXT NOT NULL,
    "name" TEXT,

    PRIMARY KEY ("id")
);
-- This part! Also, this could be a function
CREATE EXTENSION IF NOT EXISTS moddatetime;
CREATE TRIGGER update_timestamp
    BEFORE UPDATE ON public."User"
    FOR EACH ROW
    EXECUTE PROCEDURE moddatetime("updatedAt");

Note that this solution is for pgsql only!

But triggers are available in sqlite/mysql/postgresql

IF for any reasons something like moddatetime doesn't exist in other databases (mysql/sqlite) then we can resort to the engine-level updatedAt

Alternatives

Keep using @updatedAt and manually adding the triggers by yourself to each table created by prisma.

Additional context

Wanted to add records from another tool (graphile) and found out that I needed the updatedAt field to be updated on the database level.

[AlaaZorkane]

Good to mention that there is some debating ground on this one! Per example from sequelize docs:

Direct link

I honestly can't see a reason why a trigger would be bad in this context (updatedAt field) but would love to know more about the motives to keep this internal to the engine itself.

[janpio]

This could be an additional feature of the Prisma Schema to be able to define that @updatedAt should use database level triggers instead of Client side timestamp generation. (We are highly unlikely to change the existing implementation in Prisma as many people rely on it.)

[AThilenius]

We have several systems (Rails, Hasura and Prisma/Node) accessing a shared PSQL DB, and triggers are the only reliable way to set the updated_at field. I would love a way to define this in the schema.prisma file!

Even better (from a maintenance standpoint) would be support for custom attributes, and a community-build set of plugins for things like this. I would happily throw some time at that.

Related:
#1660
#4002