Search code examples
node.jspostgresqlherokupg-promise

Connections using pg-promise

So I'm using pg-promise to query my database. Since I'm using heroku postgres (free version), the maximum number of connections is 20.

To connect to the db, I use

pgp(process.env.DATABASE_URL + '?poolSize=10')
  .connect()
  .then( sco => {
    dbclient = sco;
  })
  .catch( err => {
    console.error(err);
  })

I'm using the dbclient variable to run queries, e.g. 

dbclient
  .one('select ...')
  .then(() => ...)
  .catch( res.status(500).send);

Even though I'm setting the pool size to 10, the number of connections increases indefinitely and my app crashes.

How can I solve this? Do I have to release the client each time I run a query?

EDIT:

So I edited my code, this is exactly how I'm using it now, and I still have the same problem.

const pgp = require('pg-promise')();
pgp.pg.defaults.poolSize = 10;

router.get('/', (req, res) => {
  pgp(process.env.DATABASE_URL).any('select ...')
        .then((result) => res.status(200).send(result))
        .catch(err => res.status(500).send({err}));
});

Solution

  • First of all, do not use method connect at all. The database object can manage connections automatically. Method connect is there only for some very specific tasks, like setting up listeners, or to explicitely verify the connection.

    You should execute single queries directly against the db object, and you should execute chains of queries inside tasks or transactions. See also Chaining Queries.

    You can change the default pool size through the connection parameters, using max, like this: {max: 20}. See the Connection Syntax.