Simplified Postgres database pool access for Node.js

command-line

NODE_ENV=development node sample-usage.js

database.yml

# This lives in config/database.yml

defaults: &defaults
  host: localhost

development:
  <<: *defaults
  database: my_db_development
  user: myuser
  password: mypass

production:
  <<: *defaults
  database: my_db_production
  user: myuser
  password: mypass

postgres.js

'use strict';
/*jslint node: true */

// I keep this file in lib/postgres.js

// Make sure you have these modules available
var async = require('async');
var grunt = require('grunt');
var pg = require('pg');

var config = grunt.file.readYAML('config/database.yml');

// Common querying function
function query(conn, q, params, cb) {
  var start = new Date().getTime();
  conn.query(q, params, function(err, result) {
    if(result) { result.elapsed = new Date().getTime() - start; }
    return cb(err, result);
  });
}

// Get a handle to a database connection pool so that you can :
// db = require('postgres')(process.env.NODE_ENV)
// and then :
// db('SELECT * FROM blah WHERE id = $1', [1], function(err, result) { .... });
module.exports = function pool(name) {
  if(!config[name]) { throw 'Pool : DB configuration "' + name + '" isn\'t defined in config/database.yml'; }

  return function(q, params, cb, statsCb) {
    pg.connect(
      this,
      function(err, conn, done) {
        if(err) {
          console.log('Error fetching client from pool : ' + err);
          return (err);
        }
        var queries = [[q, params]];
        if(statsCb) {
          // If a statsCb is provided
          // Wrap query in a transaction to get Postgres transaction stats for the query
          // tables/tuples used etc - handy for seeing missing indexes and building cache expiry info
          queries = [['BEGIN', []], queries[0], ['SELECT * FROM pg_stat_xact_user_tables WHERE seq_scan + idx_scan + seq_tup_read > 0;', []], ['END', []]];
        }
        async.mapSeries(
          queries,
          function(el, cb) { query(conn, el[0], el[1], cb); },
          function(err, result) {
            // result is an array of results
            done(); // Return connection to pool
            var data = result[statsCb ? 1 : 0];
            if(statsCb) { statsCb(err, result[2].rows); }
            if(err) {
              // Most likely a malformed query
              console.log('DB error : ', err);
              console.log('DB result : ', result);
              console.log('Query : ', q, params);
              console.trace();
              return cb(err);
            }
            cb(err, data.rows);
          }
        );
      }
    );
  }.bind(config[name]);
};

sample-usage.js

'use strict';

var db = require(process.cwd() + '/lib/postgres')(process.env.NODE_ENV);

// Without stats
db('SELECT * FROM users LIMIT 20', [], function(err, result) {
  if(err) { return console.error; }
  console.log(result);
});

// With stats
db('SELECT * FROM users LIMIT 20', [], function(err, result) {
  if(err) { return console.error; }
  console.log(result);
}, function(err, result) {
  // Result is database access stats
  if(err) { return console.error; }
  console.log(result);
});