how to disconnect a pool in pg module

Question

So, I use the pg module in node 8.11.1 / express 4.16.3 / pg 7.4.2

I try to use the pool for my front-end (just selects) and the examples are somewhat confusing.

In connecting it uses just a new Pool and then it shows that I have to do pool.end()

const pool = new Pool({
  user: 'dbuser',
  host: 'database.server.com',
  database: 'mydb',
  password: 'secretpassword',
  port: 3211,
})

pool.query('SELECT NOW()', (err, res) => {
  console.log(err, res)
  pool.end()
})

I made my code like that and it prints Error: Cannot use a pool after calling end on the pool If I do the same query a couple of times. So, no pool.end()

In queries there is no disconnection in the examples (?)

I finally made my code like the pooling. It shows the pool.on('error', (err, client) => { function and then it uses client.release() in the pool, since "pool.query delegates directly to client.query internally" I guess?

So, what is the right way to use pool in the pg and how to disconnect after each query or failure? I came up with this

const pool = new pg.Pool({
  user: 'user',
  host: 'localhost',
  database: 'myProject',
  password: 'secret',
  port: 5432
});

pool.on('error', (err, client) => {
  console.error('error on client', err, 'on client' , client);
  process.exit(-1);
});

app.get('/', (req, res)=>{
  pool.connect()
    .then(client => {
      return client.query('select name from table')
           .then(resolved => {
              client.release();
              res.render('index',{'testData': resolved.rows});
            })
            .catch(e => { //return client.query
              client.release();
              res.render('index',{'errorData': e});
            })
      .catch(e => { //pool.connect()
        client.release();
        res.render('index',{'errorData': e});
      })
    })
});

I dont know if this can be shorter in any way. Like, for example if the catch(e => { ////pool.connect()... is needed or it is covered by pool.on('error', (err, client) => {...

Also, it could be a lot sorter if it was like

const pool = new pg.Pool({
  user: 'user',
  host: 'localhost',
  database: 'myProject',
  password: 'secret',
  port: 5432
});
app.get('/', (req, res)=>{
  pool.query('...')
  .then(resolved => {          
      pool.end();// pool disconnect ???
      res.render('index',{
        'testData': resolved.rows
      });
  })
  .catch(e => {
      pool.end();// pool disconnect ???
      res.render('index',{
        'testData': e
      });
  })
});

But I dont know if this is right because there is no pool.connect , there is no client returned from that connection and there is no function to disconnect the pool (only to end it, that ends up again with Error: Cannot use a pool after calling end on the pool).

Please advice on the right pool usage and syntax

Thanks

Answer 1

I ran into this kind of Problem too with another npm package for mssql.

After some trail and error i decided to go for a singelton (static would also be possible) class which handles the connection.

Now you just have to call one function before each query in order to create a new connection pool or receive the present one.

Something like this:

    let _this = {};
    let connectionPool = null;

    function getConnection(){
        if(connectionPool){
            return connectionPool
        } else { 
            connectionPool = new pg.Pool({
                user: 'user',
                host: 'localhost',
                database: 'myProject',
                password: 'secret',
                port: 5432
            });
            return connectionPool;
        }
    }

   function closeConnection(){
       // close connection here       
   }

    _this.getConnection = getConnection;
    _this.closeConnection = closeConnection;

   module.exports = _this;