Express keeping connection open?

Question

I'm using node js, express and postgresql as backend. This is the approach I used to make a rest API:

exports.schema = function (inputs, res) {
  var query = knex('schema')
    .orderBy('sch_title', 'asc')
    .select();

  query.exec(function (err, schemas) {
    if(err){
      var response = {
        message: 'Something went wrong when trying to fetch schemas',
        thrownErr: err
      };
      console.error(response);
      res.send(500, response);
    }
    if(schemas.length === 0){
      var message = 'No schemas was found';
      console.error(message);
      res.send(400, message);
      return;
    }
    res.send(200, schemas);
  });
};

It works but after a while postgres logs an error and it's no longer working:

sorry, too man clients already

Do I need a close each request somehow? Could not find any about this in the express docs. What can be wrong?

This error only occurs on production server. Not on developing machine.

Update The app only brakes in one 'module'. The rest of the app works fine. So it's only some queries that gives the error.

looks like the db conection isnt closed when it finish the query. not about express. — jmingov, May 16 '14 at 14:32
http://stackoverflow.com/questions/2757549/org-postgresql-util-psqlexception-fatal-sorry-too-many-clients-already any use? — Kev Price, May 16 '14 at 14:41
This is definitely a problem with your postgres and not with express. Can you post the relevant parts of the DB code? — jeremy, May 19 '14 at 14:43
@jeremy, well I'm just creating tables with columns. No funny things going on there. Isn't there any "close" command I need to use in my REST-api? — Joe, May 20 '14 at 07:22
You app should have only one connect call and one connection to postgres, it shouldn't ever need to be closed during typical operation — jeremy, May 20 '14 at 14:04
What version of the knex library are you using? Looks like some persistent client connection bugs were fixed recently: http://knexjs.org/#changelog — furydevoid, May 20 '14 at 14:29

score 5 · Accepted Answer · answered May 26 '14 at 01:25

Just keep one connection open for your whole app. The docs shows an example how to do this.

This code goes in your app.js...

var Knex  = require('knex');
Knex.knex = Knex.initialize({
  client: 'pg',
  connection: {
    // your connection config
  }
});

And when you want to query in your controllers/middlewares...

var knex = require('knex').knex;

exports.schema = function (req, res) {
    var query = knex('schema')
        .orderBy('sch_title', 'asc')
        .select();
    // more code...
};

If you place Knex.initialize inside an app.use or app.VERB, it gets called repeatedly for each request thus you'll end up connecting to PG multiple times.

For most cases, you don't need to do an open+query+close for every HTTP request.

Where would you suggest doing a knex.destroy() in this scenario? Or is it sufficient that if the server goes down, the connection is probably destroyed? — Tony Gutierrez, May 06 '15 at 18:05
The connection would just expire but if you want a more graceful solution, you can listen to a `SIGTERM` event using Node.JS process. Something like this: https://gist.github.com/hyubs/2dbc0265b8079948c92b — hyubs, May 13 '15 at 06:41

Express keeping connection open?

1 Answers1