Question about handling upsert case

What is the best way to handle this case for upserts? in prisma, my upsert code looks like this:

    await prisma.posts.upsert({
      create: {
        title: post.title,
        userId: req.user.id
      },
      update: { title },
      where: { id: post.id || '' }
    });

    await prisma.posts.upsert({
      create: {
        title: post.title,
        userId: req.user.id
      },
      update: { title },
      where: { id: post.id || '' }
    });

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:

    await db
      .insert(posts)
      .values({
        id: post.id || crypto.randomUUID(),
        title: post.title,
        userId: req.user.id
      })
      .onConflictDoUpdate({ set: { title: post.title }, target: posts.id });

    await db
      .insert(posts)
      .values({
        id: post.id || crypto.randomUUID(),
        title: post.title,
        userId: req.user.id
      })
      .onConflictDoUpdate({ set: { title: post.title }, target: posts.id });

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 it

17 Replies

Luxaritas•12mo ago

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 update

jakeleventhalOP•12mo ago

i suppose in drizzle i can just do this

    await db
      .insert(posts)
      .values({
        id: post.id,
        title: post.title,
        userId: req.user.id
      })
      .onConflictDoUpdate({ set: { title: post.title }, target: posts.id });

    await db
      .insert(posts)
      .values({
        id: post.id,
        title: post.title,
        userId: req.user.id
      })
      .onConflictDoUpdate({ set: { title: post.title }, target: posts.id });

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 upsert

Luxaritas•12mo ago

Technically 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 hack

jakeleventhalOP•12mo ago

if this isnt a use-case for upsert, then what is

Luxaritas•12mo ago

If you have a unique column

jakeleventhalOP•12mo ago

in this case id is unique

Luxaritas•12mo ago

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

Eimis•12mo ago

You are right. just dont send ID - will create. if there will be an id - it will update.

Luxaritas•12mo ago

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

jakeleventhalOP•12mo ago

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

Luxaritas•12mo ago

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

jakeleventhalOP•12mo ago

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

Luxaritas•12mo ago

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!

jakeleventhalOP•12mo ago

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

Luxaritas•12mo ago

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

jakeleventhalOP•12mo ago

understood. good point

chronos•11mo ago

const updated = await db
.update(...)
.set(...)
.where(eq(..))

// If no rows updated, create new row
if (updated[0].affectedRows === 0) {
  await db.insert(...).values(...)
}

const updated = await db
.update(...)
.set(...)
.where(eq(..))

// If no rows updated, create new row
if (updated[0].affectedRows === 0) {
  await db.insert(...).values(...)
}

Gaming

Programming

Question about handling upsert case