Node-Postgres: program does not work after 30 seconds while using Express, no problems without Express

Question

I am using node-postgres to connect to a Postgres database from NodeJS; it gives a strange error Cannot read property 'rows' of undefined after running the program for 30 seconds (after the first request to the server). The error vanishes if I don't use Express.

I have stripped down the code to reproduce the error:

  1 var conn_string = "pg://postgres@localhost:5432/foobardb";
  2 var express = require("express");
  3 var pg = require("pg");
  4 var router_home = express.Router();
  5 var app = express();
  6
  7 pg.connect(conn_string, function(err, client, done){
  8         router_home.get("/", function(req, res, next){
  9                 client.query("select * from visit", function(err, result){
 10                         done();
 11                         res.end(JSON.stringify(result.rows[0]));
 12                 });
 13         });
 14 });
 15 app.use("/", router_home);
 16 app.listen(8080);

Running curl -X GET http://localhost:8080/ on this code sample works perfectly fine the first few times, and then fails after 30 seconds (after running it the first time) and gives the error below:

TypeError: Cannot read property 'rows' of undefined
    at null.callback (/Users/yc/nodeProjects/pgExpress.js:11:33)
    at Query.handleError (/Users/yc/nodeProjects/node_modules/pg/lib/query.js:106:17)
    at null.<anonymous> (/Users/yc/nodeProjects/node_modules/pg/lib/client.js:171:26)
    at emitOne (events.js:90:13)
    at emit (events.js:182:7)
    at Socket.<anonymous> (/Users/yc/nodeProjects/node_modules/pg/lib/connection.js:59:10)
    at emitOne (events.js:90:13)
    at Socket.emit (events.js:182:7)
    at Socket.writeAfterFIN [as write] (net.js:281:8)
    at Connection.query (/Users/yc/nodeProjects/node_modules/pg/lib/connection.js:189:15)

If I rewrite the same program without using Express, it works just fine, indefinitely.

  1 var conString = "pg://postgres@localhost:5432/foobardb";
  2
  3 var http = require('http');
  4 var pg = require('pg');
  5
  6 var server = http.createServer(function(req, res) {
  7     pg.connect(conString, function(err, client, done) {
  8
  9         client.query("select * from visit", function(err, result) {
 10             done();
 11             res.end(JSON.stringify(result.rows[0]));
 12           });
 13         });
 14 });
 15 server.listen(3001);

Except the use of Express, the code is identical. I am really confused. My guess is it might have to do with caching, although why it disappears when I don't use Express is beyond me. A fix, or how to work around this (while still using Express) would be most appreciated!

vitaly-t vitaly-t · Accepted Answer · 2016-05-11T14:01:20

It is not about express, it is about your broken logic for the database connection.

In your initial code you first connect to the database, then you set the HTTP listener, then you make the query, then release the connection. On the second call already you will be leaking your connection. The logic of what you are doing is completely broken.

You are supposed to do it like this: set up the HTTP listener, and once you get the request, connect to the database, make query, release the connection.

And if you want a simpler approach where you don't need to care about the connection, check out pg-promise ;)

Node-Postgres: program does not work after 30 seconds while using Express, no problems without Express

1 Answers