Proper way to use pg-postgres in node.js?

Question

I am new to pg-postgres for node.js. I am trying to figure out the proper way to make queries. Right now I have this

var client = new pg.Client(connectionString);
client.connect();

And then in each of my http request routes, I have this kind of code:

var query = client.query(sql);
query.on('row', function(row, result) {
    result.addRow(row);
});
query.on('end', function(data) { 
    if (data.rows[0].count === '1') {
        return callback();
    }
    return failedCallback(req, res);
});

Is this the way to do it? Or should I do this in each http route handler

pg.connect(conString, function(err, client) {
  // Use the client to do things here
  client.end();
});

Also do I need to end the client in each http route handler in this way?

Answer 1

Matter of choice:

It is a matter of choice. The first allows you to reuse declared client in other functions, while the second client is inside your pg.connect. I don't think you can say one is proper and the other is not. I can rephrase the question as "what is better: c.query(sql, function (err, result) { or query.on('end', function(data) {".

Regarding client.end():

If you end connection in each http route handler, you won't be able to reuse client, unless you client.connect(); again. It does not mean though that you don't have to close it at all. I believe bonding client termination to response.send() is reasonable. If you don't close connections to postgres you will reach max_connections quite fast. Again - you will probably use connections pooling instead of connecting to pg directly, but it does not mean you should generate new and new connections endlessly.

Example of both:

//https://github.com/brianc/node-postgres
var pg = require('pg');
var conString = 'postgres://n@10.1.10.199/b';
var sql = 'SELECT substr(extract(epoch from now())::text,10,4) sec, $1::int client,pg_backend_pid() pid,count(*) from pg_stat_activity where usename = current_user group by client';

var client = new pg.Client(conString);
client.connect();

var query = client.query(sql,[1]);
query.on('end', function(data) { 
  console.log(data.rows);
});
var query = client.query(sql,[1]);
query.on('end', function(data) { 
  console.log(data.rows);
  //client.end();
});

var to = setTimeout(  //we start new client with small timeout to see one session with previous client
  function() {
    pg.connect(conString, function(err, c) {
        // execute a query on our database
        c.query('select pg_sleep(0.6);', function (err, result) {
        });
        c.query(sql,[2], function (err, result) {
          if (err) throw err;
          console.log(result.rows);
        });
        c.query(sql,[4], function (err, result) {
          console.log(result.rows);
        });
        //c.end();
        to;
    });
  }
  , 500
);

var to = setTimeout(
  function() {
    var query = client.query(sql,[3]);
    query.on('end', function(data) { 
      console.log(data.rows);
      //client.end();
    });
  }
  , 1000
);

Generates:

...js>node 1.js
[ { sec: '2.31', client: 1, pid: 23327, count: '1' } ]
[ { sec: '2.32', client: 1, pid: 23327, count: '1' } ]
[ { sec: '3.29', client: 3, pid: 23327, count: '2' } ]
[ { sec: '3.41', client: 2, pid: 23328, count: '2' } ]
[ { sec: '3.42', client: 4, pid: 23328, count: '2' } ]

As you can see, client 1 and client 3 share same pid, although called in different functions, because global var client was declared. Same for 2 and 4, called async in one pg.connect share one pid.

Now should you put your code into c.query(sql, function (err, result) { or query.on('end', function(data) { depends on what you want to achieve.