convenience methods for working with postgres in node.js

db.coffee

pg = require 'pg'

executeSQL = module.exports.executeSQL = (sql, params, callback) ->
  # for executing sql outside of transactions
  throw new Error "DATABASE_URL not set" unless process.env.DATABASE_URL
  pg.connect process.env.DATABASE_URL, (err, client, done) ->
    return callback(err) if err
    client.query sql, params, (err, result) ->
      done(client) # gets called regardless
      return callback err, result

begin = module.exports.begin = (errorCallback, next) ->
  # for making and executing sql inside transactions
  throw new Error "DATABASE_URL not set" unless process.env.DATABASE_URL
  pg.connect process.env.DATABASE_URL, (err, client, done) ->
    # call errorCallback and not next on error
    return errorCallback(err) if err

    client.query "BEGIN", (err, result) ->
      if err
        # call errorCallback and not next on error
        # (so we never even make a transaction)
        done(client)
        return errorCallback(err)

      transaction =
        query: (sql, params, callback) ->
          # execute sql inside this client and therefore transaction
          client.query sql, params, callback

        rollback: (err) ->
          client.query "ROLLBACK", (rollbackError, result) ->
            # close the client
            done(client)
            # call the error callback with the original error
            errorCallback(err)

        commit: (callback) ->
          # try to commit
          client.query "COMMIT", (err, result) ->
            # rollback automatically if committing failed
            return transaction.rollback(err) if err
            # close the client
            done(client)
            # call commit's callback
            callback()

      # "return" the transaction so that rollback or commit can be called there
      next(transaction)

usage.coffee

# this is just a made-up example. Didn't actually test this.

db = require './db'

runSingleQuery = (callback) ->
  db.executeSQL "SELECT * FROM foos WHERE some_id = $1", [1], (err, result) ->
    return callback(err) if err
    # ...do something to turn result.rows into foos...
    callback null, foos

runMultipleQueries = (callback) ->
  # rollback will automatically short-circuit and call callback if something goes wrong
  db.begin callback, (transaction) ->
    sql = "INSERT INTO foos(some_id, name) VALUES($1, $2) RETURNING id"
    params = [1, "foo"]
    transaction.query sql, params, (err, result) ->
      return transaction.rollback(err) if err
      id = result.rows[0].id
      sql = "INSERT INTO bars(thing_id, name) VALUES($1, $2)"
      params = [id, "bar"]
      transaction.query sql, params, (err, result) ->
        return transaction.rollback(err) if err
        # commit will automatically roll back if something goes wrong
        transaction.commit ->
          callback(null, result)