Simple insertOrUpdate (insert ... on duplicate key update)

[dotMortis]

Problem

I want to create an ez "insert ... on duplicate key update" query.
I see no reason why this should only be possible via a raw query.

prisma.myTable.insertOrUpdate({data: {a: 1, b: 2, c: 3}, overwrite: {a: true, c: true}})

[pantharshit00]

Doesn't upsert solve this for you: https://www.prisma.io/docs/reference/api-reference/prisma-client-reference#upsert

Upsert basically will insert if where condition is not met, otherwise it will update the record which sounds like what you are describing.

[dotMortis]

@pantharshit00 Thank you for your answer.
Sure, it works, as far as I can see.
But why not use the native function from the database? :D
Would be nice to see this in a future release.

[janpio]

Isn't upsert, that @pantharshit00 linked to, doing exactly that? What is the difference you are hoping to see here?

[windowsdeveloperwannabe]

Isn't upsert, that @pantharshit00 linked to, doing exactly that? What is the difference you are hoping to see here?

The current upsert offered by Prisma has a race condition (#3242). It would be optimal if at least the Postgres/Mysql/Mongodb drivers used the database's native upsert to avoid that.

I currently have to use raw SQL to do upserts to continue using Prisma. But other than that it's such a nice library 🥰

[shtse8]

Doesn't upsert solve this for you: https://www.prisma.io/docs/reference/api-reference/prisma-client-reference#upsert

Upsert basically will insert if where condition is not met, otherwise it will update the record which sounds like what you are describing.

Does upsert cannot work in $transaction? as it returns Prisma.Prisma__DataClient<Data> instead of PrismaPromise.
I tried to use upsert in transaction, but the executed upsert before error didn't roll back.

---

update:
it doesn't work in $transaction([]), but it works well in interactive transaction $transaction(prisma => {}).

[Akxe]

The upsert solves this for a single row, but upsert many are still missing and thus, doing something like refreshing imported data is very complex of must be done manually.

INSERT ... ON DUPLICATE KEY UPDATE would solve this.
(related: #10362)

[taylor-lindores-reeves]

@Akxe suppose I have an API that allows car dealerships to post their stock feed to my website. I want my API to be in sync with their website, so when they mark a car as sold, it is also marked as sold on my website. Any idea how to achieve this with Prisma in its current form?

Thanks in advance!

[janpio]

I am closing this for now, please open a new issue if you need MySQL's INSERT ... ON DUPLICATE KEY UPDATE specifically (support for PostgreSQL's ON CONFLICT will be added in 4.6.0, an update will be posted in #3242)

[taylor-lindores-reeves]

Actually I managed to achieve what I was looking for using create/update in nested writes within a for... of loop.

I will take a look into PostGreSQL's ON CONFLICT. Thanks!