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 || '' }
    });

    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

post.id

post.id

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

post.id

post.id

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 });

    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

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

Luxaritas•12/24/23, 2:36 PM

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)

Luxaritas•12/24/23, 2:39 PM

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•12/24/23, 2:43 PM

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?string? so i suppose i could check if the id is defined and insert/update accordingly, but less code to upsert

Luxaritas•12/24/23, 2:45 PM

Technically I guess if

id

id

is non-nullable then it will automatically generate the id for the insert anyways

Luxaritas•12/24/23, 2:46 PM

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•12/24/23, 2:52 PM

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

Luxaritas•12/24/23, 3:04 PM

If you have a unique column

jakeleventhalOP•12/24/23, 3:04 PM

in this case id is unique

Luxaritas•12/24/23, 3:05 PM

Right, but I’m referring to case where you know the unique column ahead of time whether it’s an insert or update

Luxaritas•12/24/23, 3:05 PM

For example, imagine something like an email notification

Jjakeleventhal i suppose in drizzle i can just do this ```ts await db .insert(posts) ...

Eimis•12/24/23, 3:05 PM

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

Luxaritas•12/24/23, 3:06 PM

Where you want to either insert or update a row with a email user@example.com to set value true

Luxaritas•12/24/23, 3:10 PM

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•12/24/23, 3:12 PM

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•12/24/23, 3:12 PM

Upsert is overkill because you already know whether or not the row exists based on whether the ID is undefined

Luxaritas•12/24/23, 3:13 PM

You don’t have to check whether it exists in the DB already

jakeleventhalOP•12/24/23, 3:13 PM

i see. because it's basically an extra check in db logic

jakeleventhalOP•12/24/23, 3:13 PM

whereas the code check would be simpler/more efficient

jakeleventhalOP•12/24/23, 3:14 PM

better to avoid the extra query

Luxaritas•12/24/23, 3:14 PM

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

Luxaritas•12/24/23, 3:15 PM

The upsert is actively something something you don’t want!

jakeleventhalOP•12/24/23, 3:16 PM

it's not publicly accessible but for sake of argument how is conditionally using insert vs update any better security wise

jakeleventhalOP•12/24/23, 3:17 PM

if they specify an id they can still have the same outcome

Luxaritas•12/24/23, 3:17 PM

Only the same if you pass the ID to the insert instead of having it be autogenerated

Luxaritas•12/24/23, 3:18 PM

Imagine that someone deleted a record, but there’s still a link to that record somewhere in the internet

Luxaritas•12/24/23, 3:18 PM

A bad actor could, say, coopt that page

jakeleventhalOP•12/24/23, 3:19 PM

understood. good point

chronos•1/11/24, 10:00 AM

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(...)
}

Question about handling upsert case

Similar Threads

Similar Threads

Similar Threads