Is it good to use UUID as primary key ?
Hi, i want to define manually an id for my entry to be able to create at tame time a folder with same id.
So using uuid can be a problem ?
or maybe i have to get a normal id used for everything, and a uuid used to identify my entry in folders (for images) and for client supports ?
24 Replies
That's a tough question, there's blog posts written about it. It will ultimately be up the systems architect to decide if the implications are worth it. UUID v7 seem to be better but ULID seems even better still.
From what I’m reading the main drawbacks are size (including size in indexes) and they won’t perform well in a clustered index. There may also be some examples of some performance limitations but I’m unsure how much of an impact that winds up being and it may well depend on context
You do lose an automatic ordering mechanism, so there’s that
Might also be interested in looking at CUID
yeah that's what i will still keep an autoincrement id
The fact is that i would like a short (10 to 15 chars) id that users can use to comunicate with support and use it as folder label
Look at CUID
because i donwant user have id like "14" or " 3"
Or even NanoID
UUIDs are long
NanoID is 25 Digit
to much
Oh is it?
and have special chars
Then CUID might fit the bill
oh no
no special chars
but 21 symbols
Short IDs. It uses a larger alphabet than UUID (A-Za-z0-9_-). So ID size was reduced from 36 to 21 symbols.
https://github.com/paralleldrive/cuid2
may do the job verry whel
GitHub
GitHub - paralleldrive/cuid2: Next generation guids. Secure, collis...
Next generation guids. Secure, collision-resistant ids optimized for horizontal scaling and performance. - GitHub - paralleldrive/cuid2: Next generation guids. Secure, collision-resistant ids optim...
can configure the length
I’m not sure if you’re gaining much by using an autoincrement column
Any perf issues feels like something that won’t be an issue in most situations and you’d actually need to profile
Though admittedly this is a bit out of my wheelhouse
if i don't have autoincrement i cant sort my entries by id so?
only with creation date
Right, that’s true
so now when i create my store, i want to assign a user to it in an another table
My table have this colums :
id_store, id_customer, status.
the id_store is an autoincrement so i can't get the id before the store is fully created.
How can i get the id of the first insert ?
Maybe i can do :
``
but it doesn't seems to work
What dialect are you using?
Oh, MySQL I see it in the tag
The result of the insert provides a value that comes from mysql2, and I believe res[0].insertId should include the ID of the last insert
I recently switched from UUIDs to Nanoids for my project's refactor. I never liked the length of UUIDs.
It's possible to set a custom size for Nanoids, I chose 12.
This article from Planetscale was helpful in my decision:
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.
Yeah I am using uuidv7 for pk and I have a publicId slug with 13 char nanoid. If I ever run into performance issues then it means I should be making money to hire someone smarter to figure out a better path lol
Yeah i saw NanoId, i switched to CUID, i also can choose the length
I will try, i'm not shure it can works because it'is ina transaction instance so i think the the insert will be executed at the verry end so res[0] may be empty ?
Hi Im using nanoid as my primary key (which is varchar null in supabase by default)
thats how it looks after drizzle-kit introspect
and in code I populate it
I'm also using V7 UUIDs as primary keys, which mimic the monotonically increasing behaviour of auto incrementing IDs.
I use this library https://github.com/jetpack-io/typeid-js to Base32 encode our UUIDs improves the usability of IDs (shorter, URL friendly, double clickable)
I would be weary of using nanoids as a primary key, they would suffer the same problems that V4 UUIDs have. They are suitable in the context of planetscale because it is a sharded database and having a good random key means a nice distribution across your shards. This is not the case for most database
GitHub
GitHub - jetpack-io/typeid-js: TypeScript implementation of TypeIDs...
TypeScript implementation of TypeIDs: type-safe, K-sortable, and globally unique identifiers inspired by Stripe IDs - GitHub - jetpack-io/typeid-js: TypeScript implementation of TypeIDs: type-safe,...