postgres connection from node.js

Question

I am using node-postgres in my application. I would like to know the best practices that I want to follow to ensure the stable connection.

following is the code I'm using right now,

exports.getAll = function (args, callback) {
helper.client = new pg.Client('tcp://postgres:system6:5432@192.168.143.11/abc_dev');
helper.client.connect();
helper.client.query('select count(1) as total_records from facilities', function(err,response){
    helper.client.query('select * ,'+response.rows[0].total_records+' as total_records  from facilities', 
        function(err,response){
            callback(response);
            helper.client.end.bind(helper.client);
        });
    });
};

As you can see in the code I'm connecting the DB for every request and disconnect once the query has executed. I have one more idea where I can connect the DB globally only once and execute the query using the opened connection. The code look like

helper.client = new pg.Client('tcp://postgres:system6:5432@192.168.143.11/abc_dev');
helper.client.connect();

exports.getAll = function (args, callback) {
helper.client.query('select count(1) as total_records from facilities', function(err,response){
    helper.client.query('select * ,'+response.rows[0].total_records+' as total_records  from facilities', 
        function(err,response){
            callback(response);
        });
    });
};

Here the connection never ends. As of my knowledge I am unable to decide which one is best. Please suggest.

Thanks..

Answer 1

There is an example in the node-postgres wiki. It connects whenever a request is handled:

var server = http.createServer(function(req, res, next) {
  pg.connect(function(err, client, done) {

This is what I think is right, too: Your connection should be in request scope.