prisma: `upsert()` `where` is defined as `number | undefined` but does not allow `undefined` to not select anything

I want to give a big 👍 to this.

Here’s my use case:

    const question = await db.question.update({
      where: { id },
      data: {
        ...data,
        choices: {
          upsert: data.choices.map((choice) => ({
            where: { id: choice.id },
            create: { text: choice.text },
            update: { text: choice.text },
          })),
        },
      },
    })

Which produces this error when choice.id is undefined:

Invalid `prisma.question.update()` invocation:

{
  where: {
    id: 3
  },
  data: {
    createdAt: new Date('2021-02-25T17:28:52.806Z'),
    updatedAt: new Date('2021-02-25T17:28:52.806Z'),
    text: 'hi',
    choices: {
      upsert: {
        '0': {
          where: {
?           id?: Int
          },
          create: {
            text: '2'
          },
          update: {
            text: '2'
          }
        }
      }
    }
  }
}

Argument data.choices.upsert.0.where of type ChoiceWhereUniqueInput needs at least one argument. Available args are listed in green.

Note: Lines with ? are optional.

But I can get it to work by changing where: { id: choice.id } to where: { id: choice.id || 0 }

If I have a specific id then 99% of the time that record will exist. It’s id = undefined when I need upsert. So if upsert doesn’t allow undefined, then upsert is useless in this case (unless we do a hack like {where: {id: user.id || 0}})

I just wanted to chime in for option 1.

I ran into this issue when using UUIDs as id:

await prisma.model.upsert({
    where: { id: modelId || '' },
    update: data,
    create: data,
  })
// > Inconsistent column data: Error creating UUID, invalid length: expected length 32 for simple format, found 0

where: { id: modelId || 0 } and where: { id: modelId || null } give type errors as expected. Using the zero UUID works: where: { id: organizationId || '00000000-0000-0000-0000-000000000000' }, but thats rather awkward and defies the use of an upsert, right?

My expectation as Prisma user is that id: undefined should work.

Hmm, this is a tricky one because undefined so far has consistently meant exclude from the results. I agree with @flybayer though that id = undefined is when you need actually want to upsert. I’m wondering 2 things:

• Could an upsert with an empty where clause always try to create? This would be consistent with our current approach.
• Change the where type to not allow undefined since it triggers a validation error anyways. Then the proper way is user.id || 0 or user.id || "".

Any other ideas?

Regarding the consistency in the Prisma api that “undefined” translates to “omit this”, true, Prisma could just ask the user to only use “undefined” values in this specific way, but this has problems:

* new users should not be expected to know this and have no good way to learn this rule

* Prisma can't display an error message when "undefined" values are passed in incorrectly because, technically speaking, it's user error, not an error with Prisma

So from my viewpoint, it might be good to refactor some of the prisma api to let “undefined” simply mean “undefined” and not “omit this value”

I’m running into this issue as well. I have a table with a UUID string as primary key. When I do an upsert on this table, I use a where clause like where: { id: data?.id }. This gives the error stated by the OP when the id is undefined. As a workaround I use where: { id: data.id || ""} but this is technically not correct. An id could be “” when a record was explicitly added with “” as the id. Then the upsert would update that record instead of creating a new one.

I think the comment of @Johnrobmiller is spot on. The definition of “undefined” in a where clause should not be the same as in a create/update object.

As a workaround, maybe you could make a where clause not be required in the upsert method? When the where clause is omitted, then always perform a create. Then we could do something like where: data.id ? { id: data.id } : undefined. Not very user friendly, but at least an alternative.

By giving a random uuid you can also get rid of type errors and possible unexpected updates.

import { randomUUID } from 'node:crypto'

await prisma.model.upsert({
    where: { id: modelId || randomUUID() },
    update: data,
    create: data,
  })

Hmm, this is a tricky one because undefined so far has consistently meant exclude from the results.

I agree with @flybayer though that id = undefined is when you need actually want to upsert.

I’m wondering 2 things:

• Could an upsert with an empty where clause always try to create? This would be consistent with our current approach.
• Change the where type to not allow undefined since it triggers a validation error anyways. Then the proper way is user.id || 0 or user.id || "".

