Scenario 1: Making updates to multiple fields

#sql #sequelize #typescript

Here's an improved version of the introduction for Scenario 1:

Updating multiple rows in a database is a common task when managing data, whether you're setting the same value across several rows or handling different updates for each one. While the former is straightforward, the latter can quickly become complex and lead to performance issues due to excessive database calls.

In this article, we’ll explore how to efficiently update multiple rows with different values in a single query to minimize database overhead and improve performance.

Problem Example

Imagine you want event owners to update the prices of multiple tickets at once. The event owner provides an array of objects containing the ticket IDs and their new prices. The input might look something like this:

{
  "event_id": "my-event-conference-id",
  "ticketUpdateArr": [
    { "id": "id1", "newPrice": 400 },
    { "id": "id2", "newPrice": 700 },
    { "id": "id3", "newPrice": 790 },
    { "id": "id4", "newPrice": 1500 }
  ]
}

A straightforward implementation to handle this update might look like this:

async function bulkUpateTickets(ticketUpdateArr) {
  for (const updateObj of ticketUpdateArr) {
   await TicketsModel.update(
      {
        amount: updateObj.newPrice,
      },
      { where: { id: updateObj.id } }
    );
  }
}

In this code, we loop through each item in the array and make a separate database call to update the price for each ticket.

While this works, it’s not efficient. Each database call is expensive, and as the number of tickets grows, this function becomes significantly slower.

How Can We Improve This?

We can improve performance by moving the logic to the database and utilizing SQL CASE statements. This allows us to update all tickets in a single query.

Here’s how the query would look:


UPDATE
    tickets
SET
    amount = CASE
        WHEN id = 'id1' THEN 400
        WHEN id = 'id2' THEN 700
        WHEN id = 'id3' THEN 790
        WHEN id = 'id4' THEN 1500
        ELSE amount
    END
WHERE
    id IN ('id1', 'id2', 'id3', 'id4');

This query updates all the ticket prices in one go. The parts that come from user input are the WHEN ... THEN conditions and the list of ids in the WHERE clause.

Now, let's update our JavaScript code to dynamically build the query and make just one database call.

Improved JavaScript Code

async function bulkUpdateTickets(ticketUpdateArr) {
  if (ticketUpdateArr.length === 0) return;

  let caseStatement = "";
  const ticketIds = [];
  const replacements = {};

  for (let index = 0; index < ticketUpdateArr.length; index++) {
    const updateObj = ticketUpdateArr[index];
    const ticketIdReplacement = `ticket_id_${index}`;
    const ticketValueReplacement = `ticket_value_${index}`;

    caseStatement += /*sql*/ `
      WHEN id = :${ticketIdReplacement} THEN :${ticketValueReplacement}`;

    replacements[ticketIdReplacement] = updateObj.id;
    replacements[ticketValueReplacement] = updateObj.newPrice;
    ticketIds.push(updateObj.id);
  }

  const rawQuery = /*sql*/ `
    UPDATE
        tickets
    SET
        amount = CASE ${caseStatement}
        ELSE amount
    END
    WHERE id IN (:ticket_ids);
  `;

  await sequelize.query(rawQuery, {
    type: QueryTypes.UPDATE,
    replacements: {
      ...replacements,
      ticket_ids: ticketIds,
    },
  });
}

This code builds a dynamic SQL CASE statement based on the input array, constructing the WHEN ... THEN clauses and the list of ticket ids for the WHERE clause. Here's an example of the generated SQL:


UPDATE
    tickets
SET
    amount = CASE
        WHEN id = :ticket_id_0 THEN :ticket_value_0
        WHEN id = :ticket_id_1 THEN :ticket_value_1
        WHEN id = :ticket_id_2 THEN :ticket_value_2
        WHEN id = :ticket_id_3 THEN :ticket_value_3
        ELSE amount
    END
WHERE
    id IN (:ticket_ids);

The :ticket_id_n and :ticket_value_n placeholders will be replaced by the corresponding values in the replacements object provided to Sequelize. This dynamic construction not only prevents SQL injection but also improves performance significantly.

Performance Gains

Although the code still loops through the input array to build the query, the big improvement is that it only makes one database call for all updates. This drastically reduces the number of database interactions, making the application much faster, especially when handling a large number of tickets.

Conclusion

In my experience building backend applications and APIs, it’s common to encounter scenarios where we need to update multiple records in the database. While looping through each item and making individual calls works, it's not scalable. By using CASE statements and bulk queries, we can efficiently update multiple records in a single call, resulting in significant performance gains.