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

I am writing a node.js app on Heroku and using the pg module. I can't figure out the "right" way to get a client object for each request that I need to query the database.

The documentation uses code like this:

pg.connect(conString, function(err, client) {
  // Use the client to do things here
});

But surely you don't need to call pg.connect inside every function that uses the database right? I've seen other code that does this:

var conString = process.env.DATABASE_URL || "tcp://postgres:1234@localhost/postgres";
var client = new pg.Client(conString);
client.connect();
// client is a global so you can use it anywhere now

I am leaning toward the second option since I believe the free database instance for Heroku is limited to one connection anyway, but are there any drawbacks to doing it this way? Do I need to check if my client object is still connected every time before I use it?

I'm the author of node-postgres. First, I apologize the documentation has failed to make the right option clear: that's my fault. I'll try to improve it. I wrote a Gist just now to explain this because the conversation grew too long for Twitter.

> Using pg.connect is the way to go in a web environment. > > PostgreSQL server can only handle 1 query at a time per connection. > That means if you have 1 global new pg.Client() connected to your > backend your entire app is bottleknecked based on how fast postgres > can respond to queries. It literally will line everything up, queuing > each query. Yeah, it's async and so that's alright...but wouldn't you > rather multiply your throughput by 10x? Use pg.connect set the > pg.defaults.poolSize to something sane (we do 25-100, not sure the > right number yet). > > new pg.Client is for when you know what you're doing. When you need > a single long lived client for some reason or need to very carefully > control the life-cycle. A good example of this is when using > LISTEN/NOTIFY. The listening client needs to be around and > connected and not shared so it can properly handle NOTIFY messages. > Other example would be when opening up a 1-off client to kill some > hung stuff or in command line scripts.

One very helpful thing is to centralize all access to your database in your app to one file. Don't litter pg.connect calls or new clients throughout. Have a file like db.js that looks something like this:

module.exports = {
   query: function(text, values, cb) {
      pg.connect(function(err, client, done) {
        client.query(text, values, function(err, result) {
          done();
          cb(err, result);
        })
      });
   }
}

This way you can change out your implementation from pg.connect to a custom pool of clients or whatever and only have to change things in one place.

Have a look at the node-pg-query module that does just this.

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.

I was interested in a very simple handler for this so I made my own without making it over complicated. I'm under no illusions that it's super basic but it could help some people get started. Basically, it connects, runs queries and handles errors for you.

function runQuery(queryString, callback) {
  // connect to postgres database
  pg.connect(postgresDatabase.url,function(err,client,done) {
    // if error, stop here
    if (err) {console.error(err); done(); callback(); return;}
    // execute queryString
    client.query(queryString,function(err,result) {
      // if error, stop here
      if (err) {console.error(err+'\nQuery: '+queryString); done(); callback(); return;}
      // callback to close connection
      done();
      // callback with results
      callback(result.rows);
    });
  });
}

Then you would use by calling it this way:

runQuery("SELECT * FROM table", function(result) {
  // Whatever you need to do with 'result'
}

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()