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

score 4 · Answer 1 · answered May 22 '18 at 13:59

4

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;

answered May 22 '18 at 13:59

BraveButter

1,408
9
22

Nide thinking right there but I thought that if I include the `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); });` part in a file, export it and import it where I do queries, then that would have the same effect as the singleton. Thanks – codebot May 22 '18 at 14:07
As far as i know you will create a new pool each time you import your connection file – BraveButter May 22 '18 at 14:11
Really? Well, that sucks. Are we sure that this changed in the newer pg versions? How can I check this? Thanks – codebot May 22 '18 at 14:21
I think that´s not ps, it´s js. You will create a new pool and overwrite the variable and not release the connections properly, this could end in many open connections to your db until you can´t connect because of them. – BraveButter May 22 '18 at 14:32
Hi there again. My project relies on the pg module, so I am still learning and experimenting. About the singleton we discussed, I think its not necessary because, you can have a file that creates a Pool once and then exports different queries and functions (but not the Pool itself), like in the doc [here](https://node-postgres.com/guides/project-structure). So the Pool is created once and the query is available everywhere else. So its the same effect as a singleton. – codebot May 29 '18 at 20:33
If you find another good solution for you feel free to add an answer too – BraveButter May 29 '18 at 20:38
I am a newbie in web development in general, so just to be sure, the solution I suggested actually has the same effects as the singleton, right? Thanks – codebot May 29 '18 at 20:56

how to disconnect a pool in pg module

1 Answers1