The perils of UUID primary keys in SQLite
andersmurphy.com126 points by emschwartz 18 hours ago
126 points by emschwartz 18 hours ago
UUIDs are way over used. There is almost always a better key to use, usually a bigint for databases. If you're making some kind of leaderless distributed data store, then maybe, but even then there are other ID sharding strategies I'd go for first depending on the constraints.
For a single database, bigints are smaller and faster, with less footguns.
UUIDs can be nice for an opaque public ID, however I'd still prefer something like a Sqid for space and usability.
> bigints are smaller and faster, with less footguns
But be careful!! Javascript WILL interpret your bigints as Number() and round them down because they are too big without telling you!!!
Famously seen by every snowflake user that has interacted with Javascript, quite an annoying problem.
Good trick is to prefix all such keys with magic, i.e. a couple of letters that identify type type of key.
Then it will always be a string and you will be free to change the format/type of the key in the future to UUID or whatever you like.
Using a Feistel cipher and base 32 encoding at the boundaries of the system can help catching vibe coded edge code that attempt to decode identifiers in javascript. It also somewhat obfuscate the cardinalities and fill rate of the tables.
This can be avoided by supplying a reviver:
const json = '{ "a": 9007199254740993 }'
JSON.parse(json, (_key, value, context) => /^\d+$/.test(context.source) ? BigInt(context.source) : value)Fortunately we're seeing more JS DB libraries offering to read large numbers as the BigInt type.
But frustratingly, a JS BigInt is nothing like a BigInt in any other language.
In JS - BigInt is 64bit integer.
In anything else - BigInt is a arbitrarily large integer.
Hm? JavaScript BigInts are arbitrary precision, and you need to use methods like BigInt.asIntN(64, a) to convert them to 64 bits
I hate this so much because you can’t nicely serialise a BigInt as JSON. Using a string is nicer but it only makes sense where int64 is used as an ID, not where it’s used as a number; and you don’t wanna have to configure this per field per query.
You can serialize a BigInt by specifying a replacer:
const obj = { a: 9007199254740993n }
JSON.stringify(obj, (_key, value) => typeof value === 'bigint' ? JSON.rawJSON(value.toString()) : value)And then you end up with strings on the other side, not numbers.
No you don't? The example I gave produces
{"a":9007199254740993}
{"a":"9007199254740993"}