Questions about IDs in api Database design
Hello everyone I am creating a blog api for practice and using prisma orm. I was previously using text type for Primary Key ID in tables because the orm doesn't support uuid in postgres natively and instead uses it's own implementation to generate uuid and put them in. My thought was uuid would make resource id less guessable due to what I read somewhere.
Some people here told me I should use autoincrementing Integer instead and another field that has uuid if I need uuid. However do I need uuid? The resourse Id would be exposed to the frontend as I will put them in links components so routing frameworks can use them as route params.
34 Replies
Also it seems that this orm uses serial instead of identity
I assume this is also not ideal. I don't remember exactly how but serial had a chance to not advance the id sequence if used in a certain way
GitHub
Use
IDENTITY columns in PG10+ · Issue #4693 · prisma/prismaProblem When I define a new entity/table in my schema using id Int @id @default(autoincrement()) the generator returns "id" SERIAL. Suggested solution The recommended solution is to use t...
pain in the ass. It's stuck in the backlog
maybe I should just use plain node PG package again and write raw sql from next projects if it's not a big project
Throwing in the beginning of the discussion mentioned:
https://discord.com/channels/436251713830125568/448294302053957632/1378367036786475160
Also, @Rägnar 😜
I have not much to add than what I already said in the discussion Beck just linked, feel free to ping if you have any defined question
Well currently the main question i have is do I need uuid at all? As I said the resource (be it blog, comment etc) will be exposed in the links on frontend
Which while is a minor risk according to the convo linked above, I'm not sure if it's something I can ignore and just use the auto increment ids
it's up to you to decide if you are willing to take the risk
I would not, but you do you
Currently I'm just deciding how much of a pain it'd be to map resource_id to actual ID of object.
Resource_ID will be exposed by link but the actual ID will be too since it'll be stored in the frontend javascript
Which if anyone is actually wanting to take advantage of that then I can't stop me from reading the value from live code
Plus this is a practice/portfolio piece with low risk to anyone. (I'm not even collecting emails from users just a username and password. Which well if they forget the password they're cooked. Not like it matters anyway since I'll have to take it down after the free hosting service exceeds quota.
Well i guess that answers my question. I'll just keep the uuid implementation in mind if I do a project that'll actually need to be secure
As you practice, so shall you do.
Practice makes habit
XD true
Well I'll see
Thank you both
Resource_ID will be exposed by link but the actual ID will be too since it'll be stored in the frontend javascriptnah, what you call the "actual ID" but what is in fact the table's primary key should not leave the database, you should not filter based on it or select it. what you send to the client or what you use in the backend code is the "Resource_ID" most commonly named "remote id" both are just fields of the table but they have very distrinct uses
Wouldn't that defeat the purpose of indexing? I'm not using indexing since I don't understand it fully yet but that was one of the point of the convos that autoincremnt int ID are easier to index iirc
If use remote ID in my fetches the orm will query with the remote_id and not the integer ID PK and indexing won't work
Or maybe I'm not thinking of this properly
it depends a lot on what you want do query out of you DB, if the queries are random-ish (like a user navigating product pages on a merchant site for example) then a BTree index on a sequencial ID or a BTree index on a random id won't change anything as the thing you want to access are themselves random.
if you do a lot of insertions, for example if you use a DB to store action logs that are inherently sequential and accessed sequentially, a sequential id index will be more efficient.
and ID indexes are also important in when writing, this video will explain all that better than I could tho
https://youtu.be/OAOQ7U0XAi0?si=6YivXZsW-1x1lXRG
Hussein Nasser
YouTube
The effect of Random UUID on database performance
In this video I whiteboard how UUIDs hurt write (and read performance) when used on secondary and primary indexes. UUIDv4 are the most popular but they are truly random. Compared to snowflakes, ULID or UUIDv7 or even UUIDv1.
0:00 UUIDv4
2:30 B+Tree Indexes and UUID
5:30 Random UUIDv4 Insert Workload
12:40 Ordered Insert Workload (UUID7/ULID, S...
another thing you have to keep in mind is that you can have more than one index per table, heck you can even have more than one index per field
for example if you make a lot of query wit hfilters on the same 3 fields, it can be interesting to setup an agregate index on multiple collumn to help the query planner
PostgreSQL Documentation
11.3. Multicolumn Indexes
11.3. Multicolumn Indexes # An index can be defined on more than one column of a table. For example, if you …
but in most cases when building a DB backed website, if you keep a PK as an autoincremented int and a remote ID you leverage the nice sequential aspect of the PK for indexing when inserting and the randomness of the remote ID is not an issue as the read themselves are random most of the time so you would not benefit from a sequential ID access
(plus if you have a low amount of writes the cache thrashing when re-indexing on write will be minimal anyway 'cause the cache would've been used by something else when the next query is handled)
Alright I'll take a look at these and see how indexs work a bit more. Tho I'll put off actually implementing indexs before I finish this project and do it after I'm done with it, already too many new things to do in it.
My main confusion with remote_id was thinking how would I use it to fetch with the PK which you just cleared since I don't need to send the PK at all.
yeah, a good thing to keep in mind is that if something is called a key, it should not leave the database, so primary keys should not be in a select and foreign key either as they are simply pointer to the primary key of another record (possibly in the same table)
Gotcha thanks that clears it up
But you’d use the pk in join statements, right? Since they’re internal to the DB?
yeah
that's what they are for
And used for fks in other tables?
FK are always a pointer to a PK in another table
always
if you use a FK that points on something that isn't a PK you'll have problems down the line
Alright, cool. Thanks for taking me to school!
one cool thing about forrign keys is that you can use those for constraints, to keep the data consistent
for example, in mysql, you can use one that automatically deletes metadata for a post if you delete a post, and the database does that for you
that's not a cool thing, that's what they are made for
how's that not cool?
just because it was made to do something and it does the thing, it can't be cool?
What if I need the foreign key in the frontend? For example a parent_comment_id that shows if a comment is a child of other comments so i can decide how to nest them
Do i create another field parent_comment_remote_id and link that with remote_id
You don't need the foreign key in the front end, never.
You join the tables and use the remote key of the entity you joined with.
like
select sub.title, sub.body, sub.remote_id from comments inner join comments as sub on comments.id = sub.parent_id where comments.remote_id =$remote_id limit 50
(you can also join to the users or anything else you might need)
(pulled the field names out my ass for the sake of the example you would need to adapt to your structure)If I needed a sub.parent_remote_id (this field doesn't exist just talking about it conceptually) alongside the remote_id. I would add inside the select
comments.remote_id as parent_id rightYeah
Alright. Thanks again