How to use node-postgres on a server?

Question

I am writing a Node.js web server that uses a Postgres database. I used the following for each new request:

app.get('/', function (req, res) { pg.connect(pgconnstring, function (err, client) { // ... }); }); 

But after several requests, I noticed "out of memory" errors on Heroku when trying to connect. My database has only 10 rows, so I don’t see how this can happen. All access to my database takes the following form:

 client.query('SELECT * FROM table', function (err, result) { if (err) { res.send(500, 'database error'); return; } res.set('Content-Type', 'application/json'); res.send(JSON.stringify({ data: result.rows.map(makeJSON) })); }); 

Assuming that the memory error is due to the presence of several persistent database connections, I switched to the style that I saw in several node-postgres examples to connect only once at the top of the file:

 var client = new pg.Client(pgconnstring); client.connect(); app.get('/', function (req, res) { // ... }); 

But now my requests freeze (endlessly?) When I try to execute the request after the connection is broken. (I modeled it by killing the Postgres server and returning it.)

So how do I do this?

• Connect Postgres connections correctly so that I can "reconnect" every time without running out of memory.
• The global client automatically connects after a network failure.

Answer 1

I assume that you are using the latest version of node-postgres, in which the connection pool has been greatly improved. Now you should check the connection back to the pool, or you will bleed the connections:

 app.get('/', function (req, res) { pg.connect(pgconnstring, function (err, client, done) { // do some stuff done(); }); }); 

As for error handling in the global connection (# 2, but I would use a pool):

 client.on('error', function(e){ client.connect(); // would check the error, etc in a production app }); 

The “missing” documents for all this are on the GitHub wiki .