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:[email protected]/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:[email protected]/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..

Very interesting question. As far as I know a connection on request scope is popular in most languages. However, I'm not sure if it is the same with Node. — Sebastian vom Meer
– Sebastian vom Meer, Commented Apr 13, 2013 at 15:02

Sebastian vom Meer · Accepted Answer · 2013-04-13 15:12:44Z

3

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.

answered Apr 13, 2013 at 15:12

Sebastian vom Meer

5,2442 gold badges31 silver badges37 bronze badges

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

1 Comment

robertklep Over a year ago

Since node-postgres uses connection pooling (default pool size is 10 connections), this is a good solution. For modules which don't provide pooling, and actually have to reconnect to the database server each time, it seems a waste of resources and a global connection might work just as well (and faster).

Collectives™ on Stack Overflow

postgres connection from node.js

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related