[FEATURE]: Prisma's updatedAt behavior. onUpdate API suggestion

[danulqua]

Describe what you want

Prisma has awesome feature such as declaring a column with @updatedAt which updates that column with a new timestamp every time the record updates.

Instead of suggesting to add something similar like just updatedAt alternative, what if you provide a more flexible onUpdate method when declaring columns in schema so we can do something like this:

const table = pgTable('table', {
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow().onUpdate(() => Date.now()),
});

The idea is that this method accepts a callback function that returns a new value for the column. The callback function is invoked every time the record updates. The basic example is to use it for timestamps on updatedAt columns, but it can be used for anything since the new value will be the one returned by the callback.

In general, when the record updates, we can find out which columns were updated, old values and new values. Maybe, this data can be accepted by onUpdate callback like

onUpdate((columns, oldValues, newValues) => ...)

Or any other better API for this.

[zivtamary]

What is the current approach to implementing updatedAt?

[danulqua]

@zivtamary either manually when updating records by providing new value for updatedAt column, or by creating a custom SQL migration and defining a trigger.

[MatanYadaev]

Related: #843

[laurent512]

Would really be interested by this feature !

[Pentiado]

You can ask the database to update the timestamp for you

const table = pgTable('table', {
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').default(sql`CURRENT_TIMESTAMP(3) on update CURRENT_TIMESTAMP(3)`),
});

Or, you can do it in your application code

// some db/utils.ts file with methods like updateUser, deleteUser, etc. 
export const updateUser = (data: Partial<InferModel<typeof users, 'insert'>>) => db.update(users).set({
  updatedAt: new Date(),
  ...data,
});

// api/user.ts
const yourApiOrStuffHandler = async (req, res) => {
  await updateUser({ firstName: req.query.firstName }).where(eq(users.id, req.query.id));
  
  res.send('ok!!');
};

Right now Drizzle is a lightweight ORM with no learning curve. You read through the docs in no time. In Prisma docs, you can get lost for days, and SQL knowledge won't help you much. I hope Drizzle will stay like this and will leave application code for developers to implement in their apps.

[Amar-Gill]

I would love to use the above approach using the sql statement but it does not appear to be possible for sqlite.

For me, the benefit of defining triggers in theschema.ts file is to keep my local db synchronized with generated migrations when I run drizzle-kit push:sqlite for local dev.

[Gerschtli]

@Pentiado That does not work in postgres either, looks like only mysql knows this on update notation, see also https://stackoverflow.com/questions/1035980/update-timestamp-when-row-is-updated-in-postgresql

[Angelelz]

This can now be achieved with the new .$default() / .$defaultFn() functions introduced in v0.28.3.
Can this issue be closed?

[danulqua]

@Angelelz How it's related to the ON UPDATE? Or could you provide an example using .$default() / .defaultFn()?

[Angelelz]

Nope, I was mistaken. Based on the shape of the API in the initial feature request, it looked a lot like $default(). But it would only run on inserts, not on updates.

[danulqua]

@Angelelz Exactly.

[statusunknown418]

any updates on this? I saw a PR pending merge

[MunifTanjim]

For PostgreSQL, until this is implemented, this is what I'm using for the time being:

create or replace function on_row_update_set_updated_at() returns trigger
    language plpgsql as
$$
begin
    NEW."updated_at" = now();
    return NEW;
end;
$$;

create or replace function on_create_table_add_trigger_on_row_update_set_updated_at() returns event_trigger
    language plpgsql as
$$
declare
    obj      record;
    tbl_name text;
begin
    for obj in select * from pg_event_trigger_ddl_commands() where object_type = 'table'
        loop
            tbl_name := obj.objid::regclass;
            if exists(select 1
                      from information_schema.columns
                      where table_schema = obj.schema_name
                        and table_name = tbl_name
                        and column_name = 'updated_at') then
                execute format(
                        'create or replace trigger on_row_update_set_updated_at before update on %I for each row execute procedure on_row_update_set_updated_at();',
                        tbl_name);
            end if;
        end loop;

end
$$;

