19

How can I upsert many fields in prisma ORM with one query?

I don't want to use upsert fields one by one. Can I upsert all of them with one query?

Chris Albert
  • 2,462
  • 8
  • 27
  • 31
xander
  • 201
  • 1
  • 2
  • 8

2 Answers2

19

You can't do it right now in Prisma. There is createMany, updateMany and deleteMany, but no upsertMany. (Docs)

The most efficient way if you need to handle lots of data would probably be something like that:

prisma.$transaction([
  prisma.posts.deleteMany({ where: { userId: 1 } }),
  prisma.posts.createMany({
    { id: 1, title: 'first',  userId: 1 },
    { id: 2, title: 'second', userId: 1 },
    { id: 3, title: 'third',  userId: 1 },
  }),
]);

So you delete existing records and then recreate them again inside of a transaction.

Danila
  • 15,606
  • 2
  • 35
  • 67
  • Is there a more efficient way of retrieving the final updated state of the data than making a 3rd `prisma.posts.findMany({ where: { userId: 1 }})` call at the end of the transaction? – kevscript Jul 07 '22 at 11:05
  • Would the `deleteMany` cascade and delete rows from other tables? – devordem Mar 06 '23 at 12:45
  • 1
    @devordem Depends on your setup, but yes, normally it probably would – Danila Apr 24 '23 at 10:30
  • 1
    for me, the records has so many dependedncies, i can't just delete them, else I violate so many constraints. – Pencilcheck May 10 '23 at 04:39
3

Depending on the database (and schema) you use, Prisma supports an optional boolean within a createMany call: skipDuplicates, see https://www.prisma.io/docs/reference/api-reference/prisma-client-reference#createmany

Do not insert records with unique fields or ID fields that already exist. Only supported by databases that support ON CONFLICT DO NOTHING.

Martin
  • 2,754
  • 1
  • 15
  • 35
  • 5
    It's not what is required `ON CONFLICT DO NOTHING` won't update existing records. Unfortunately, this feature is not available, although it is very much in demand. – radzserg Feb 17 '23 at 01:27
  • 1
    this is not what the OP is asking for i think, as skipDuplicates doesn't "update" if existing records exists, it skips them instead. – Pencilcheck May 10 '23 at 04:40