Managing Database transactionsa using Sequelize

What are transactions?

The term transactions might make your mind go straight to working in a bank, and honestly, this is kind of what transactions are. Whenever you withdraw money from a bank,  two things happen.

1. You get cash

2. An equal amount gets deducted

Both of these tasks have to be successful for a transaction to be completed. If one happens but the other does not, then that is a non-successful transaction.

For a set of tasks to become a transaction, the following four properties have to be fulfilled. These properties are collectively called ACID.

A: Atomicity- Either all of the transactions get completed or none of them do. The atomicity basically means treating our transaction as one whole, not as a bunch of several transactions.

C: Consistency or Correctness -  Assume you have $100 in your bank account and you send $50 to your friend who already had $50. After the transaction, your friend should have $100 and you should have $50. You can see that the total amount before the transaction is the same as the total after, hence consistency is maintained.

I: Isolation - Multiple transactions happening should not mess with each other.

D: Durability - A successful transaction is stored in the database even after the system failure.

How do databases maintain these properties?

Whenever a transaction starts, the database makes a copy of the data and all the changes are done in that. If the transaction completes successfully then these changes get committed to the database. Otherwise, the database does a rollback and uses the previous version.

Let’s set up Sequelize and then we will see how to make transactions.

Open a new folder in your IDE and run the command npm init to initialize a new npm project and install express, sequelize, sequelize-cli, mysql2.

npm i express sequelize sequelize-cli mysql2

Now let’s generate files required for Sequelize, run sequelize init and your folder should look like this.

In your config/config.json file replace the username, password, and database name according to your DB credentials.

1
2
3
4
5
6
7
8
9
{
  "development": {
    "username": "root",
    "password": null,
    "database": "database_development",
    "host": "127.0.0.1",
    "dialect": "mysql"
  }
}

Now in your root folder create a file index.js and paste the following code in it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
const express = require('express');
const app = express();
const db = require("./models");
app.use(express.json());
app.use(express.urlencoded({
  extended: false
}));

db.sequelize
  .sync()
  .then((result) => {
    app.listen(3000, () => {
      console.log("Server started");
    });
  })
  .catch((err) => {
    console.log(err);
  });

This started a server at port 3000. Until now this was a basic Sequelize setup. Since transactions are an advanced topic, you should be familiar with Sequelize to keep working.

Sequelize supports two types of transaction management:

1. Unmanaged: All the commits and rollbacks will have to be done and managed by the user themselves.

2. Managed: Sequelize commits automatically if no error is thrown but rolls back if the error gets thrown.

Unmanaged Transaction:

Create user.js in the models’ folder and put the following code in it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define(
    'User', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
      },
      firstName: {
        type: DataTypes.STRING,
      },
      age: {
        type: DataTypes.INTEGER,
      },
    },
  );
  return User;
};

Now, let’s create a function in our root/index.js file called addData. Every time we save our file this function will be executed. Paste the following code in the function and make sure to call it as well.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
async function addData() {
  const transactionInstance = await sequelize.transaction();
  try {
    const user1 = await db.User.create({
      firstName: "Srajan",
      age: 21,
    }, {
      transaction: transactionInstance
    });
    const user2 = await db.User.create({
      firstName: "Kiril",
      age: 21,
    }, {
      transaction: transactionInstance
    });
    await transactionInstance.commit();
  } catch (err) {
    console.log(err);
    await transactionInstance.rollback();
  }
}

addData();

Here I have created a transaction using the variable transactionInstance and in every database action, I am passing this as the transaction object. Since it’s inside a try-catch block, once both the actions are performed then we will reach the end of the try block and if for some reason any process fails then we will hit the catch block and the rollback will happen.
Now run your server using nodemon index.js and see what you have in the MYSQL workbench and terminal.
The following is my terminal output:

And the MYSQL looks like this,

So our inserts have worked, but we still don’t know if we cancel our process mid-way, if it will roll back.
Let’s do it.
Add the following line after the first insert statement. Note that you have to place it after the first insert statement so that one insert happens and the other does not.

throw "Custom error";

It will throw an error.

As you can see a rollback occurred.Let’s check our MYSQL, no changes should have been made there.

There are no changes. Hence, the transaction is working.

Managed transaction:

In this case, we do not have to explicitly call the commit and rollback method and when we are instantiating the transaction then we pass as a callback the function that we want to execute.
Modify your addData function in the following way:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
async function addData() {
  try {

    await sequelize.transaction(async (transactionInstance) => {

      const user1 = await db.User.create({
        firstName: 'Nikhil',
        age: 21
      }, {
        transaction: transactionInstance
      });

      const user2 = await db.User.create({
        firstName: 'Kamli',
        age: 21
      }, {
        transaction: transactionInstance
      });
    });
  } catch (error) {

    console.log(error);
  }
}

addData();

As you can see, the workflow is quite similar. The only difference is that everything is inside a callback and there is no call for commit and rollback by the user.
Let’s see our terminal.

Our transactions are working perfectly. Thanks for reading.
✌✌