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...

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)?
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.

Don't do that at all. Instead, trust a high-level library like pg-promise do all that for you automatically. — vitaly-t
– vitaly-t, Commented Nov 13, 2015 at 1:34
I have worked on several web apps and can tell you that every one which uses the pin-a-db-connection-to-the-request model has inevitably ended up with scalability problems from the way it handles database resources. (Get + Use + Return) is superior in my book. So many fewer things that can go wrong. — Brandon
– Brandon, Commented Nov 17, 2015 at 19:35

Rafael P. Miranda · Accepted Answer · 2015-11-12 14:50:16Z

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.

edited Nov 12, 2015 at 14:50

answered Nov 12, 2015 at 14:02

Rafael P. Miranda

7761 gold badge11 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

M.M. Over a year ago

thank you for your answer. I have not tried your code, but I think I see the point. You are showing how to have one global db instance (point 2 in my original question). But, how is the releasing done (if with mongoclient there is such thing)?

Rafael P. Miranda Over a year ago

I unaware of the need for releasing in Mongo, forgot about that in the answer. It looks like this solution its not good for Postgres.

vitaly-t · Accepted Answer · 2015-11-17 19:29:07Z

0

The best advise would depend on the type of database and the basic framework that represents the database.

In case of Postgres, the basic framework/driver is node-postgres, which has embedded support for connection pool. That support is however low-level.

For high-level access see pg-promise, which provides automatic connection management, support for tasks, transactions and much more.

answered Nov 17, 2015 at 19:29

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

Comments

Tyler · Accepted Answer · 2016-08-24 19:27:13Z

0

Here is what has worked well for me.

var pg = require('pg');
var config = { pg : 'postgres://localhost/postgres' };

pg.connect(config.pg, function(err, client, done) {
  client.query('SELECT version();', function (err, results) {
    done();

    //do something with results.rows
  });
});

answered Aug 24, 2016 at 19:27

Tyler

7725 silver badges12 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related