NodeJs and the Postgres Transaction

Written by Data Pilot
November 07, 2019
  • NodeJS
  • PostgreSQL

Introduction to NodeJs and Postgres Transactions

Postgres is designed to automatically commit SQL transactions when executed. However, you can also use transaction blocks to execute a SQL statement in steps; this allows you to handle Postgres or Node exceptions with more care. The SQL keywords used to control the execution of transactions include BEGIN, ROLLBACK, and COMMIT. In this article, we’ll look at some JavaScript code and learn how to use NodeJs with Postgres transactions.

Prerequisites to using NodeJs for a Postgres transaction

Before we begin working on our example code, let’s review some prerequisites that need to be in place for this tutorial:

  • Make sure that you have already installed PostgreSQL on your device. You can verify that Postgres is installed by using the command service postgresql status.

  • Make sure that you also have psql installed on your machine. You can use the command psql -V to verify that this command-line interface for PostgreSQL is installed and running.

Screenshot of a terminal window getting the PostgreSQL server status and psql version

Check that Node and its ‘npm’ package manager are installed

If you’ve already installed NodeJs, you can use the commands node -v and npm -v to see which version of Node and its Node Package Manager are running on your machine.

Use the Node Package Manager to install ‘pg’ Postgres packages

You’ll need to use npm to install the ‘pg’ PostgreSQL client and add it to your dependencies. You can do this by running the following command in your project directory:

1
npm i pg

Be sure you have a PostgreSQL database and a table that you can query before proceeding with the tutorial.

Create a Javascript file for the NodeJs project application

Now that we’ve installed and set up everything we need, we can start working on our JavaScript. We’ll begin by creating a JavaScript file for the Node application that will execute and roll back our Postgres SQL transactions. You can use a terminal-based editor such as vim, nano or gedit, or you can use an IDE like Atom, Sublime or VS Code to create the file.

Initializing the application for the NodeJs Postgres transaction

Next, navigate to your project directory and run the npm init -y command in a terminal window to make sure that Node is initialized.

NOTE: Take care to enter the correct values when prompted. If you’d like to change anything after the initial setup, just edit the package.json file. In particular, make sure that the value for the "main" JSON field matches the name of the JavaScript file for your Node application.

Require the pg Node module for the Postgres transactions

At this point, we’re ready to open up our JavaScript file for editing. We’ll use Node’s require() function to include the pg module in our script. The following line of code declares a constant for the Postgres client’s Pool() method library:

1
const { Pool } = require("pg");

Connect to Postgres with Node by declaring a new pool

Before we can do anything with NodeJs and Postgres transactions, we’ll first have to declare a new connection pool instance. The code shown below is used to connect to PostgreSQL. When you follow along with your script, make sure to change the values that get passed to the method so that they match your Postgres credentials:

1
2
3
4
5
6
7
const pool = new Pool({
  user: "objectrocket",
  host: "localhost",
  database: "some_database",
  password: "1234",
  port: "5432"
});

We can also declare a global string for the Postgres table name:

1
2
// declare string for Postgres table name
const tableName = "some_table";

Execute a NodeJs Postgres transaction using an asynchronous function

The code shown in this article uses an asynchronous function to execute Postgres transactions. In NodeJs, the async function allows us to use the await expression; this forces the script to wait before further executing lines of code:

