Node.js - PostgreSQL Transaction

In this article, we would like to show you how to make PostgreSQL Transaction in Node.js.

Note: at the end of this article you can find database preparation SQL queries.

In the examples below, we will perform a transaction that will transfer money from Mark's account to Kate's account.

This is how the simplified version in SQL looks like:

BEGIN

UPDATE "accounts" 
SET "balance" = "balance" - 500 
WHERE "id" = 1       -- Mark's account

UPDATE "accounts" 
SET "balance" = "balance" + 500 
WHERE "id" = 2       -- Kate's account

COMMIT

1. Node.js - PostgreSQL example with callback

const { Client } = require('pg');

const client = new Client({
    host: '127.0.0.1',
    user: 'postgres',
    database: 'database_name',
    password: 'password',
    port: 5432,
});

const executeTransaction = async (callback) => {
    await client.connect();
    try {
        await client.query('BEGIN');
        try {
            await callback(client);
            await client.query('COMMIT');
        } catch (error) {
            await client.query('ROLLBACK');
            console.error(error.stack)        
        }
    } finally {
        await client.end();
    }
};

const fromAccount = 1;
const toAccount = 2;
const transfer = 500;

executeTransaction(async (client) => {
    const { rows } = await client.query('SELECT "balance" FROM "accounts" WHERE "id" = $1', 
        [fromAccount]
    );
    const balance = rows[0].balance;
    if (balance >= transfer) {
        await client.query('UPDATE "accounts" SET "balance" = "balance" - $1 WHERE "id" = $2', 
        [transfer, fromAccount]);
        await client.query('UPDATE "accounts" SET "balance" = "balance" + $1 WHERE "id" = $2', 
        [transfer, toAccount]);
    }
});

We used a nested try catch block to avoid calling a ROLLBACK query in the catch block in the event of a failed BEGIN query.

Example 2

const { Client } = require('pg');
const client = new Client({
    host: '127.0.0.1',
    user: 'postgres',
    database: 'database_name',
    password: 'password',
    port: 5432,
});

const fromAccount = 1;
const toAccount = 2;
const transfer = 500;

(async () => {
    await client.connect();
    try {
        await client.query('BEGIN');
        try {
            const { rows } = await client.query('SELECT "balance" FROM "accounts" WHERE "id" = $1', 
            [fromAccount]);
            const balance = rows[0].balance;
            if (balance >= transfer) {
                await client.query('UPDATE "accounts" SET "balance" = "balance" - $1 WHERE "id" = $2', 
                [transfer, fromAccount]);
                await client.query('UPDATE "accounts" SET "balance" = "balance" + $1 WHERE "id" = $2', 
                [transfer, toAccount]);
            }
            await client.query('COMMIT');
        } catch (error) {
            await client.query('ROLLBACK');
            console.error(error.stack); // or throw error;
        }
    } finally {
        client.end();
    }
})();

Database preparation

create_tables.sql file:

CREATE TABLE "accounts" (
    "id" SERIAL,
    "name" VARCHAR(50) NOT NULL,
    "balance" DEC(15,2) NOT NULL,
    PRIMARY KEY ("id")
);

insert_data.sql file:

INSERT INTO "accounts"
	("name","balance")
VALUES
	('Mark', 5000),
	('Kate', 7000);