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.

Solution

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

Comments

[Steven L.]

Thank you @vitaly-t for your responsiveness. This is exactly what I was looking for.

[retorquere]

How do I get the actual connection pool? There's a 3rd party lib which wants a ppol and I'd rather not set up a 2nd connection alongside the pg-promise connection I have.

[vitaly-t]

@retorquere When this answer was written there was no such thing as connection pool. The currently released version is 5.9.5, which still uses driver v5.1, which doesn't have any external pool to be shared, it only has one global internal pool. Only version 6.x uses the latest driver with its connection pool, which can be accessed via db.$pool.

[vitaly-t]

@retorquere version 6.x of pg-promise has been released, in which you can access the new pool object via db.$pool ;)

[Cmag]

@vitaly-t, thank you for explaining the e.cn and the event being thrown on connection errors. I asked a similar question here: stackoverflow.com/questions/46243680/… dealing with throwing away failing connections to replicas

[Manohar Reddy Poreddy]

@vitaly-t const db = pgp(cn2); await DB.none(..) 2nd statement is producing a lot of file logging, is becoming a performance problem. I could not find anything about disable logs in the documentation. if you are not doing this who is doing this? how can we avoid this, is there a configuration for this? thank you

[vitaly-t]

@ManoharReddyPoreddy pg-promise does not do any file logging.

[Manohar Reddy Poreddy]

@vitaly-t pgMonitor.setTheme('matrix'); is the reason for file logs, as found by my dev. Writing one comment in documentation on the same would really help save many days for other - github.com/vitaly-t/pg-monitor/tree/master/typescript

[vitaly-t]

@ManoharReddyPoreddy But pg-monitor is a separate library, and is out of scope here in this question.

[Manohar Reddy Poreddy]

@vitaly-t Yes, it is unrelated to question, but related to the owner. It was a suggestion to update documentation.

[vitaly-t]

What does package update have to do with any of this? Without any detail, it is a completely worthless addition.

[mythicalcoder]

@vitaly-t the same error ENOTFOUND occurs when connection string password has '#' character in it. Is it the case?

[Mithiridi Prasanth]

@vitaly-t how can we connect to a private rds postgres instance?