feat: [Drizzle] Convert MySQL timestamps to datetimes

[Wundero]

Is your feature request related to a problem? Please describe.

For date + time related fields, MySQL's timestamp field is only 32 bits, and will run into issues once 2038 comes around. This isn't a giant issue per se, and users can certainly correct this on their own, but IMO there really is no harm in switching to datetime columns instead, since they can hold dates from 1000-01-01 00:00:00 to 9999-12-31 23:59:59, which will last a lot longer.

Describe the solution you'd like to see

Prisma already stores datetimes like this (reference), and switching drizzle is as simple as:

// Example model schema from the Drizzle docs
// https://orm.drizzle.team/docs/sql-schema-declaration

import { sql } from "drizzle-orm";
import {
  bigint,
  index,
  mysqlTableCreator,
-  timestamp,
+  datetime,
  varchar,
} from "drizzle-orm/mysql-core";

/**
 * This is an example of how to use the multi-project schema feature of Drizzle ORM. Use the same
 * database instance for multiple projects.
 *
 * @see https://orm.drizzle.team/docs/goodies#multi-project-schema
 */
export const mysqlTable = mysqlTableCreator((name) => `project1_${name}`);

export const posts = mysqlTable(
  "post",
  {
    id: bigint("id", { mode: "number" }).primaryKey().autoincrement(),
    name: varchar("name", { length: 256 }),
-   createdAt: timestamp("created_at")
-     .default(sql`CURRENT_TIMESTAMP`)
+   createdAt: datetime("created_at", { fsp: 3 })
+     .defaultCurrentTimestamp()
      .notNull(),
-    updatedAt: timestamp("updatedAt").onUpdateNow(),
+    updatedAt: datetime("updatedAt", { fsp: 3 }).onUpdateCurrentTimestamp(),
  },
  (example) => ({
    nameIndex: index("name_idx").on(example.name),
  })
);

This is using a sub-second precision of 3 points, which is in-line with what Prisma does, just for consistency.

Describe alternate solutions

N/A

Additional information

The syntax for onUpdateCurrentTimestamp requires https://github.com/drizzle-team/drizzle-orm/pull/1114 to be merged, and I don't know that Drizzle currently supports the syntax necessary for auto-updates directly, though I believe you can just insert it into the default SQL like so:

updatedAt: datetime("updatedAt", { fsp: 3 }).default(sql`CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`),

[c-ehrlich]

seems reasonable. do you want to open a pr for this?

[Wundero]

seems reasonable. do you want to open a pr for this?

I'm watching the Drizzle issue which will introduce the change for the default timestamp, since I don't want to rely on semantics that are kind of undefined behavior (the on update logic being in the default statement) in code that will likely ship to production applications. Once that goes through, I will probably make a PR if no one else has.

[statusunknown418]

hey @Wundero this line

updatedAt: datetime("updatedAt", { fsp: 3 }).default(sql`CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`),

throws this error when trying db:push

    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ER_INVALID_DEFAULT',
  errno: 1067,
  sql: 'CREATE TABLE `post` (\n' +
    '\t`id` bigint AUTO_INCREMENT NOT NULL,\n' +
    '\t`name` varchar(256),\n' +
    '\t`createdById` varchar(255) NOT NULL,\n' +
    '\t`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,\n' +
    '\t`updatedAt` datetime(3) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n' +
    '\tCONSTRAINT `post_id` PRIMARY KEY(`id`)\n' +
    ');\n',
  sqlState: '42000',
  sqlMessage: 'target: pathways.-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for \'updatedAt\' (errno 1067) (sqlstate 42000) (CallerID: mi3x34888e3ekca4weoi): Sql: "create table post (\\n\\tid bigint not null auto_increment,\\n\\t`name` varchar(256),\\n\\tcreatedById varchar(255) not null,\\n\\tcreated_at timestamp not null default current_timestamp(),\\n\\tupdatedAt datetime(3) default current_timestamp() on update current_timestamp(),\\n\\tconstraint post_id primary key (id)\\n)", BindVars: {REDACTED}'
}

[EDIT]: It does work, I was using timestamp instead of datetime as the column type lol