Using Transactions In Sequelize to Ensure Data Integrity

According to Wikipedia, a transaction is a single unit of work in a database management system. The concept of transactions in database systems serve two purposes:

  1. Protecting data integrity and ensuring consistency across database systems even in cases of system failure.
  2. Ensure isolation between programs accessing a database concurrently.

The first advantage relates to recovery, whereby a change in the database either happens fully or doesn’t happen. This is important if your change requires several database updates. For instance, if X happens, update database A then update B, and so on.

Let’s take an example of a money transfer service like PayPal. When transferring money between two accounts, say X and Y, PayPal has to perform several database updates. In the simplest case, the balance has to be reduced from one account followed by an increase in the balance of the receiving account.

These are two separate database operations and hence a glitch might occur and the two operations might not be completed.

This might result in a case where, say, account X has sent money but account Y has not received it. To prevent such from happening, the concept of transactions is used.

In the context of our PayPal example, the database is only updated when both operations are successfully completed (they are treated as one). If either update fails, a rollback happens.

By definition, a transaction needs to be ACID (Atomic, Consistent, Isolated, Durable). ACID properties ensure that the data stored is valid all times regardless of system failures and whatnot.

So, how can you utilize the concept of transactions in your SQL-based database using Sequelize ORM?

How To Use Transactions in Sequelize

Sequelize ORM doesn’t support transactions by default. You have to do the setup yourself. There are two ways to manage transactions in Sequelize.

You can manually setup your transactions and take control of rollbacks incase of failure. Alternatively, Sequelize provides so-called managed transactions. In managed transactions, Sequelize will automatically rollback the transaction if an error occurs.

Here’s how you can configure managed transactions using Sequelize:

// start by importing your sequelize configuration
try {
  const result = await sequelize.transaction(async (t) => {
    // if event exists, then delete it

    const deleteEventResult = await Event.destroy(
      {
        where: {
          id: eventId,
        },
      },
      { transaction: t }
    );

    // if deletion is successful, create a new archive record
    await EventArchive.create(
      {
        id: eventId,
        event_name: eventData.event_name,
        event_description: eventData.event_description,
        start_date: eventData.start_date,
        end_date: eventData.end_date,
        max_attendees: eventData.max_attendees,
        ticket_price_regular: eventData.ticket_price_regular,
        ticket_price_vip: eventData.ticket_price_vip,
        created_by: eventData.created_by,
        deleted_by: req.id,
      },
      { transaction: t }
    );

    // return response after successful archiving and deletion
    res.status(200).json({
      status: "success",
      message: "Event deleted successfully",
      data: {
        deletedItems: deleteEventResult,
      },
    });
  });
} catch (error) {
  res.status(422).json({
    status: "fail",
    message: "Error deleting event, try again later",
  });
}

From the code snippet above, we’re using a trycatch block and inserting out transactions inside the try. All sequential database updates (in this case we’re deleting data from one database, Events, then adding it to another called EventArchives).

The transaction starts when we call sequelize.transaction(async t => {}). After that, whenever we call a database-updating action in the callback function, we need to pass a second object argument { transaction: t } which we don’t typically include in database calls. This tells Sequelize that this is a transaction.

If an error occurs either in deleting or archiving our events, Sequelize will automatically rollback the changes ensuring that our database data is consistent.

References