1
async function execute() {

Start the NodeJs Promise chain and connect to Postgres

In this section, we’ll create a Promise chain of callbacks to commit and execute our Postgres transaction. The code shown below starts the chain by connecting to Postgres with a Pool instance:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
// Promise chain for pg Pool client
const client = await pool
  .connect()

  .catch(err => {
    console.log("\nclient.connect():", err.name);

    // iterate over the error object attributes
    for (item in err) {
      if (err[item] != undefined) {
        process.stdout.write(item + " - " + err[item] + " ");
      }
    }

    // end the Pool instance
    console.log("\n");
    process.exit();
  });

This code will catch any errors while attempting to connect to the PostgreSQL database; if it encounters any, it will promptly exit the Node application.

Execute the ‘BEGIN’ keyword for the Postgres transaction

In this section, we’re introduced to the BEGIN keyword, which is used to initialize a Postgres transaction. The following code will use BEGIN with the client’s query() method after connecting to Postgres:

1
2
3
try {
// Initiate the Postgres transaction
await client.query('BEGIN')

Concatenate a string for the Postgres SQL statement

The next part of our code will use a substitution list of values to insert into our SQL string. It’s better to concatenate query strings using this method, instead of doing it manually, to prevent SQL injection attacks:

1
2
3
4
try {
// declare string for the SQL statement
const sqlString = `INSERT INTO ${tableName} (id, str, int, bool) VALUES ($1, $2, $3, $4);`
const values = ['1b62a1fa568c4c108be46b8aa7957cad', 'ObjectRocket', 42, false]

Execute an SQL statement after the query has begun

Next, we’ll pass our SQL string to another query() method to execute the transaction. We’ll have it return potential error and result objects:

1
2
3
4
// Pass SQL string to the query() method
await client.query( sqlString, values, function (err, result) {

console.log('client.query() SQL result:', result)

Check for errors before completing the Postgres transaction with ‘COMMIT’

Before we complete our Postgres transaction, we need to check if the err object has a value assigned to it.

The following code will execute the "END TRANSACTION" query in the event of an error:

1
2
3
4
5
6
7
if (err) {
  console.log("\nclient.query():", err);

  // Rollback before executing another transaction
  client.query("ROLLBACK");
  console.log("Transaction ROLLBACK called");
}

If there were no errors, we can make an API call to commit the SQL statement and complete our INSERT Postgres transaction:

1
2
3
4
5
else {
client.query('COMMIT')
console.log("client.query() COMMIT row count:", result.rowCount)
}
})

The last part of our code will catch any errors that might occur throughout the various callbacks for the Promise object. If any errors are caught, it will make the call to ROLLBACK the transaction:

1
2
3
4
5
6
} catch(er) {
// Rollback before executing another transaction
client.query('ROLLBACK')
console.log("client.query():", er)
console.log("Transaction ROLLBACK called")
}

NOTE: The ROLLBACK SQL statement is necessary because Postgres will not allow another transaction to be started until the offending one has been rolled back.

Release the Pool client instance and call the execute() function

We can use JavaScript’s finally statement, in conjunction with the try-catch error handling blocks from earlier, to release the client instance and free up the system’s resources after our Postgres transaction is complete:

1
2
3
4
5
} finally {
client.release()
console.log("Client is released")
}
}

The final step is to call the execute() function:

1
execute();

Conclusion to the NodeJs and PostgreSQL transactions

We’ve completed our script– it’s time to test it out. You can run the script using the node command followed by the JavaScript file’s name. You should see the following output logged to your console at the end if the code works as expected:

1
client.query() COMMIT row count: 1

If you run the script again, the try-catch exception blocks will return the following error if your Postgres table’s schema has a unique PRIMARY KEY constraint:

1
client.query(): error: duplicate key value violates unique constraint "some_table_pkey"

Screenshot of a Node app executing a PostgreSQL transaction

If you’re working with PostgreSQL in your NodeJs applications, it can be helpful to manage your database transactions using SQL keywords such as BEGIN, ROLLBACK and COMMIT. In this article, we walked you through a code example showing how to use NodeJs with Postgres transactions. With the examples and instructions provided in this article, you’ll be prepared to incorporate transactions into your own Node applications.

Just the Code

Shown below is the complete JavaScript code we looked at throughout this tutorial:

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
const { Pool } = require("pg");

// Instantiate a Pool connection
const pool = new Pool({
  user: "objectrocket",
  host: "localhost",
  database: "some_database",
  password: "1234",
  port: "5432"
});

// Declare string for Postgres table name
const tableName = "some_table";

// Declare an asynchronous function for the Postgres transaction
async function execute() {
  // Promise chain for pg Pool client
  const client = await pool
    .connect()

    .catch(err => {
      console.log("\nclient.connect():", err.name);

      // iterate over the error object attributes
      for (item in err) {
        if (err[item] != undefined) {
          process.stdout.write(item + " - " + err[item] + " ");
        }
      }

      // end the Pool instance
      console.log("\n");
      process.exit();
    });

  try {
    // Initiate the Postgres transaction
    await client.query("BEGIN");

    try {
      // declare string for the SQL statement
      const sqlString = `INSERT INTO ${tableName} (id, str, int, bool) VALUES ($1, $2, $3, $4);`;
      const values = [
        "1b62a1fa568c4c108be46b8aa7957cad",
        "ObjectRocket",
        42,
        false
      ];

      // Pass SQL string to the query() method
      await client.query(sqlString, values, function(err, result) {
        console.log("client.query() SQL result:", result);

        if (err) {
          console.log("\nclient.query():", err);

          // Rollback before executing another transaction
          client.query("ROLLBACK");
          console.log("Transaction ROLLBACK called");
        } else {
          client.query("COMMIT");
          console.log("client.query() COMMIT row count:", result.rowCount);
        }
      });
    } catch (er) {
      // Rollback before executing another transaction
      client.query("ROLLBACK");
      console.log("client.query():", er);
      console.log("Transaction ROLLBACK called");
    }
  } finally {
    client.release();
    console.log("Client is released");
  }
}

execute();
Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.