How should I Connect PostgreSQL with Node.js Application

PostgreSQL is one of the most popular and advanced open-source database solutions. Within Cloudjiffy PaaS, I can install a standalone or clustered PostgreSQL database, set up its vertical and horizontal scaling and configure connection to the required application in a matter of minutes.

In this guide, we will overview a simple example of connecting a Node.js application to the PostgreSQL server.

1. Log into my Cloudjiffy account, create an environment with PostgreSQL database and add a Node.js compute node. Set up the needed scaling limits for each server.
   
   
   
   
   
   
2.  Access my Node.js server via SSH, e.g. with an embedded Web SSH client. 
   
   
   
   
   

3. Once connected, I need to install the node-postgres which is a collection of Node.js modules for interfacing with the PostgreSQL database. To do this execute the following command:
   
   

   npm install pg
   
   

   The installation will be finished in a moment.
   
   
   

Prepare a simple Node.js script to verify the connection. 

The node-postgres supports client and pool connections. A client is one static connection and the pool manages the dynamic list of client objects with the automatic reconnection function. We’ll go with the pool option that can be used in case of having or expecting several simultaneous requests.

So, create a file with the .js extension, using any text editor of my choice (e.g. vim script.js) and paste the code as follows which should establish a connection to the database and perform a query.

const { Pool } = require('pg') const pool = new Pool({ user: '{user}', host: '{host}', database: '{database}', password: '{password}', port: {port}, }) pool.query('SELECT NOW()', (err, res) => { console.log(err, res) pool.end() })

The placeholders in the script above should be adjusted using the appropriate connection information (provided within an email for my PostgreSQL container):

       

{user} - username to log into database with

{password} - password for the specified user

{host} - link to my PostgreSQL container

{database} - database to be accessed (e.g. the default one - PostgreSQL)

{port} - port number the database server listens to. The default one for PostgreSQL is 5432

As for our example we got the email like this:




Thus the script should look like below:





Note: Instead of credentials in the script’s body, the node-postgres can use the environment variables for connection to a PostgreSQL server.

These are:


  PGUSER={user}
  PGHOST={host}
  PGPASSWORD={password}
  PGDATABASE={database}
  PGPORT={port}

1. Using this script, I can check the connection to the database from my application server and request the current local time value from the database server node. Run it with the appropriate command:
   
   node script.js

   
    
   
   
   In case of a successful connection, a PostgreSQL server node time is displayed (highlighted with red). Now, I assured my database container is accessible for my Node.js application.