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
Nice read!
I started using ULIDs as DB IDs but what do I know
Mostly because the use the fancy words like Lexicographically Sortable
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?
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
Definitely. I expect my "solution" to be the absolute last resort. I mainly came here to hear other options
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.
![No description](https://cdn.answeroverflow.com/1169041295759650826/image.png)
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 definitionpostgres is magic
also planetscale:
![No description](https://cdn.answeroverflow.com/1169043184039510078/4a2c396da69e2167c87ed5ccc1e59b8d.png)
so where are you generating the ulids?
We didn't mind that pitfalls because I just maintain a small app used internally at our company
Our users are just the employees
oh i see
also, performance aside, ulids have the BEST pronunciation U-Lids
There's a js library of course
@Angelelz I've been follwing your direction and I made a ulid custom column to use in my tables:
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:
Results in the following print:
Do you have any suggestion on how to solve this?
NM, solved by letting the DB handle the binary extraction from the hex string
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
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?
Sorry I got you to that tangent in your project? What do you think of it so far?