Explain how upsert can behave as a hypothetical findOrCreate

[thebiglabasky]

Problem

Currently, people are wondering about having a findOrCreate method in the API (prisma/prisma-client-js#85).
We also have recently introduced connectOrCreate as an experimental feature (#568).

It happens that upsert behaves like a hypothetical findOrCreate method when receiving an empty update parameter.
There has been a decision to avoid adding shortcuts in the API to already supported methods to avoid unnecessarily expand the surface to test, but having a documentation update would help people figuring that out.

Suggested solutions

• We update the upsert documentation to cover the usage with an empty update parameter.
• Optionally (probably a bad idea): we add a findOrCreate section which explains one should use upsert as documented above to have this behavior

[Jolg42]

Would be nice to cover the usage with an empty update parameter in docs. Also having findOrCreate which is a common keyword for that would help find it through search and Google

[Jolg42]

Example

const user = await prisma.user.upsert({
  where: { email: '[email protected]' },
  update: {},
  create: { email: '[email protected]' },
})

[Jolg42]

Using it for a idempotent seed script
https://github.com/Jolg42/prisma-seed-example/blob/main/javascript/prisma/seed.js
https://github.com/Jolg42/prisma-seed-example/blob/main/typescript/prisma/seed.ts

[olliechick]

What about when you want to findOrCreate based on non-unique input? (For example, finding or creating a car based on the numberplate and country.)

An upsert doesn't work, because the where only takes unique input. I would want to do something like this:

const car = await prisma.car.findOrCreate({
  where: { licensePlate: 'ABC123', country: 'NZ' }
})

[codeslayer1]

I have the same question that @olliechick asked above

[matthewmueller]

Hey @olliechick, if license plates are scoped to country, then you can use a composite unique and then use upsert.

I've created an issue for cases where that's not possible: prisma/prisma#5436

[nrxus]

There is a slight hump about this. When doing an update without any update body, the updatedAt field does not get updated. This is expected and probably desired BUT that means that it is not possible for us to tell if the retrieved entity is an existing entity or a created one. Looking for how to tell "updated vs new" led me to: prisma/prisma#3432 which explicitly suggests looking at the updatedAt field.

I could manually add updatedAt to the update body but that feels wrong, all I want is to find or create, and to know if it was found or created.

[Thore1954]

What @nrxus mentioned is a good point on how upsert can't actually behave as findOrCreate. We are stuck with findFirst/findUnique + update in our controllers if we need to return the correct status code.

[lukasver]

There is a slight hump about this. When doing an update without any update body, the updatedAt field does not get updated. This is expected and probably desired BUT that means that it is not possible for us to tell if the retrieved entity is an existing entity or a created one. Looking for how to tell "updated vs new" led me to: prisma/prisma#3432 which explicitly suggests looking at the updatedAt field.

I could manually add updatedAt to the update body but that feels wrong, all I want is to find or create, and to know if it was found or created.

Same feeling here. Im stuck with looking for a good way to identify if the record retuner was found or created.

[owensj-basis]

What about when you want to findOrCreate based on non-unique input? (For example, finding or creating a car based on the numberplate and country.)

An upsert doesn't work, because the where only takes unique input. I would want to do something like this:

const car = await prisma.car.findOrCreate({
  where: { licensePlate: 'ABC123', country: 'NZ' }
})

I think this use case is the most compelling case for a separate findOrCreate function - since findOrCreate wouldn't need to worry about supporting update, it would be able to use findFirst's where clause instead of update's.

[magoz]

Agreed! findOrCreate would be super handy.

[stellenberger]

2 years later and still no solution 🥲

[Thexumaker]

I'm with you, I just don't understand the hesitancy around adding it in. findOrCreate is similar but different than upsert :')

[owensj-basis]

Just saw this was closed and got super excited, only to find out they just added a warning to the docs 🙄. The whole point of this thread is that upsert doesn't have the flexibility of any of the find queries so it doesn't really do well as a workaround. Very disappointed in this outcome

[TasinIshmam]

Hi @owensj-basis

Sorry, we haven't been able to prioritize this feature. To clarify, though, it's only the docs issue that is being closed, as the current behavior of upsert was documented.

The feature request for a dedicated findOrCreate is still open: prisma/prisma#5436

[owensj-basis]

I see, apologies for the negativity

[stellenberger]

I don't know who this may be helpful to, but it's the closest solution I found is to use connectOrCreate :)

But as it states, it's for connecting models together, but there's the findOrCreate element there, for those who want it

[ishaangandhi]

Does this work atomically?

For example, I have the following code:

    const user = await prisma.user.upsert({
      where: { id: userId },
      update: {},
      create: { id: userId, name: name },
    });

I think if I call this function twice when there is no user with userId, then do Promise.all on the two resulting promises, I get an exception:

Invalid `prisma.user.upsert()` invocation:
Unique constraint failed on the fields: (`id`)

I think this is happening because each promise is first doing the find, then doing the create. If both happened at the same time, the exception would be impossible.