CODEWITHSUNDEEP
javascriptsqlnode.jsprismaprisma2
xander karimi
xander karimi

Reputation: 443

how to upsert many fields in prisma ORM

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?

Upvotes: 33

Views: 29666

Answers (3)

CTS_AE
CTS_AE

Reputation: 14783

Trying to deleteMany and then createMany will likely not work due to relational constraints while trying to remove records. If that's the case one of the top answers will not work for you.

Another Option

Although there is an updateMany it doesn't handle an update like we would want -- it can update many records all with the same value. So unfortunately you will have to update records one-by-one.

It's too bad that the createMany with the skipDuplicates flag doesn't return the records that were created. If that was the case we could find the difference between the returned records and update the records that were not created.

Plan of Attack

  1. Find Many
  2. Update Many
  3. Create Many
  4. Optional: Find Many again if you need the results back

Example

In this example assume we have a merchants table with a unique constraint on the merchant's name, so we can utilize that to update by without having something like an ID, this allows us to have the assumption that the record may or may not exist.

This doesn't feel great, the only win you are really getting is the createMany. You might be best to just utilize the upsert from prisma if you do not have many records. If you have a ton of records then maybe this route is worthwhile.

function async upsertMerchant(merchants: Merchant[]) {
  const merchantNames = merchants.map((merchant) => merchant.name)

  const existingMerchants = await prisma.findMany({
    where: {
      name: {
        in: merchantNames,
      },
    },
  })

  // For a quick lookup
  const existingMerchantNames = new Set(existingMerchants.map((merchant) => merchant.name))

  // This could be a reduce instead if you would like.
  // I figured this is easier to follow for most folks.
  const merchantsToCreate: Merchant[] = []

  merchants.forEach((merchant) => {
    if (existingMerchantNames.has(merchant.name)) {
      await prisma.merchant.update({
        where: {
          name: merchant.name,
        },
        data: merchant,
      })
    } else {
      merchantsToCreate.push(merchant)
    }
  })

  await prisma.merchant.createMany({
    data: merchantsToCreate,
    skipDuplicates: true, // optional, there should not be duplicates, unless other code is racing you in concurrency.
  })

  // Optional if you want all the merchants back
  // This should return all newly created and previously existing records with updated information.
  return Prisma.merchants.findMany({
    where: {
      name: {
        in: merchantNames,
      },
    },
  })
}

In Conclusion

As I stated earlier, if you have a ton of records this may be more performant on the createMany, otherwise you're stuck updating records one-by-one. If you can deleteMany that is a quicker more performant approach, but likely not an option if you have relationships.

If you're not working with a ton of records it might be easiest to just loop through and utilize a Prisma upsert. It would most definitely result in easier to read code.

Upvotes: 4

Danila
Danila

Reputation: 18476

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.

Upvotes: 23

Martin
Martin

Reputation: 3114

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.

Upvotes: 12

Related Questions