Prisma-client-js: Upsert should allow where clause with only undefined

[Sytten]

Bug description

Currently when using upsert, you have to specify at least one argument in the where clause. This is problematic when creating an upsert interface since the id might be undefined/null. This requires us to do something like:

where: { id: id || '' },

Otherwise it throws something like:

Argument where of type MyModelWhereUniqueInput needs at least one argument. Available args are listed in green.

How to reproduce

1. Take any model
2. Try to upsert with an undefined id
3. Failure

Expected behavior

Prisma should treat this case as a create case instead of throwing an error. This would be closer to the expected behaviour. It would be even better to accept null too (currently only undefined is accepted).

[matthewmueller]

Thanks @Sytten for creating this issue. We definitely agree that this is unwieldy.

We're planning to allow undefined in there, but not null. Null has special meaning in many cases, so while it doesn't affect where specifically, it's better to separate them out explicitly.

[timsuchanek]

Thanks @Sytten and thanks @matthewmueller for the answer.
We indeed discussed this internally and in general undefined should be possible, as well as a non-existent value, if it makes sense in the api.

Let's understand what upsert does, to see, if it also would make sense here.
Upsert performs the following steps:

1. Look up the unique row provided through where - the same as findOne does
2. If it exists, do an update
3. If it doesn't exist, do a create

The first lookup step can't be performed, if the element can't even be looked up.
And this lookup is the crucial element of upsert.
If we don't look for anything before creating, we can just use create instead.

[Sytten]

I agree with the idea. Still I think it is useful to allow the case where you cannot lookup to go directly to step 3. The general principle of an upsert is: update if possible, create otherwise.

So a typical usecase that I have now seen in two different codebase is a GraphQL mutation like saveObject that takes an optional id field. So this translate to id?: string | null. So I can understand not wanting to allow null but at least if I can do id | undefined that would still be an improvement.

[matthewmueller]

@Sytten wondering if an upsert makes sense in this case. Would you be able to do this instead?

if (id) {
  return prisma.user.update({data})
} else {
  return prisma.user.create({data})
}

[Sytten]

Most likely yes, but when you have many of them the upsert syntax is nicer. I don't really see the point of an upsert otherwise. It is really rare that one would know a database ID but not know if it exists or not. Maybe in the case of a composite key?

[timsuchanek]

@Sytten the case of knowing a database ID and not sure if it exists is for sure very rare.
A typical use-case is rather, that you have a different unique identifier like an email, then upsert makes more sense.

[matthewmueller]

Closing this issue since there's are no plans to improve this right now.

That being said, I absolutely do agree with @Sytten that one of the major use cases of upsert is when you may or may not have an ID and you just want the database client to do the right thing.

We may revisit if we hear more folks complain about this.

[PHILLIPS71]

Running into this issue now, where we either update a unique token if provided or create one if undefined. Would be nice to have this all wrapped in a single upsert, but the current state is blocking it. Processing undefined variables into the create procedure would be rather nice in these sort of cases.