This article explains what SQL database transactions are, and how to implement them in Node.js.

banner

SQL Transactions are very useful when you want to perform multiple operations on a database, but still treat them as a single unit. We will go into detail on why this is useful, and how you can use it in your Node.js applications.

To illustrate our examples, we will use Postgres as our database of choice.

If you just want to see the source code, you can find it here

Transactions

A normal SQL insert query looks like this:

INSERT INTO pets (name, species) VALUES ('Fido', 'dog'), ('Albert', 'cat');

This query can represent something like adopting two new pets.

Sometimes, the event you want to record needs to be expressed as multiple queries: For example, what if someone adopted pets and bought food for them?

We could then write two queries to do just that:

INSERT INTO pets (name, species) VALUES ('Fido', 'dog'), ('Albert', 'cat');
INSERT INTO food (name, quantity) VALUES ('Dog Biscuit', 3), ('Cat Food', 5);

Now lets think about what happens if the first query succeeds, but the second query fails: you now have data which shows that two new pets are adopted, but no food has been bought.

  • We cannot treat this as a success, since one of the queries failed
  • We cannot treat this as a failure, since the first query passed. If we consider this a failure and retry, we would have to insert Fido and Albert for a second time.

Illustration showing inconsistent DB state from failed queries

To avoid situations like this, we want both the queries to pass or fail together. This is where SQL transactions come in. If you see the example below, it’s just a slight modification from executing the queries normally:

BEGIN;
INSERT INTO pets (name, species) VALUES ('Fido', 'dog'), ('Albert', 'cat');
INSERT INTO food (name, quantity) VALUES ('Dog Biscuit', 3), ('Cat Food', 5);
END;
  • The BEGIN statement starts a new transaction
  • Once the transaction has begun, SQL statements are executed one after the other, although they don’t reflect in the database just yet.
  • The END statement commits the above transactions atomically
  • Incase we want to abort the transaction in the middle, we could have used the ROLLBACK statement

Here, “atomically” means both of the SQL statements are treated as a single unit - they pass or fail together

Illustration showing transaction lifecycle

Implementing Transactions in Node.js

Now that we’ve seen how transactions are useful, let’s go through how we can implement them in our Node.js application…

Setup

Let’s create a new project to see how transactions work. To start, we will be:

  • Initializing a new project
  • Adding an index.js file
  • Installing our Postgres library pg
## Create new directory
mkdir node-sql-transactions
cd node-sql-transactions

## Create index.js file
touch index.js

## Install pq postgres driver
npm init
npm install pg

Creating Our Tables

Let’s create the pets and food tables from the previous examples. Execute the following statements from the Postgres shell:

CREATE TABLE pets (id SERIAL PRIMARY KEY,name TEXT,species TEXT);
CREATE TABLE food (id SERIAL PRIMARY KEY, name TEXT, quantity INT);

Basic Transactions

We will now make use of the pg library and implement the two insert queries we discussed in the previous example:

const { Client } = require("pg")

// create a new client and point it to your database location
// here, it points to the pet_shop database running on my
// local machine, without any password
const client = new Client("postgres://localhost:5432/pet_shop")

// initialize the db connection
client.connect()

// start the transaction using the begin statement
client
	.query("begin")
	.then((res) => {
		// next, insert some data into the pets table
		return client.query(
			"INSERT INTO pets (name, species) VALUES ($1, $2), ($3, $4)",
			["Fido", "dog", "Albert", "cat"]
		)
	})
	.then((res) => {
		// next, insert some data into the food table
		return client.query(
			"INSERT INTO food (name, quantity) VALUES ($1, $2), ($3, $4)",
			["Dog Biscuit", 3, "Cat Food", 5]
		)
	})
	.then((res) => {
		// once that's done, run the commit statement to
		// complete the transaction
		return client.query("commit")
	})
	.then((res) => {
		// if the transaction completes successfully
		// log a confirmation statement
		console.log("transaction completed")
	})
	.catch((err) => {
		// incase there are any errors encountered
		// rollback the transaction
		console.error("error while querying:", err)
		return client.query("rollback")
	})
	.catch((err) => {
		// incase there is an error when rolling back, log it
		console.error("error while rolling back transaction:", err)
	})

Here, we can commit or rollback the transaction from our code itself. This is very useful since:

  1. We can inspect the result of each query and write custom logic to decide if we should rollback or not.
  2. We can even rollback if we decide from some external factors that we don’t want to commit the transaction:
    //...
    .then(res => {
    	if (iDontFeelLikeIt()) {
    		return client.query("rollback")
      }
      //...
    })
    

Read-and-Update Transactions

In some cases, we want to update data in a table depending on the previous values stored in some other table. Transactions can help us do this atomically as well.

For example, consider the case where we want to add cat food to our inventory, and the quantity that we want to add is proportional to the number of cats that we have.

In this case, we would need to find the count of the total number of cats first, and then update the quantity of cat food.

const { Client } = require("pg")

const client = new Client("postgres://localhost:5432/pet_shop")

client.connect()

client
	.query("begin")
	.then((res) => {
		return client.query(
			"INSERT INTO pets (name, species) VALUES ($1, $2), ($3, $4)",
			["Fido", "dog", "Albert", "cat"]
		)
	})
	.then((res) => {
		return client.query("SELECT COUNT(*) FROM pets WHERE species='cat'")
	})
	.then((res) => {
		// get the total count from the result of the previous query
		const catCount = parseInt(res.rows[0].count)
		// set the query params based on the `catCount`
		return client.query(
			"UPDATE food SET quantity=quantity+$1 WHERE name='Cat Food'",
			[10 * catCount]
		)
	})
	.then((res) => {
		return client.query("commit")
	})
	.then((res) => {
		console.log("transaction completed")
	})
	.catch((err) => {
		console.error("error while querying:", err)
		return client.query("rollback")
	})
	.catch((err) => {
		console.error("error while rolling back transaction:", err)
	})

Here, we can allow our application to decide how much cat-food to add to our inventory (which in this case is 10x the number of cats).

It’s important to note why the read query is executed within the transaction: any read query outside the transaction doesn’t consider the values of an uncommitted transaction. This means that if our read query was outside the transaction, we would not consider the pets added in the first insert query.

read queries within a transaction can read updates from that transaction

Conclusion

Transactions are really useful if you want to modify data in multiple places, or use data from previous modifications. Of course, you should only use transactions when needed, since they carry their own overhead when compared to regular queries: transactions sometimes hold a lock on the row being modified, blocking other transactions from modifying it.

If you want to see the source code for these examples, you can find it on Github, and if you feel I didn’t cover something important, let me know in the comments!