Best practices for handling db connection pool in node js application?

Question

I mean node-postgres below, but I think this question is pretty general.

There is a trivial example where you 1) acquire (connect) a connection (client) from the pool at the top level of the http request handler, 2) do the whole thing inside this handler, and 3) release it back to the pool after you are done.

I think this works well for this example, but as soon as your application gets a little larger, it will soon become painful.

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

• Go to the client "get client + work + release client", where I need to talk to db.

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

• try assigning a global shared link to a single client / connection accessible via require()

  Is this a good idea and actually reasonably doable? Is it possible to beautifully handle "back to pool release" in all ugly cases (for example, errors in parallel asynchronous files)?

Thanks.

Answer 1

Well, I lost time trying to figure it out. In the end, after some consideration and under the influence of John Pap 's code , I decided to use the database module as follows:

 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 execute the request:

 getDb().then(function(db) { //performe query here }); 

At least for Mongodb, this is a good practice, as shown here .

Answer 2

Best advice will depend on the type of database and the underlying structure representing the database.

In the case of Postgres, the base node-postgres framework / driver that has built-in connection pool support. This support, however, is low level.

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

Answer 3

Here is what worked 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 }); });