querying postgres db with node-postgres

Question

Do I need to use pg.connect() every time I query the database? After looking over the githhub page and wiki, the examples show a query inside the pg.connect callback like this (the comments are from the github example, i did not write them)

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

The comments are confusing because it sounds like pg.connect() is creating a new client pool with each call which would obviously be inefficient. I have seen other examples in the documentation that create a client, but I want to use the client pool.

If you want to avoid the connection complexity, use pg-promise — vitaly-t
– vitaly-t, Commented Dec 12, 2015 at 22:50

Saad · Accepted Answer · 2015-12-12 08:21:37Z

2

Yea pg.connect is the recommended way of doing things. as stated in the github page: https://github.com/brianc/node-postgres. Its not creating a pool for each request, rather a new request will create a 'pool' and all subsequent queries are added to that connection, until the time-out, 30 seconds. //it will keep idle connections open for a (configurable) 30 seconds So when the app isn't being used there is no connection, but once you are getting a few queries every second, they are all queued on that connection. the time out and amount queued can be changed.

answered Dec 12, 2015 at 8:21

Saad

29k16 gold badges51 silver badges71 bronze badges

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

2 Comments

user137717 Over a year ago

Could you clarify, "Its not creating a pool for each request, rather a new request will create a 'pool' and all subsequent queries are added to that connection"? Say I have a two http routes with db calls in them. One user goes to route A, pg.connect() creates a client pool and executes the request. Now another user goes to route B (within 30 seconds). Does the pg.connect() in route B use an existing client from the pool created in route A or does it create its own pool? I imagine pg.connect() reuses connections from the existing pool and creates a pool only when needed, but I have to ask.

Saad Over a year ago

yea, route B uses an existing connection, pg-node doesn't care how you are routing or which table, as long as you are hitting the same database.

Collectives™ on Stack Overflow

querying postgres db with node-postgres

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related