Implementing Efficient Binary ULID Storage in MySQL with Drizzle ORM

drizzle-ulid.md

For anyone considering the use of ULIDs in MySQL with drizzle, here's a ready-to-use ULID type for your convenience.

import { Ulid as ULID } from "id128";

export const ulid = customType<{
  data: string;
  notNull: true;
  default: false;
  driverData: Buffer;
  config: never;
}>({
  dataType() {
    return "binary(16)";
  },
  toDriver(value) {
    return sql`UNHEX(${ULID.fromCanonical(value).toRaw()})`;
  },
  fromDriver(value) {
    return ULID.fromRawTrusted(value.toString("hex")).toCanonical();
  },
});

The challenging aspect was ensuring that drizzle and MySQL worked seamlessly when storing ULIDs in a binary(16) format.

Note: MySQL only provides binary conversion functions for UUIDs, and the built-in binary type in drizzle is known to be problematic .

For ease of use, you might consider wrapping this further:

export const ulidPk = (dbName: string) =>
  ulid(dbName)
    .primaryKey()
    .$default(() => ULID.generate().toCanonical());

Note the dependency on id128 for ULID functionality. Chosen as it's currently the only library supporting binary ULIDs.

Currently, the only caveat is that updates and deletes will not function with Drizzle Studio, as it bypasses the toDriver method in the custom type definition. This limitation is likely to persist until the underlying behavior is modified. Keep in mind, issues only arise when storing ULIDs in binary; string storage is an alternative if you don't care about it, but here's why you should.