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-inbinary
type indrizzle
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.