NodeJs and the Postgres Transaction

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.

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:

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:

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:

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

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:

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:

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:

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:

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:

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:

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

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:

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:

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

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:

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:

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: