pg pool - whats right way to utilise pg pool with timeout functionality

Question

I need some help regarding pg npm.

I have read many write ups and examples and have got totally confused about using the pg pool in a right way. Many of the articles are old which I read.

I would like to show you some of my code and how I have structured everything around db. I have few concerns where I need your support.

This is how I have implemented:

I establish a connection with Postgres once at the time of server startup only.

let pg = require('pg')

const db_config = {
    user : DB_USER,
    password : DB_PWD,
    database : DB_NAME,
    host : DB_HOST,
    max : 5, // max number of clients in the pool
    connectionTimeoutMillis : 5000,
    idleTimeoutMillis : 30000
};

conn = new pg.Pool(db_config);

conn.connect((err, client, done) => {
    if (err) {
        log.error(err.message);
        log.error(`could not connect to database`);
    } else {
        conn.query('SELECT 1', (err, res) => {
            done();
            if (err) {
                log.error(err)
            } else {
                log.info("connected to database");
            }
        });
    }
});

Please note conn object is global which I use every where in my program further.

Then I start my HTTP server. Every request coming to server has to run around 25 database queries on an average. I use same conn object for firing queries as below during the lifetime of a program. Every function in the code includes only following piece of the code for queries.

conn.query(query, function (err, docs) {    
    if (err) {
        log.error(err);
    } else {
        // do something 
    }
});

I am using "pg": "^6.2.2"

——————————————————

My concerns :

1. Am I utilising a pool?

2. How do I properly utilise pool?

3. Do I need to connect to pg which means conn.connect every time before I execute any query in the code?

4. How do I handle if the database is not reachable, code just hangs up on read and then times out after very long time? Is there any way I can time out after desired time?

Answer 1

I know this is an old question and for pg 6.2.2, but I'll answer it for pg 7+ (easy enough to update) because I too faced the same problem and was at my wits end.

Below is how I set it up to use a pool, using your code. pg is smart enough to know that a pool already exists when conn.connect() is used again, immediately or later. I wrap the logic in a query function I can easily reuse. Now it won't hang when there's an error but you may get unexpected data returned when an error happens, so watch out for that.

let pg = require('pg')

const db_config = {
  user : DB_USER,
  password : DB_PWD,
  database : DB_NAME,
  host : DB_HOST,
  max : 5, // max number of clients in the pool
  connectionTimeoutMillis : 5000,
  idleTimeoutMillis : 30000
};

const conn = new pg.Pool(db_config);

function query(sql, cb) {
  conn.connect((err, client, done) => {
    if (err) {
      log.error(err.message);
      log.error(`could not connect to database`);
      cb(err);
      done();
    }
    else {
      client.query(sql, (err, res) => {
        cb(res);
        done();
        if (err) {
          log.error(err)
        }
        else {
          log.info("connected to database");
        }
      });
    }
  });
}

Then to run a query, simply do:

// run a query
query("SELECT 1", function(result) {
  console.log(result);
});