Update several records at once with different values with sequelize

Question

I have a RESTful API. I'm trying to update several records saved in my MySQL database with different values each one. I'm developing it through of Sequelize.

const input = [
  {
    id: 'id-1',
    field1: 'data0',
    field2: 'data1',
    field3: 'data2',
  }, 
  {
    id: 'id-2',
    field1: 'data3',
    field2: 'data4',
    field3: 'data5',
  },
];
 
for (let i = 0; i < input.length; i++) {
  const element = input[i];

  await <database_manager>.<name-table>.update(element,
    { where: { id: element.id } },
  );
}

Is there better way to implement this type of updating? or Is the loop the better way to do it?

https://stackoverflow.com/a/54900639/6345936 – kusiaga Mar 29 '21 at 06:15 — kusiaga, Mar 29 '21 at 06:15

score 2 · Answer 1 · answered Mar 29 '21 at 16:01

To update several records at once (from the same table/model) you can use the Model.bulkUpdate(). Note that by default the bulk hooks will run but the "individual" hooks will not, unless you specify individualHooks: true in the options. You may also want to specify validate: true to trigger any custom validators.

If you want to update multiple tables/Models at the same time you should pass them to Promise.all() to resolve them asynchronously. Regarding the example in your question - you generally want to avoid using await in a for loop. This will cause it to run the Promises (which are asynchronous) sequentially, effectively making them synchronous. If each db call takes 1 second, and you make 3, then the total time will be ~3 seconds. If you use the following code to run them concurrently you should see a total time of ~1sec - 3x faster.

const inputs = [
  {
    model: 'TableOne',
    data: {
      id: 'id-1',
      field1: 'data0',
      field2: 'data1',
      field3: 'data2',
    },
  },
  {
    model: 'TableTwo',
    data: {
      id: 'id-2',
      field1: 'data3',
      field2: 'data4',
      field3: 'data5',
    },
  },
];

// loop over the inputs and return an array of promises, one for each update
const promises = inputs.map(input) => {
  const { model, data } = input;
  return db[model].update(data, { where: { id: data.id } });
});
// resolve all the db calls at once
await Promise.all(promises);

Thank you so much by your comment, I like the last paragraph about to use `Promise.all`. This is very fast in execution and save resource. I will use the `Promise.all`. I think that `Model.bulkUpdate` only is useful when use `Model.bulkCreate` because I looked for the example and always it show, first creation and after update. — rubenesda, Apr 03 '21 at 18:48

score 0 · Answer 2 · answered Jul 05 '23 at 10:34

To update several records at once with different values -

Here are the possible ways:

Looping with async-await : This will cause a performance issue as the number of database queries will be N and moreover there will be sequence updates for queries not parallel.
Prepare promises with iteration : Prepare all database queries promises as presented in the answer given by @doublesharp. In this approach also N database queries will be triggered but they will get executed in parallelism so latency time will get reduced as databases are capable to handle concurrent operations.
Raw SQL update query with Case-When-Then: This is one of the efficient approache as multiple updates with different values will happen only in one query.

Here is the example for the third approach with respect to asked question

/* Custom update query to update multiple rows with different values at once */
let query = `update table_name set field1 = (case`;
inputs.forEach(input => {
  query += ` when id = ${input.id} then '${input.field1}'`
});
query += ` end) where id in (${inputs.map(input => input.id).join(',')})`;

await Sequelize.query(
  query,
  {
    bind: {
      ids: 192 // This is not used in the query but can be used to inject dynamic value if needed. And this variable will be accessed in query using $ids
    },
    type: QueryTypes.UPDATE
  }
);

For more information on sequelize raw queries please refer

https://sequelize.org/docs/v6/core-concepts/raw-queries/

Hope this will help you or somebody else. Thanks!

Happy Coding :-)

Update several records at once with different values with sequelize

2 Answers2