4 ms·
That‘s in my experience also the best approach. I wrote an article a few days ago about the exact thing: An integer auto incrementing PK with an UUID you use ex
by tpetry 4y ago
That‘s in my experience also the best approach. I wrote an article a few days ago about the exact thing: An integer auto incrementing PK with an UUID you use externally:
https://sqlfordevs.io/uuid-prevent-enumeration-attack https://sqlfordevs.io/uuid-prevent-enumeration-attack
- tylerscott 4y agoJust seconding this as a sane way to use UUIDs IME. Basically the sequential integer PK is the “internal ID”. IIRC in SQLite regardless of type or existence of PK there is a private sequential integer. Super handy pattern to use.
- ciupicri 4y agoAre you talking about ROWID? [1] > Except for WITHOUT ROWID tables, all rows within SQLite tables have a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the "rowid". The rowid value can be accessed using one of the special case-independent names "rowid", "oid", or "_rowid_" in place of a column name. If a table contains a user defined column named "rowid", "oid" or "_rowid_", then that name always refers the explicitly declared column and cannot be used to retrieve the integer rowid value. > [...] If an INSERT statement attempts to insert a NULL value into a rowid or integer primary key column, the system chooses an integer value to use as the rowid automatically. A detailed description of how this is done is provided separately. [2] [1]: https://www.sqlite.org/autoinc.html https://www.sqlite.org/autoinc.html [2]: https://www.sqlite.org/autoinc.html https://www.sqlite.org/autoinc.html
- hn_throwaway_99 4y agoThat is how I used to do (and currently still do) DB design, but honestly I think if DBs start supporting UUID v7s well that I would use that as the sole primary DB key as well as the external ID: 1. They are still sorted in increasing timestamp order (at millisecond granularity), so they should have good DB index characteristics. 2. At the same time, they contain 62 bits of randomness, would should pretty much eliminate IDOR attacks if there is a bug elsewhere that isn't doing proper access checks. Not good enough for secure tokens, but just good defense against access permission check bugs. That is, you should basically get the best of both worlds: ordered keys with enough randomness to make ID-increment attacks infeasible.
- RobertRoberts 4y agoYep, I want UUID v7, because right now I am using ULID and it's fantastic, but I'd like more official and wide support as well.
- jeremyjh 4y agoI like the textual representation of ULIDs as well though; I wish they’d just adopted ULID as v7. At least it is binary compatible with existing UUID types.
- RobertRoberts 4y agoIs there not an equivalent text representation for UUIDv7?
- jeremyjh 4y agoSure you could encode it in Crockford's base-32 but if it isn't part of the standard then tools won't implement it natively, so you couldn't copy a key from a url and look it up in postgres without running it through a conversion function, for example.
- tpetry 4y agoYou can write a custom data type in pure SQL for PostgreSQL which is just transforming a visible string to the more efficient uuid type. That‘s basically how the uuid type can be implemented: For storage it‘s binary(16) but all operations transform the value to the visible string you see all the time. It‘s a pretty powerfull feature.
- yencabulator 4y agoI was enthusiastic for a while but https://www.postgresql.org/docs/current/sql-createtype.html https://www.postgresql.org/docs/current/sql-createtype.html > (This restriction is made because an erroneous type definition could confuse or even crash the server.) Uhh, what.. > Generally these functions have to be coded in C or another low-level language. Oh, okay. That sounds like this not really doable with many hosted Postgres services out there.