Verify database connection with pg-promise when st

Question

I am building an express application that connects to a postgres database using the pg-promise module.

I would like to ensure that the database connection is successful when starting the application server. In other words, if the connection to the database fails, I'd like to throw an error.

My server.js file is as follows:

const express = require("express");

const databaseConfig= {
  "host": "localhost",
  "port": 5432,
  "database": "library_app",
  "user": "postgres"
};

const pgp = require("pg-promise")({});
const db = pgp(databaseConfig);

const app = express();
const port = 5000;

app.listen(port, (err) => {
  console.log(`running server on port: ${port}`);
});

The current configuration will start the express server regardless of whether the database connection is valid, which is not the behavior I would like.

I tried browsing the docs but couldn't find a solution. I also tried const db = pgp(databaseConfig).catch((err) => { // blow up });, but that didn't work because pgp does not return a promise.

Answer 1

I am the author of pg-promise ;) And this isn't the first time this question is asked, so I'm giving it a detailed explanation here.

When you instantiate a new database object like this:

const db = pgp(connection);

...all it does - creates the object, but it does not try to connect. The library is built on top of the connection pool, and only the actual query methods request a connection from the pool.

From the official documentation:

Object db represents the database protocol, with lazy database connection, i.e. only the actual query methods acquire and release the connection. Therefore, you should create only one global/shared db object per connection details.

However, you can ask the library to connect without executing any query, by using method connect, as shown further.

And while this method is no longer a recommended way for chaining queries, ever since support for Tasks has been introduced (as a safer approach), it still comes in handy checking for the connection in general.

I copied the example from my own post: https://github.com/vitaly-t/pg-promise/issues/81

Below is an example of doing it in two ways at the same time, so you can choose whichever approach you like better.

const initOptions = {
    // global event notification;
    error(error, e) {
        if (e.cn) {
            // A connection-related error;
            //
            // Connections are reported back with the password hashed,
            // for safe errors logging, without exposing passwords.
            console.log('CN:', e.cn);
            console.log('EVENT:', error.message || error);
        }
    }
};

const pgp = require('pg-promise')(initOptions);

// using an invalid connection string:
const db = pgp('postgresql://userName:password@host:port/database');

db.connect()
    .then(obj => {
        obj.done(); // success, release the connection;
    })
    .catch(error => {
        console.log('ERROR:', error.message || error);
    });

Outputs:

CN: postgresql://userName:########@host:port/database EVENT: getaddrinfo ENOTFOUND host host:5432 ERROR: getaddrinfo ENOTFOUND host host:5432

Every error in the library is first reported through the global error event handler, and only then the error is reported within the corresponding .catch handler.

Alternative

Instead of establishing the connection manually, you can simply execute a type of query that would always succeed for a valid connection, like the following one:

db.proc('version')
    .then(data => {
        // SUCCESS
        // data.version =
        // 'PostgreSQL 9.5.1, compiled by Visual C++ build 1800, 64-bit'
    })
    .catch(error => {
        // connection-related error
    });

API links:

• Method proc
• Method connect
• Event error