Verify database connection with pg-promise when starting an app

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

As an alternative, none of this worked for me until I upgraded pg-promise package