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 commandpsql -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:
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" |
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(); |
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started