How to access clients from exported pool for release and then pool.end?

[dman777]

I am trying to gracefully stop my postgres db on process.on('SIGINT', handler) in my main index.js.

With Node Postgres, I am using a pool with the 4 clients. After reading the docs titled shut it down for closing a pool, I need to first release the clients.

From the exported pool itself(module.exports.pool = pool;) , how do I access the clients in a pool to do this?

This is how I initiate my postgres db, create the pool, and export it to other modules.

const colors = require('colors');
const pg = require('pg');

if (process.env.DOLPHIN_TEST) {
  var dbName = 'test_dolphin_db';
  var maxCon = 1
} else {
  var dbName = 'dev_dolphin_db';
  var maxCon = 4
}

const config = {
  user: 'postgres',
  database: dbName,
  host: 'localhost',
  max: maxCon,
  idleTimeoutMillis: 30000
}

const pool = new pg.Pool(config);

function poolConnected(err, client, done, resolve) {
  if(err) {
    return promiseArgs.reject(`error fetching client from pool ${err}`);
  }
  client.query('SELECT $1::int AS number', ['1'], function(err, result) {
    //call `done()` to release the client back to the pool
    done();

    if(err) {
      return console.error('error running query', err);
    }

    console.log(colors.grey(
      `Postgres is online using ${client.database} as user ${client.user}\n` + 
      `and is listening on ${client.host}`));
    promiseArgs.resolve();
  });
}

var promiseArgs = { resolve: undefined, reject: undefined };  

function initDb() {
  var promise = new Promise(function(resolve, reject) {
    promiseArgs.resolve = resolve;
    promiseArgs.reject = reject;

    pool.connect(poolConnected);

    pool.on('error', function (err, client) {
      // if an error is encountered by a client while it sits idle in the pool
      // the pool itself will emit an error event with both the error and
      // the client which emitted the original error
      // this is a rare occurrence but can happen if there is a network partition
      // between your application and the database, the database restarts, etc.
      // and so you might want to handle it and at least log it out
      console.error('idle client error', err.message, err.stack)
    });
  });
  return promise;
}

module.exports.pool = pool;
module.exports.initDb = initDb;

[charmander]

pool.end();

[dman777]

No, documentation states to release clients in the pool first. So, I need a way to access the clients from the pool to release them.

[charmander]

You release clients into the pool, not from the pool. If you’re calling done() correctly after acquiring any client as you should, pool.end() will be enough. To make doing that easier, consider making use of pg-pool’s conveniences, like query and the promise interface. Here’s a simplification of your script following that:

'use strict';

const colors = require('colors');
const pg = require('pg');

const [dbName, maxCon] =
  process.env.DOLPHIN_TEST ?
    ['test_dolphin_db', 1] :
    ['dev_dolphin_db', 4];

const config = {
  user: 'postgres',
  database: dbName,
  host: 'localhost',
  max: maxCon,
  idleTimeoutMillis: 30000,
};

const pool = new pg.Pool(config)
  .on('error', err => {
    console.error('idle client error', err.message, err.stack);
  });

async function initDb() {
  await pool.query('SELECT $1::int AS number', [1]);

  console.log(colors.grey(
    `Postgres is online using ${client.database} as user ${client.user}\n` +
    `and is listening on ${client.host}`));
}

exports.pool = pool;
exports.initDb = initDb;

[brianc]

Thanks for the help on this @charmander - closing since it seems to just be a question which now has a nice answer. 😄