While it is possible to use node-pg with just raw callbacks, as soon as you want to use transactions the chances of getting it right dwindle rapidly.
Using a promise library helps remove some of the pain. I ended up with something like this (using Q):
var Q = require('q'), pg = require('pg'), Connection = require('./Connection'); module.exports = function(connString) { this.connect = function() { var deferred = Q.defer(); pg.connect(connString, function(err, client, done) { if (err) { return deferred.reject(new Error(err)); } deferred.resolve(new Connection(client, done)); }); return deferred.promise; }; };
var Q = require('q'), pg = require('pg'); module.exports = function(client, done) { this.begin = function() { return this.query('BEGIN;', []); }; this.query = function(sql, args) { var deferred = Q.defer(); client.query(sql, args, function(err, result) { if (err) { err.message = err.message + ' (Query: ' + sql + ', Args: ' + args + ')'; return deferred.reject(err); } deferred.resolve(result.rows); }); return deferred.promise; }; this.rollback = function() { var deferred = Q.defer(); client.query('ROLLBACK', function(err) { deferred.resolve(); return done(err); }); return deferred.promise; }; this.commit = function() { var deferred = Q.defer(); client.query('COMMIT', function(err) { if (err) { deferred.reject(err); } else { deferred.resolve(); } return done(err); }); return deferred.promise; }; this.close = function() { done(); }; }
Which you can use like this:
var db = new Db(connString); return db.connect().then(function(conn) { return db.begin().then(function() { return conn.query('SELECT something FROM foo WHERE id = $1;', [id]).then(function(rows) { return conn.query('INSERT INTO ...', [...]); }).then(function() { return conn.commit(); }); }).fin(conn.close); });
And remove at least some of the risk of failing to return the connection to the pool, or otherwise bungling your error handling.
Try this one: https://github.com/vitaly-t/pg-promise