Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The perils of UUID primary keys in SQLite

andersmurphy.com

Post reply on HN
Print view View original

81–90 of 117 posts

Re: The perils of UUID primary keys in SQLite

#81

Post by Fabricio20 »

paulddraper wrote: post #21
Fabricio20 wrote: post #17

Earlier quoted context omitted.

> 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.

!! Node.js drivers will correctly read int64 as string or bigint, not number. E.g. pg for PostgreSQL Maybe there’s a buggy driver but I don’t know it.

Browser!! The browser reads it as Number. If your rest api returns {"id": 1324535222364012585} for example, javascript will try and parse that as number from the response!!!

You can of course, change the api such that it does {"id": "1324535222364012585"} instead and voila, it will no longer try parsing it as number. Or the many other workarounds people have recommended above (like appending a prefix, or using a different encoding), but why is it trying to parse a number thats too big and instead of throwing it just rounds down without telling you????!

Re: The perils of UUID primary keys in SQLite

#82

Post by dexterdog »

Volundr wrote: post #67
voakbasda wrote: post #45

Earlier quoted context omitted.

You can use an integer

How do I know the time zone of an integer? Sure there are plenty of cases where one doesn't care, but there are also many cases where the original time zone is important.

The integer is a UTC time so it can be sorted. If you need the time zone you store than in a smaller field.

Re: The perils of UUID primary keys in SQLite

#85

Post by marcosdumay »

mort96 wrote: post #41
anematode wrote: post #31

Earlier quoted context omitted.

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.

IMO, I'm tending toward thinking that having types on your readable serialization format is a mistake, and that they should be always input to the (de)serializer instead.

Re: The perils of UUID primary keys in SQLite

#86

Post by sedatk »

UUIDv7 and sequential integers are quite similar. Sequential integers disclose count and neighboring IDs while UUIDv7 discloses timestamp. Either can be a security issue in certain cases.

So, UUIDv4 as a PK on a clustered index can be perfectly feasible for cases where you want to avoid disclosing stuff and row insertion performance isn’t that important.

Re: The perils of UUID primary keys in SQLite

#87

Post by sheept »

mort96 wrote: post #66
sheept wrote: post #59

Earlier quoted context omitted.

No you don't? The example I gave produces {"a":9007199254740993} not {"a":"9007199254740993"}

Oh, that's much worse! The JSON string `{"a":9007199254740993}` decodes to the object `{"a":9007199254740992}` with typical JSON parsers like JavaScript's `JSON.parse`.

If you're applying a replacer, then you'd supply a reviver when parsing:

    const json = '{ "a": 9007199254740993 }'
    JSON.parse(json, (_key, value, context) => /^\d+$/.test(context.source) ? BigInt(context.source) : value)

Re: The perils of UUID primary keys in SQLite

#88

Post by willtemperley »

blopker wrote: post #5

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…

UUIDs make client code so much simpler. Just create a UUID, use it client side to create your object graph and commit or not as appropriate. No need to retrieve an incremented integer.

Re: The perils of UUID primary keys in SQLite

#89

Post by tyre »

sheept wrote: post #53
Fabricio20 wrote: post #17

Earlier quoted context omitted.

> 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.

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)

Which can be avoided by using UUIDs

Re: The perils of UUID primary keys in SQLite

#90

Post by mort96 »

sheept wrote: post #87
mort96 wrote: post #66

Earlier quoted context omitted.

Oh, that's much worse! The JSON string `{"a":9007199254740993}` decodes to the object `{"a":9007199254740992}` with typical JSON parsers like JavaScript's `JSON.parse`.

If you're applying a replacer, then you'd supply a reviver when parsing: const json = '{ "a": 9007199254740993 }' JSON.parse(json, (_key, value, context) => /^\d+$/.test(context.source) ? BigInt(context.source) : value)

Yeah but now you have the world's biggest foot gun in your API.
Post reply on HN