Verify database connection with pg-promise when starting an app

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 force a connection, by calling method connect, as shown further. And while this method is not a recommended way for chaining queries (Tasks should be used for that), it 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 => {
        // Can check the server version here (pg-promise v10.1.0+):
        const serverVersion = obj.client.serverVersion;

        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.

Update

Modern approach to testing connection + getting server version in one step:

// tests connection and returns Postgres server version,
// if successful; or else rejects with connection error:
async function testConnection() {
    const c = await db.connect(); // try to connect
    c.done(); // success, release connection
    return c.client.serverVersion; // return server version
}

Links

• Method connect
• Event error