Best practices of db connection pool handling in a node js app?

Question

I'm referring to node-postgres package below, but I guess this question is rather generic.

There is this trivial example where you 1) acquire (connect) a connection (client) from the pool in the top level http request handler, 2) do all business inside of that handler and 3) release it back to the pool after you're done.

I guess it works fine for that example, but as soon as your app becomes somewhat bigger this becomes painfull soon.

I'm thinking of these two options, but I'm not quite sure...

1. do the "get client + work + release client" approach everywhere I need to talk to db.

   This seems like a good choice, but will it not lead to eating up more than one connection/client per the top http request (there are parallel async db calls in many places in my project)?

2. try to assign a globaly shared reference to one client/connection accessible via require()

   Is this a good idea and actually reasonably doable? Is it possible to nicely handle the "back to the pool release" in all ugly cases (errors in parallel async stuff for example)?

Thank you.

Answer 1

Well, I lost some time trying to figure that out. At the end, after some consideration and influenced by John Papa's code I decided use a database module like this:

var Q = require('q');
var MongoClient = require('mongodb').MongoClient;

module.exports.getDb = getDb;

var db = null;

function getDb() {
  return Q.promise(theDb);

  function theDb(resolve, reject, notify) {
    if (db) {
      resolve(db);
    } else {
      MongoClient.connect(mongourl, mongoOptions, function(err, theDb) {            
          resolve(db);
        }
      });
    }
  }
}

So, when I need to perform a query:

getDb().then(function(db) {

    //performe query here

});

At least for Mongodb this is good practice as seen here.