How to write equivalent of SQL MERGE statement using Prisma?

Question

I have a GraphQL mutation accepting the list of tag for a post. The PostTag model is:

model PostTag {
  id        BigInt          @id @default(autoincrement())

  order     Int
  value     String

  post      Post            @relation(fields: [postId], references: [id])
  postId    BigInt          @map("post_id")
  

  @@map("post_tag")
}

My REST API is PUT /posts/{postId}/tags which receives the list of tags that is to be updated against a given Post. In this list, some tags could have id which means it is an existing tag item that should be updated. There would be tag items without id means, it should be newly added. And the missing tags against a given post means those tags should be deleted.

I can do this using SQL merge statement. I want to achieve similar result using Prisma. How to do that efficiently using Prisma?

Answer 1

As of writing, there's no equivalent to the merge statement. A workaround using two queries would be as follows:

1. Delete existing PostTag records connected to a specific Post with deleteMany.
2. Create new PostTag records from the data received from the API endpoint with createMany.

Preferably, you can wrap query 1 and 2 in a transaction to ensure the database is not in an inconsistent state.

This isn't as optimized as the merge command. If you need the flexibility and performance of that specifically, please consider creating a feature request in the prisma repo mentioning your use case.