Question about handling upsert case
What is the best way to handle this case for upserts?
in prisma, my upsert code looks like this:
If the post was being created,
post.id
would be undefined, thus the || ''
would make it such that no post was found with id of ''
, so it would create a new post
if the post was being updated, post.id
would exist and it would find the post with the matching id and update it.
in drizzle, the upsert code i have now looks like this:
is there a better way to do this without needing to randomly generate the UUID for the id
field? in the prisma code it was able to find no match on the id field, and just use the auto generate rule for the id field rather than me having to manually set it17 Replies
That’s not actually how you’re supposed to do it in Prisma either! I’m Prisma, if a record is found that matches the where, it runs the update, otherwise it does the create. You shouldn’t need the
|| ''
(presumably you have no existing record with an empty ID, so it behaves the same)
But even then, you already said the post ID is undefined if you’re creating a new post. Why do you need a DB upsert at all? If the passed post id is undefined, do an insert. Otherwise do an updatei suppose in drizzle i can just do this
if the id is undefined, itll just insert. otherwise it wont
why upsert? the api accepts id as
string?
so i suppose i could check if the id is defined and insert/update accordingly, but less code to upsertTechnically I guess if
id
is non-nullable then it will automatically generate the id for the insert anyways
Personally though? I wouldn’t do a DB upsert because you’re not actually trying to do a DB upsert. Feels like a hackif this isnt a use-case for upsert, then what is
If you have a unique column
in this case id is unique
Right, but I’m referring to case where you know the unique column ahead of time whether it’s an insert or update
For example, imagine something like an email notification
You are right. just dont send ID - will create. if there will be an id - it will update.
Where you want to either insert or update a row with a email [email protected] to set value true
Basically a situation where you know the unique column (often a key) has to be some value, but you don’t know whether or not it exists yet
i'm not following how the use case in the OP is not that though?
the code either saves or updates the post based on whether or not the unique key is already exists
Upsert is overkill because you already know whether or not the row exists based on whether the ID is undefined
You don’t have to check whether it exists in the DB already
i see. because it's basically an extra check in db logic
whereas the code check would be simpler/more efficient
better to avoid the extra query
In fact, you could actually be causing an error here. What if the API request specified an ID that doesn’t exist? If this API is publicly accessible it’s even possible for that to cause a security vulnerability in some cases (it may or may not in your case) since clients now have control over IDs being created
The upsert is actively something something you don’t want!
it's not publicly accessible but for sake of argument how is conditionally using insert vs update any better security wise
if they specify an id they can still have the same outcome
Only the same if you pass the ID to the insert instead of having it be autogenerated
Imagine that someone deleted a record, but there’s still a link to that record somewhere in the internet
A bad actor could, say, coopt that page
understood. good point