create event trigger
    on_create_table_add_trigger_on_row_update_set_updated_at on ddl_command_end
    when tag in ('CREATE TABLE', 'CREATE TABLE AS')
execute procedure on_create_table_add_trigger_on_row_update_set_updated_at();

Create your tables with

"updated_at" timestamp with time zone DEFAULT now() NOT NULL

column, and it'll be updated automatically whenever any row changes.

[Angelelz]

@AlvaroAquijeDiaz

any updates on this? I saw a PR pending merge

What is that PR? I did a quick search and couldn't find it. I might open one if no one else has one open.

[statusunknown418]

@Angelelz here #1114 , I think it's actually yours lol

[Angelelz]

@Angelelz here #1114 , I think it's actually yours lol

That PR is for MySQL, postgres doesn't have an on update clause.

[statusunknown418]

@Angelelz here #1114 , I think it's actually yours lol

That PR is for MySQL, postgres doesn't have an on update clause.

Right, but could it be merged for MySQL though?

[pascalmtts]

We would also love to have this in drizzle. Currently, this is not really possible in a meaningful way with drizzle.

[ashishpandey001]

This is a core schema creation feature, at least for MySQL. Why is this labelled as an enhancement?

[Angelelz]

This is a core schema creation feature, at least for MySQL. Why is this labelled as an enhancement?

Postgres doesn't have an onUpdate. We're looking into this one. We're thinking that an API like the .$default() and .$defaultFn() but for updates would close this issue.

[ashishpandey001]

@Angelelz understood, thank you. I checked out the linked PR. A concern that I have is that implementing this functionality at the library level would be counter-productive for the MySQL dialect which has native support at the schema level for the trigger. Could we possibly implement this in such a way that this utilizes the schema definition for MySQL whereas use the lib's internal implementation or some custom plpgsql method for pg and other dialects?

[Angelelz]

How would it be counterproductive for MySql? We already have a similar feature with the .$default() method.

[ashishpandey001]

I'm saying that we should be replicating the behaviour described in this reference manual specifically for the DATETIME and TIMESTAMP data types via the MySQL dialect for drizzle rather than a generic .$defaultFn() or .$updateFn(). I hope I'm making some sense.

[Angelelz]

This is implemented already in mysql. We have in MySql the method .onUpdateNow() on all datetime and timestamp columns. The feature being proposed goes beyond updating timestamps, it could allow for example, implementing automatic counter at the database for how many times a user has updated his profile like this:

updateQty: int("updateQty").$onUpdate(() => sql`updateQty + 1`)

Now I know this can be done with a generated column in MySql, but not the datetime stuff in Pg, and also this is opt-in behavior.

[ashishpandey001]

I had no idea about the .onUpdateNow(). Is this documented somewhere because I can't seem to find it or the search is broken.

[Angelelz]

It is not. Can you raise a documentation issue?

[205g0]

@Angelelz according to the docs there's no .$onUpdate()

[Angelelz]

@Angelelz according to the docs there's no .$onUpdate()

I was referring to a feature being proposed, not a current feature.

[2coo]

The functionality that developers can extend their drizzle client like prisma client extension.
It would improve the developer experience way better.

Scenarios that I have faced:

• Hashing the password value before saving it into a database.
• adding a new property to query results at runtime and type-level.
  e.g. to use casl ability it requires to __caslSubjectType__ property.
• Implement soft delete

[gsavvidis96]

any news on this issue? I am using postgres and currently there is not a drizzle-integrated way to handle updated_at field.

[predragnikolic]

You can subscribe to this issue and the PR #1509
to get notifications.

[givenloyiso]

It seems like they removed that function or something.

[davidprokopec]

@givenloyiso It's currently possible only for MySql drivers, not for any others

[jassibacha]

This is implemented already in mysql. We have in MySql the method .onUpdateNow() on all datetime and timestamp columns. The feature being proposed goes beyond updating timestamps, it could allow for example, implementing automatic counter at the database for how many times a user has updated his profile like this:
...
Now I know this can be done with a generated column in MySql, but not the datetime stuff in Pg, and also this is opt-in behavior.

