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 · Accepted Answer · 2016-05-11 14:01:20Z

1

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 ;)

answered May 11, 2016 at 14:01

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ahron Over a year ago

By http listener (with Express), I assume you mean the route. If I set up the route first, and then make the connection: it will imply that I must make a new connection inside every route - in a normal server program, there will be a large number of routes. Are you sure it is a good idea to define a new pool of clients (by using pg.connect) inside each and every route? What would you suggest, short of using promises? I can make as many requests (2 or 30 or 50) I want within the first 30 seconds, so I am not so sure it is just about leaking connection.

vitaly-t Over a year ago

@Yogesch you misunderstand what pg.connect does. It does not create a new pool, it pulls a connection from the existing pool. And then done() releases the connection back into the pool.

vitaly-t Over a year ago

@Yogesch The pool is created automatically on the first call, to be reused through the application life, unless it hasn't been used for poolIdleTimeout milliseconds.

ahron Over a year ago

Many thanks! I was starting to get rather puzzled over this. In the docs github.com/brianc/node-postgres/wiki/pg it says: "use the pg object to create pooled clients". I will edit the wiki to include something like "It only creates a pool on first call and does not create new pools on subsequent calls, it pulls a connection from the existing pool", just to avoid this sort of confusion.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related