Prisma upsert many

The Prisma ORM provides the ability to perform bulk insert and update operations using the “upsertMany” method. With this method, you can either insert multiple new records or update existing records in a single database query. Let’s dive into more details and examples.

Syntax

The syntax of the upsertMany method is as follows:

const result = await prisma.<Model>.upsertMany({
  where: {
    // Specify the conditions for the records to be updated
  },
  create: [
    // An array of new records to be inserted
  ],
  update: [
    // An array of existing records to be updated
  ]
});

Examples

Inserting Multiple Records

To insert multiple new records at once, you need to provide an array of objects representing the records in the “create” field. Here’s an example:

const result = await prisma.user.upsertMany({
  create: [
    { name: "John", age: 25 },
    { name: "Alice", age: 30 },
    { name: "Bob", age: 20 }
  ]
});

Updating Multiple Records

If you want to update multiple existing records, you should provide an array of objects representing the updates in the “update” field. Additionally, you need to specify the conditions for the records to be updated using the “where” field. Here’s an example:

const result = await prisma.user.upsertMany({
  where: {
    id: {
      in: [1, 2, 3]  // Update records with ids 1, 2, and 3
    }
  },
  update: [
    { id: 1, age: 26 },  // Update age of record with id 1
    { id: 2, age: 31 },  // Update age of record with id 2
    { id: 3, age: 21 }   // Update age of record with id 3
  ]
});

Combining Insert and Update

You can also combine both insert and update operations in a single upsertMany query. Here’s an example:

const result = await prisma.user.upsertMany({
  where: {
    id: {
      in: [1, 2, 3]  // Specify conditions for records to be updated
    }
  },
  create: [
    { name: "Eve", age: 27 },  // Insert a new record
    { name: "Chris", age: 22 } // Insert another new record
  ],
  update: [
    { id: 1, age: 26 },  // Update age of record with id 1
    { id: 2, age: 31 },  // Update age of record with id 2
    { id: 3, age: 21 }   // Update age of record with id 3
  ]
});

Result and Error Handling

The upsertMany method returns a result object with information about the executed query. This object contains the number of created and updated records. Additionally, you can handle any errors that might occur during the operation using try-catch blocks. Here’s an example:

try {
  const result = await prisma.user.upsertMany({
    create: [
      // Array of records to insert
    ],
    update: [
      // Array of updates to apply
    ]
  });

  console.log("Created:", result.counts.created);  // Number of created records
  console.log("Updated:", result.counts.updated);  // Number of updated records
} catch (error) {
  console.error("Upsert failed:", error.message);
}

Make sure to replace “<Model>” with the actual model name from your Prisma schema, such as “user”, “post”, or any other model you have defined.

Prisma upsert many