Hmm, I don't seem to be able to use .onUpdateNow() on datetime in my current project (MySQL + PlanetScale). I tried changing my updatedAt from timestamp to datetime and got an error.

export const createTable = mysqlTableCreator((name) => `asdf_${name}`);

export const users = createTable(
  "user",
  {
    id: varchar("id", { length: 256 }).primaryKey(),
    createdAt: timestamp("created_at")
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    updatedAt: datetime("updatedAt").onUpdateNow(),
  },
  (table) => ({}),
);

I get this error on updatedAt line: Property 'onUpdateNow' does not exist on type 'MySqlDateTimeBuilderInitial<"updatedAt">'.

I also see nothing in the documentation for Drizzle that even mentions onUpdateNow(), and the AI Assistant on the Docs also couldn't find anything.

UPDATE: After doing more googling it looks like it's a PlanetScale issue that prevents onUpdateNow(). Boo. Wish the docs explained this stuff.

[DannyFeliz]

This is what is needed

  updatedAt: timestamp('updated_at', { mode: 'date', precision: 3 }).$onUpdate(() => new Date()),

[tposch]

This is what is needed

  updatedAt: timestamp('updated_at', { mode: 'date', precision: 3 }).$onUpdate(() => new Date()),

You probably do not want to use new Date() because that will use server time. You probably want to use

$onUpdate(() => sql`CURRENT_TIMESTAMP`),

[anthonyhoegberg]

This is what is needed

  updatedAt: timestamp('updated_at', { mode: 'date', precision: 3 }).$onUpdate(() => new Date()),

You probably do not want to use new Date() because that will use server time. You probably want to use

$onUpdate(() => sql`CURRENT_TIMESTAMP`),

This would cause issues with stuff like drizzle-graphql since it assumes its a date, is there another way do use Date with db server time?

[carloschneider]

This is what is needed

  updatedAt: timestamp('updated_at', { mode: 'date', precision: 3 }).$onUpdate(() => new Date()),

You probably do not want to use new Date() because that will use server time. You probably want to use

$onUpdate(() => sql`CURRENT_TIMESTAMP`),

I dont know the reason, but when I tried to update an entry I received this error "TypeError: value.toISOString is not a function" so I chaged to new Date and it works.

[trevorpfiz]

This is what is needed

  updatedAt: timestamp('updated_at', { mode: 'date', precision: 3 }).$onUpdate(() => new Date()),

You probably do not want to use new Date() because that will use server time. You probably want to use

$onUpdate(() => sql`CURRENT_TIMESTAMP`),

I dont know the reason, but when I tried to update an entry I received this error "TypeError: value.toISOString is not a function" so I chaged to new Date and it works.

I am getting TypeError: value.toISOString is not a function as well with $onUpdateFn(() => sql`now()`)

[belkocik]

It works with postgres-js library and PostgreSQL database

export const users = pgTable('users', {
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at')
    .defaultNow()
    .$onUpdate(() => new Date()),
});

However, with

$onUpdate(() => sql`CURRENT_TIMESTAMP`),

I got an error that was mentioned by others:
TypeError: value.toISOString is not a function

Any solution how to use sql(`CURRENT_TIMESTAMP`) or sql`now()

[christophemarois]

@belkocik workaround here: #2212 (comment)

[ghost]

My setup. It works flawlessly

const timestamps = {
	created_at: timestamp({ withTimezone: true, mode: 'string' })
		.default(sql`(now() AT TIME ZONE 'utc'::text)`)
		.notNull(),
	updated_at: timestamp({ withTimezone: true, mode: 'string' })
		.default(sql`(now() AT TIME ZONE 'utc'::text)`)
		.notNull()
		.$onUpdate(() => sql`(now() AT TIME ZONE 'utc'::text)`),
}

export const countries = pgTable("countries", {
	id: uuid().defaultRandom().primaryKey().notNull(),
	name: text(),
	short: text(),
	code: text(),
	flag: text(),
	currency: text().default(''),
	symbol: text(),
	...timestamps,
});