[BUG]: Unable to provide sql value in $onUpdate

[PurpleTape]

What version of drizzle-orm are you using?

0.30.10

What version of drizzle-kit are you using?

0.21.4

Describe the Bug

// schema
const table = pgTable('table', {
    ...
    updatedDate: timestamp('updated_date', { mode: 'date' }).$onUpdate(() => sql`CURRENT_TIMESTAMP`),
});

An error occurs when trying to update a table with such a schema:

TypeError: value.toISOString is not a function
    at PgTimestamp.mapToDriverValue (file:///app/node_modules/drizzle-orm/pg-core/columns/timestamp.js:33:18)
    at file:///app/node_modules/drizzle-orm/sql/sql.js:119:73
    at Array.map (<anonymous>)
    at SQL.buildQueryFromSourceParams (file:///app/node_modules/drizzle-orm/sql/sql.js:72:32)
    at file:///app/node_modules/drizzle-orm/sql/sql.js:94:21
    at Array.map (<anonymous>)
    at SQL.buildQueryFromSourceParams (file:///app/node_modules/drizzle-orm/sql/sql.js:72:32)
    at file:///app/node_modules/drizzle-orm/sql/sql.js:94:21
    at Array.map (<anonymous>)
    at SQL.buildQueryFromSourceParams (file:///app/node_modules/drizzle-orm/sql/sql.js:72:32)
    at file:///app/node_modules/drizzle-orm/sql/sql.js:94:21
    at Array.map (<anonymous>)
    at SQL.buildQueryFromSourceParams (file:///app/node_modules/drizzle-orm/sql/sql.js:72:32)
    at file:///app/node_modules/drizzle-orm/sql/sql.js:94:21
    at Array.map (<anonymous>)
    at SQL.buildQueryFromSourceParams (file:///app/node_modules/drizzle-orm/sql/sql.js:72:32)
    at file:///app/node_modules/drizzle-orm/sql/sql.js:52:26
    at Object.startActiveSpan (file:///app/node_modules/drizzle-orm/tracing.js:8:14)
    at SQL.toQuery (file:///app/node_modules/drizzle-orm/sql/sql.js:51:19)
    at PgDialect.sqlToQuery (file:///app/node_modules/drizzle-orm/pg-core/dialect.js:386:17)
    at file:///app/node_modules/drizzle-orm/pg-core/query-builders/insert.js:133:53
    at Object.startActiveSpan (file:///app/node_modules/drizzle-orm/tracing.js:8:14)
    at QueryPromise._prepare (file:///app/node_modules/drizzle-orm/pg-core/query-builders/insert.js:132:19)
    at file:///app/node_modules/drizzle-orm/pg-core/query-builders/insert.js:141:19
    at Object.startActiveSpan (file:///app/node_modules/drizzle-orm/tracing.js:8:14)
    at QueryPromise.execute (file:///app/node_modules/drizzle-orm/pg-core/query-builders/insert.js:140:19)
    at QueryPromise.then (file:///app/node_modules/drizzle-orm/query-promise.js:21:17)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

The occurrence of this error is due to the fact that when preparing a value for saving to the database, only the case is handled when the value is of type X https://github.com/drizzle-team/drizzle-orm/blob/a78eefe08e127922565486143e0150a718b27e8a/drizzle-orm/src/pg-core/columns/timestamp.ts#L65-L67

However, page DrizzleORM v0.30.5 release indicates that $onUpdate can take various values, including SQL

Expected behavior

No response

Environment & setup

No response

[vonkanehoffen]

For anyone Googling this (like me), a workaround is to change:

.$onUpdateFn(() => sql`now()`),

to

.$onUpdateFn(() => new Date()),

Related: https://github.com/drizzle-team/drizzle-orm/issues/2212

[DarkestSeconds]

new Date() uses server time :(

[DarkestSeconds]

Here is my solution:

import { toZonedTime, format } from 'date-fns-tz'

function getCurrentTimeInTimeZoneISO(timeZone: string) {
  const now = Date.now();

  const zonedDate = toZonedTime(now, timeZone);

  zonedDate.toISOString = () => format(zonedDate, "yyyy-MM-dd'T'HH:mm:ss'Z'", { timeZone })

  return zonedDate
  }

Use:

updatedAt: timestamp("updated_at").$onUpdate(() => getCurrentTimeInTimeZoneISO('America/Sao_Paulo'))

[alangumer]

This issue doesn't happen when using node-postgres.

[5amfung]

Here is my solution:

import { toZonedTime, format } from 'date-fns-tz'

function getCurrentTimeInTimeZoneISO(timeZone: string) {
  const now = Date.now();

  const zonedDate = toZonedTime(now, timeZone);

  zonedDate.toISOString = () => format(zonedDate, "yyyy-MM-dd'T'HH:mm:ss'Z'", { timeZone })

  return zonedDate
  }

Use:

updatedAt: timestamp("updated_at").$onUpdate(() => getCurrentTimeInTimeZoneISO('America/Sao_Paulo'))

The time from this example is not the database time.

[DarkestSeconds]

Here is my solution:

import { toZonedTime, format } from 'date-fns-tz'

function getCurrentTimeInTimeZoneISO(timeZone: string) {
  const now = Date.now();

  const zonedDate = toZonedTime(now, timeZone);

  zonedDate.toISOString = () => format(zonedDate, "yyyy-MM-dd'T'HH:mm:ss'Z'", { timeZone })

  return zonedDate
  }

Use:

updatedAt: timestamp("updated_at").$onUpdate(() => getCurrentTimeInTimeZoneISO('America/Sao_Paulo'))

The time from this example is not the database time.

Yeah, but with that u can easily control the time. When changing the TZ of process.env, the "new Date" only returned the correct timezone date when using .toString(), but drizzle uses the Date toISOString method, so I wouldn't have the TZ date that I I wanted. So I took this approach with date-fns which solved the problem, thus managing to control the date my way. (OBS: this is my case)

[oscarhermoso]

Work-around that may help some people, can use now() if you are happy to return a string instead of a Date.

    updatedAt: timestamp('updated_at', {
        withTimezone: true,
+       mode: 'string',
    })
        .defaultNow()
        .notNull()
        .$onUpdate(() => sql`now()`),

Drizzle ORM version 0.32.0, Postgres.js version 3.4.4

[kravetsone]

Work-around that may help some people, can use now() if you are happy to return a string instead of a Date.

  updatedAt: timestamp('updated_at', {
      withTimezone: true,
+     mode: 'string',
  })
      .defaultNow()
      .notNull()
      .$onUpdate(() => sql`now()`),

Drizzle ORM version 0.32.0, Postgres.js version 3.4.4

Thanks!