Node JS and pg module 'How can I really close connection?'

Question

I'm going crazy with node pg module, getting 'too many clients already' error.

My app.js file for example, manages some routes in which I query some data to postgres. app.js looks like bellow:

//First I create a client
var client = new pg.Client(connectionString);
// Then I use that client to every routes, for example:
ContPg.prototype.someController = function(req, res){
    client.connect(function(error){
        if(error) return console.error('error conectando', error); 
        // Need to close client if there's an error connecting??

        client.query(someQuery, function(e,r){
            client.end(); 
            // Here sometimes I dont end client if i need to query more data 
            if(e) return console.error('error consultando', e);
            // Do anything with result...
        })
    });
}

As I said I use that client for all routes in file pg.js, but in other files with other routes I do the same to connect to postgres (create client and use for all routes that manage that file)

Questions

Is something wrong with my code? I ended wrong client connection? If there's nothing wrong, what could be causing 'too many clients already' error?

Thanks in advance!!

Solution

The recommended pattern is to use client pooling. From the node-postgres documentation:

Generally you will access the PostgreSQL server through a pool of clients. A client takes a non-trivial amount of time to establish a new connection. A client also consumes a non-trivial amount of resources on the PostgreSQL server - not something you want to do on every http request. Good news: node-postgres ships with built in client pooling.

var pg = require('pg');
var conString = "postgres://username:password@localhost/database";

//this initializes a connection pool
//it will keep idle connections open for a (configurable) 30 seconds
//and set a limit of 20 (also configurable)
pg.connect(conString, function(err, client, done) {
  if(err) {
    return console.error('error fetching client from pool', err);
  }
  client.query('SELECT $1::int AS number', ['1'], function(err, result) {
    //call `done()` to release the client back to the pool
    done();

    if(err) {
      return console.error('error running query', err);
    }
    console.log(result.rows[0].number);
    //output: 1
  });
});

Don't forget to call done() or you'll be in trouble!

Comments

[vitaly-t]

If you were to use pg-promise, you would not have this problem at all. It opens and closes connections automatically for you.

[Jose Hermosilla Rodrigo]

Thank you! I'll try pg-promise module later. I have seen a little and it looks fine and user-friendly.

[Jose Hermosilla Rodrigo]

Ok, so I should use that pattern for every request and call done() when I don't need that client to query anymore? I will be creting a connection pool for every request. I need to end that connection pool? Sorry if stupid question, but as I said, i'm going crazy with node-postgres. Thanks!!

[jcaron]

For each (web) request where you'll need access to the Postgresql server, you call pg.connect to get a client, and once you're done with it, you call done.