How to perform a bulk update in Sequelize

While Sequelize doesn’t provide a bulkUpdate() method, both update() and bulkCreate() methods allow you to update multiple rows with a single method.

If you need to update multiple rows with the same values, you can use the update() method

When you need to update multiple rows with different values, you can use the bulkCreate() method.

Suppose you have a Users table with the following data:

+----+-----------+----------+
| id | firstName | status   |
+----+-----------+----------+
|  1 | Nathan    | pending  |
|  2 | Jane      | active   |
|  3 | John      | pending  |
|  4 | Lance     | disabled |
+----+-----------+----------+

This tutorial will help you learn how to use update() and bulkCreate() methods to update the table data above.

Bulk update with update() method

The update() method is the Sequelize way of generating and executing an UPDATE statement.

When you need to update multiple rows with the same value, you need to add a where option with the right condition to update the rows.

For example, suppose you want to update the status of all rows as active. You can use the following update() method:

await User.update(
  { status: "active" },
  {
    where: {
      status: ["pending", "disabled"],
    },
  }
);

Notice how the where option contains the status attribute with an array of values.

The above method will cause Sequelize to generate and execute the following SQL statement:

UPDATE
  `Users`
SET
  `status` = ?
WHERE
  `status` IN ('pending', 'disabled');

The WHERE clause uses the IN operator to include multiple values.

When you need to update the rows using the id, you can change the where option as follows:

await User.update(
  { status: "active" },
  {
    where: {
      id: [1, 2, 3],
    },
  }
);

The update() method is able to update multiple rows with the same update values.

If you need to update with different values for each/ some of the rows, you need to use the bulkCreate() method.

Bulk update with bulkCreate() method

Although the bulkCreate() method is used to INSERT many rows to the table, it has the updateOnDuplicate option to update your rows when there’s a conflict to perform the insert.

Let’s see an example of using bulkCreate() for updating rows. Suppose you want to update the status column as follows:

• id of 2 status as disabled
• id of 3 status as pending

Here’s how you do it:

await User.bulkCreate(
  [
    { id: 2, status: "disabled" },
    { id: 3, status: "pending" },
  ],
  {
    updateOnDuplicate: ["status"],
  }
);

The updateOnDuplicate option accepts an array of values. you need to put the name of the attribute (or column) you wish to update as a string inside the array.

The generated SQL query will be as shown below:

INSERT INTO `Users` (`id`, `status`)
  VALUES
    (2, 'disabled'),
    (3, 'pending')
  ON DUPLICATE KEY UPDATE
    `status` = VALUES (`status`);

If you want to update both firstName and status columns, then you can add the firstName attribute into the updateOnDuplicate array:

await User.bulkCreate(
  [
    { id: 2, firstName: "Daniel", status: "disabled" },
    { id: 3, firstName: "Jackson", status: "pending" },
  ],
  {
    updateOnDuplicate: ["firstName", "status"],
  }
);

If you don’t add the column name into the updateOnDuplicate option, then the different values in your bulkCreate() method will be ignored.

Please note that the updateOnDuplicate option is only supported by MySQL, MariaDB, SQLite version >= 3.24.0 & PostgreSQL version >= 9.5.

Other SQL database like MS SQL doesn’t support this option.

For unsupported databases, then your best option is to create an array of raw queries using sequelize.query() method and execute them using Promise.all() method.

Consider the following example:

const values = [
  { id: 2, firstName: "Daniel" },
  { id: 3, firstName: "Jackson" },
];

const statements = [];
const tableName = "Users";

for (let i = 0; i < values.length; i++) {
  statements.push(
    sequelize.query(
      `UPDATE ${tableName} 
      SET firstName='${values[i].firstName}' 
      WHERE id=${values[i].id};`
    )
  );
}
const result = await Promise.all(statements);
console.log(result); // all statements are executed

You can learn more about Sequelize raw query here:

How to run raw queries with Sequelize

And that’s how you can update multiple rows with different values in Sequelize.

You’ve learned how to update multiple rows using update() and bulkCreate() methods. Nice work! 😉