What is the proper way to use the node.js postgresql module?

I am the author of pg-promise, which simplifies the use of node-postgres via promises.

It addresses the issues about the right way of connecting to and disconnecting from the database, using the connection pool implemented by node-postgres, among other things, like automated transactions.

An individual request in pg-promise boils down to just what's relevant to your business logic:

db.any('SELECT * FROM users WHERE status = $1', ['active'])
    .then(data => {
        console.log('DATA:', data);
    })
    .catch(error => {
        console.log('ERROR:', error);
    });

i.e. you do not need to deal with connection logic when executing queries, because you set up the connection only once, globally, like this:

const pgp = require('pg-promise')(/*options*/);

const cn = {
    host: 'localhost', // server name or IP address;
    port: 5432,
    database: 'myDatabase',
    user: 'myUser',
    password: 'myPassword'
};
// alternative:
// const cn = 'postgres://username:password@host:port/database';

const db = pgp(cn); // database instance;

You can find many more examples in Learn by Example tutorial, or on the project's home page.

Pool is the way to go now.Some thing like this

const { Pool } = require('pg');

    const pool = new Pool({
      connectionString: DATABASE_URL,
      ssl: false,
      max: 20,
      idleTimeoutMillis: 30000,
      connectionTimeoutMillis: 2000,
    });
    module.exports = {
        query: (text, params) => pool.query(text, params)
      }

it can be used as db.query('<BEGIN,COMMIT,ROLLBACK,your query,anything')

It is better to create a pg pool globally and each time you need to do a db operation use the client and then release it back to the pool. Once all db operations are done end the pool using pool.end()

Sample code -

let pool = new pg.Pool(dbConfig);
pool.connect(function(err, client, done) {

if (err) {
    console.error('Error connecting to pg server' + err.stack);
    callback(err);
} else {
    console.log('Connection established with pg db server');

    client.query("select * from employee", (err, res) => {

            if (err) {
                console.error('Error executing query on pg db' + err.stack);
                callback(err);
            } else {
                console.log('Got query results : ' + res.rows.length);


               async.each(res.rows, function(empRecord) {   
                        console.log(empRecord.name);
                });
            }
            client.release();

        });
}

});  

For more details, you can refer to my blog post -Source

As you can see from the documentation both options are valid, so choose whichever you prefer. As you, I would go with the second choice.

Here's how I do it, sort of an "all of the above approach"

Promise = require 'bluebird'
pg = module.exports = require 'pg'

Promise.promisifyAll pg.Client.prototype
Promise.promisifyAll pg.Client
Promise.promisifyAll pg.Connection.prototype
Promise.promisifyAll pg.Connection
Promise.promisifyAll pg.Query.prototype
Promise.promisifyAll pg.Query
Promise.promisifyAll pg

connectionString = process.env.DATABASE_URL

module.exports.queryAsync = (sql, values) ->
  pg.connectAsync connectionString
  .spread (connection, release) ->
    connection.queryAsync sql, values
    .then (result) ->
      console.log result.rows[0]
    .finally ->
      release()