Any other ideas?

Hmm, this is a tricky one because undefined so far has consistently meant exclude from the results.

I agree with @flybayer though that id = undefined is when you need actually want to upsert.

I’m wondering 2 things:

• Could an upsert with an empty where clause always try to create? This would be consistent with our current approach.
• Change the where type to not allow undefined since it triggers a validation error anyways. Then the proper way is user.id || 0 or user.id || "".

Any other ideas?

Not a big fan of the first point, if that was the case we should just use update then. I like the second point though.

Just updating the docs would be a valid solution imo., as the example given upsert a user where id = 1, but how often are id’s supplied in a create?

const result = await prisma.user.upsert({ 
  where: { id: user.id || 0 }, 
  update: { email: 'alice@prisma.io' }, 
  create: { email: 'alice@prisma.io' }, 
})

maybe?

@DanStevensonDev yep I imagine a few of us have the same dirty little hard coded 00000000-0000-0000-0000-000000000000 in our code because of this oddity in the way prisma upsert works.

As you say, simple solution would be to allow undefined and just do a “create” if there is no where clause.

+1 to making the where property optional on the upsert options and doing a create if it is not passed… as mentioned earlier, this issue is particularly annoying if you use UUID primary keys

just ran into this, the error is very vague as well

Argument where of type <modelName>WhereUniqueInput needs at least one argument. Available args are listed in green.

…what?

I don’t see any problem with the first point. If the ID comes from an user input, then it should be possible to pass undefined. I mean, it’s exactly the point of an upsert: “if the id is not provided or the record doesn’t exists, create, otherwise update”.

For data it works but not in where clause. This causes an error

    question_id = undefined;
    return ctx.prisma.question.upsert({
      create: newQuest,
      update: newQuest,
      where: {question_id}
    });

This works

    return ctx.prisma.question.upsert({
      create: newQuest,
      update: newQuest,
      where: {question_id: question_id ? question_id : 0}
    });

@melihkizmaz by doing so you are introducing a non-zero probability of operation failure, no matter how small the probability is, you are now responsible for handling that failure case. Which is going to be nasty to deal with.

We need real solution, not some hack to get around it, Prisma needs to keep up with these requests, we are not asking for much here.

I know that this is a work around, I apologize for pretending to offer a solution, I just made an extra suggestion instead of a usage like ‘00000000-0000-0000-0000-0000-000000000000’ (the same non-zero probability is here) but I could not express it correctly.

I agree that we need a real solution by Prisma.

@melihkizmaz by doing so you are introducing a non-zero probability of operation failure, no matter how small the probability is, you are now responsible for handling that failure case. Which is going to be nasty to deal with.

We need real solution, not some hack to get around it, Prisma needs to keep up with these requests, we are not asking for much here.

Of course @janpio , thank you. I moved on but when I’ll have time I’ll open a new issue

So, I’m replying again to say that right now upsert is not working with MongoDB and there’s no workaround. As @ohueter is pointing out, you have to pass a valid value and in case of MongoDB it must be an ObjectId. I really think this is not a secondary issue and it should be addressed since a core API is completely not working. Even if I’d like to see the undefined option to be implemented, I’d appreciate anything that works. I’d also provide a PR as soon as a design decision is taken

Hmm, this is a tricky one because undefined so far has consistently meant exclude from the results.

I agree with @flybayer though that id = undefined is when you need actually want to upsert.

I’m wondering 2 things:

• Could an upsert with an empty where clause always try to create? This would be consistent with our current approach.
• Change the where type to not allow undefined since it triggers a validation error anyways. Then the proper way is user.id || 0 or user.id || "".

Any other ideas?

Regarding the consistency in the Prisma api that “undefined” translates to “omit this”, true, Prisma could just ask the user to only use “undefined” values in this specific way, but this has problems:

• new users should not be expected to know this and have no good way to learn this rule
• Prisma can’t display an error message when “undefined” values are passed in incorrectly because, technically speaking, it’s user error, not an error with Prisma

So from my viewpoint, it might be good to refactor some of the prisma api to let “undefined” simply mean “undefined” and not “omit this value”