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, you 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 your 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 your Node.js server via SSH, e.g. with an embedded Web SSH client. 
   
   
   
   
   

3. Once connected, you 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 your 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 your PostgreSQL container):

       

{user} - username to log into database with

{password} - password for the specified user

{host} - link to your 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, you can check the connection to the database from your 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, you are assured, your database container is accessible for your Node.js application.