Drizzle Team•11mo ago

Smart or Idiotic? The Great BigInt-to-Hash Adventure!

Alright, fellow code-wranglers, here's a spicy tech taco for you: we're dancing with regular bigint IDs for all our database entities, thanks to our waltz with planetscale row-reads pricing. Now, I don't know about you, but I’d rather our users not play detective and guess how many entities are populating our database by spotting the latest ID. Feels a bit like showing your poker hand, right? Enter the possible hero of our story: a nifty method that takes these IDs and morphs them into encrypted random strings using a sprinkle of secret salt. In this magical world, our database is blissfully unaware, munching on regular int-type IDs, while our users get served fancy hashed strings that look like UUIDs. Fancy, huh? But... plot twist! What if our secret salt takes a vacation and ends up in the wrong hands? We'd be stuck with a bunch of links that might as well lead to Narnia. Picture a "https://tinyurl.com/WhereDidTheMagicGo" that's lost its sparkle. Given that I’ve been known to occasionally doubt the genius-level of my brainstorm sessions (hey, humility, right?), what say you? Smart or Idiotic? Bring on the verdict!

35 Replies

Angelelz•11mo ago

Nice read! I started using ULIDs as DB IDs but what do I know Mostly because the use the fancy words like Lexicographically Sortable

Mendy•11mo ago

Thanks for chiming in! Ah, ULIDs - the alluring blend of uniqueness and lexicographical sortability. I've come across them before, and they indeed have that certain appeal, especially with those "fancy words" in their selling points. But that's an interesting take. Have you faced any specific challenges using them as DB IDs? I'm curious to hear about your experience! Unusually (he said) i've been a tad lazy reading the docs recently, so pardon the direct ask: do you know if there's decent support for ULIDs with Drizzle and Planetscale?

Angelelz•11mo ago

Well it's great not to care about transforming IDs before sending them to the client apps and still be able to use cursor based pagination

Mendy•11mo ago

Definitely. I expect my "solution" to be the absolute last resort. I mainly came here to hear other options

Mendy•11mo ago

interesting that planetscale chose to use nanoId in combination with an internal bigint id: https://planetscale.com/blog/why-we-chose-nanoids-for-planetscales-api

Why we chose NanoIDs for PlanetScale’s API — PlanetScale

Learn why PlanetScale used NanoID to generate obscure and URL friendly identifiers.

Angelelz•11mo ago

I don't know about planetscale, but since we use MySQL, I've found annoying not having a .returning() method. So I can't use the new $default() on the column definition

Mendy•11mo ago

postgres is magic

Mendy•11mo ago

also planetscale:

Mendy•11mo ago

so where are you generating the ulids?

Angelelz•11mo ago

We didn't mind that pitfalls because I just maintain a small app used internally at our company Our users are just the employees

Mendy•11mo ago

oh i see also, performance aside, ulids have the BEST pronunciation U-Lids

Angelelz•11mo ago

There's a js library of course

Mendy•11mo ago

@Angelelz I've been follwing your direction and I made a ulid custom column to use in my tables:

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

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

For reads and inserts this works prefect, but it won't work on update and deletes as the filter values get parametrized and the db gets the full string instead of the buffer - resulting in no match. Running this:

console.log(
  db
    .update(testUlid)
    .set({ value: "Testing update" })
    .where(eq(testUlid.id, "01HEENT3YW02X8Q915QFR4X23W"))
    .toSQL(),
);

console.log(
  db
    .update(testUlid)
    .set({ value: "Testing update" })
    .where(eq(testUlid.id, "01HEENT3YW02X8Q915QFR4X23W"))
    .toSQL(),
);

Results in the following print:

{
  sql: 'update `test_ulid` set `value` = ? where `test_ulid`.`id` = ?',
  params: [
    'Testing update',
    <Buffer 01 8b 9d 5d 0f dc 00 ba 8b a4 25 bb f0 4e 88 7c>
  ]
}

{
  sql: 'update `test_ulid` set `value` = ? where `test_ulid`.`id` = ?',
  params: [
    'Testing update',
    <Buffer 01 8b 9d 5d 0f dc 00 ba 8b a4 25 bb f0 4e 88 7c>
  ]
}

Do you have any suggestion on how to solve this? NM, solved by letting the DB handle the binary extraction from the hex string

Mendy•11mo ago

If you're here looking for a solution, see this gist

Gist

Implementing Efficient Binary ULID Storage in MySQL with Drizzle ORM

Implementing Efficient Binary ULID Storage in MySQL with Drizzle ORM - drizzle-ulid.md

Angelelz•11mo ago

OMG, this is gold! We did not take the time to even attempt to store it as binary Does it behave properly when comparing like this?

eq(testUlid.id, "01HEENT3YW02X8Q915QFR4X23W")

eq(testUlid.id, "01HEENT3YW02X8Q915QFR4X23W")

Sorry I got you to that tangent in your project? What do you think of it so far?

Gaming

Programming

Smart or Idiotic? The Great BigInt-to-Hash Adventure!