when to disconnect and when to end a pg client or

Question

My stack is node, express and the pg module. I really try to understand by the documentation and some outdated tutorials. I dont know when and how to disconnect and to end a client.

For some routes I decided to use a pool. This is my code

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

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

app.get('/', (req, res)=>{
  pool.connect()
    .then(client => {
      return client.query('select ....')
            .then(resolved => {
              client.release();
              console.log(resolved.rows);
            })
            .catch(e => { 
              client.release();
              console.log('error', e);
            })
      pool.end();
    })
});

In the routes of the CMS, I use client instead of pool that has different db privileges than the pool.

const client = new pg.Client({
  user: 'clientuser',host: 'localhost',database: 'mydb',password: 'clientuser',port: 5432});    
client.connect();

const signup = (user) => {
  return new Promise((resolved, rejeted)=>{
    getUser(user.email)
    .then(getUserRes => {
      if (!getUserRes) {
        return resolved(false);
      }            
            client.query('insert into user(username, password) values ($1,$2)',[user.username,user.password])
              .then(queryRes => {
                client.end();
                resolved(true);
              })
              .catch(queryError => {
                client.end();
                rejeted('username already used');
              });
    })
    .catch(getUserError => {
      return rejeted('error');
    });
  }) 
};

const getUser = (username) => {
  return new Promise((resolved, rejeted)=>{
    client.query('select username from user WHERE username= $1',[username])
      .then(res => {
        client.end();
        if (res.rows.length == 0) {
          return resolved(true);
        }
        resolved(false);
      })
      .catch(e => {
        client.end();
        console.error('error ', e);
      });
  })
}

In this case if I get a username already used and try to re-post with another username, the query of the getUser never starts and the page hangs. If I remove the client.end(); from both functions, it will work.

I am confused, so please advice on how and when to disconnect and to completely end a pool or a client. Any hint or explanation or tutorial will be appreciated.

Thank you

Answer 1

You shouldn't disconnect the pool on every query, connection pool is supposed to be used to have "hot" connections.

I usually have a global connection on startup and the pool connection close on (if) application stop; you just have to release the connection from pool every time the query ends, as you already do, and use the same pool also in the signup function.

Sometimes I need to preserve connections, I use a wrapper to the query function that checks if the connection is active or not before perform the query, but it's just an optimization.

In case you don't want to manage open/close connections/pool or release, you could try https://github.com/vitaly-t/pg-promise, it manage all that stuff silently and it works well.

Answer 2

Its quite simple, a client-connection (single connection) opens up, query with it, once you are done you end it.

The pool concept is different, in the case of mysql : you have to .release() the connection back to the pool once you are done with it, but it seems that with pg is a different story:

From an issue on the github repo : Cannot use a pool after calling end on the pool #1635

"Cannot use a pool after calling end on the pool"

You can't reuse a pool after it has been closed (i.e. after calling the .end() function). You would need to recreate the pool and discard the old one.

The simplest way to deal with pooling in a Lambda is to not do it at all. Have your database interactions create their own connections and close them when they're done. You can't maintain a pool across freeze/thaw cycles anyway as the underlying TCP sockets would be closed.

If opening/closing the connections becomes a performance issue then look into setting up an external pool like pgbouncer.

So I would say that your best option is to not end the pool, unless you are shutting down the server

Answer 3

First, from the pg documentation*:

const { Pool } = require('pg')

const pool = new Pool()

// the pool with emit an error on behalf of any idle clients
// it contains if a backend error or network partition happens
pool.on('error', (err, client) => {
  console.error('Unexpected error on idle client', err) // your callback here
  process.exit(-1)
})

// promise - checkout a client
pool.connect()
  .then(client => {
    return client.query('SELECT * FROM users WHERE id = $1', [1]) // your query string here
      .then(res => {
        client.release()
        console.log(res.rows[0]) // your callback here
      })
      .catch(e => {
        client.release()
        console.log(err.stack) // your callback here
      })
  })

This code/construct is suficient/made to get your pool working, providing the your thing here things. If you shut down your application, the connection will hang normaly, since the pool is created well, exactly not to hang, even if it does provides a manual way of hanging, see last section of the article. Also look at the previous red section which says "You must always return the client..." to accept

• the mandatory client.release() instruction
• before accesing argument.
• you scope/closure client within your callbacks.

Then, from the pg.client documentation*:

Plain text query with a promise

const { Client } = require('pg').Client
const client = new Client()
client.connect()
client.query('SELECT NOW()') // your query string here
  .then(result => console.log(result)) // your callback here
  .catch(e => console.error(e.stack)) // your callback here
  .then(() => client.end())

seems to me the clearest syntax:

• you end the client whatever the results.
• you access the result before ending the client.
• you don´t scope/closure the client within your callbacks

It is this sort of oposition between the two syntaxes that may be confusing at first sight, but there is no magic in there, it is implementation construction syntax. Focus on your callbacks and queries, not on those constructs, just pick up the most elegant for your eyes and feed it with your code.

*I added the comments // your xxx here for clarity