Connection Pooling with node-postgres

Update on June 24, 2012: I submitted a patch to node-pool that exposes this pattern through a method called pooled on generic connection pools. I’ve updated the code samples below to include the more robust pattern. Take a look for Pooled function decoration in node-pool’s README.md for more information.

Documentation on node-postgres’ connection pooling is a little sparse. It is built-in but usage is somewhat unclear. Below is a minimally intrusive way to introduce it into an app.

node-postgres’ connection pooling works through the connect function on the module object:

pg = require 'pg'
pg.connect connectionString, (err, pgClient) ->
  return console.log "Error! #{err}" if err?
  # use pgClient

pgClient above is a pooled client. When a drain event occurs on the client, it is automatically returned to the pool: drain events generally occur after query has been executed on the client, unless you have suppressed drain events during a transaction. For full information, see the node-postgres documentation

Let’s assume you have a data access module with methods like this:

exports.findWidget = findWidget = (id, callback) -> ...
exports.transactedWidget = transactedWidget = (widgetSauce, callback) -> ...

The obvious way to incorporate connection pooling is something like:

pg = require 'pg'
connectionString = "tcp://postgres:postgres@localhost/dummy_db"
exports.findWidget = findWidget = (id, callback) ->
  pg.connect connectionString, (err, pgClient) ->
    return callback(err) if err?
    ... #Use pgClient to find

exports.transactedWidget = transactedWidget = (widgetSauce, callback) ->
  pg.connect connectionString, (err, pgClient) ->
    return callback(err) if err?
    ... #Use pgClient and do some transaction stuff

Frown town: three lines of useless boilerplate to every exported method in the data layer. Furthermore, trying to re-use methods in a transaction context is impossible. We can do better – create a new file called pg_pool.coffee with the following:

pg = require 'pg'

module.exports = pooler =
   #Get a connection from the pool
  acquire: (callback) -> pg.connect "tcp://postgres:postgres@localhost/dummy_db", callback

  #Decorate a function to use the de-pooled connection as a first argument
  pooled: (fn) -> ->
    callerCallback = arguments[arguments.length - 1]
    callerHasCallback = typeof callerCallback == 'function'
    callerArgs = Array::slice.call(arguments, 0, if callerHasCallback then -1 else undefined)

    pooler.acquire (err, pgClient) ->
      return (callerCallback err if err?) if err?
      pgClient.pauseDrain()
      callerArgs.push ->
        pgClient.resumeDrain()
        callerCallback.apply(null, arguments) if callerHasCallback
      fn pgClient, callerArgs...

Also available as gist. The pooled method creates a python-style decorator that will wrap data access methods in a connection pool. Furthermore, the client is kept out of the pool until the callback is executed (this is what pgClient.pauseDrain() and pgClient.resumeDrain() do in the above example). Using this, we can replace the data layer code with:

{pooled} = require './pg_pool'
exports.findWidget = pooled findWidget = (pgClient, id, callback) ->
 ... #Use pgClient to find widget
exports.transactedWidget = pooled transactedWidget = (pgClient, widgetSauce, callback) -> 
 ... #Use pgClient to do some jazz

In addition to brevity, this enabled nested method calls in transaction-context. The non-exported versions of the methods accept a pgClient parameter. For example, if transactedWidget needed to call findWidget in a transaction:

{pooled} = require 'pg_pool'
exports.findWidget = pooled findWidget = (pgClient, id, callback) ->
 ... #Use pgClient to find widget
exports.transactedWidget = pooled transactedWidget = (pgClient, widgetSauce, callback) -> 
 pgClient.query "BEGIN", (err) ->
   return callback(err) if err?  
   findWidget pgClient, "123", (err, widget) ->
     return callback(err) if err?
     pgClient.query "COMMIT", (err) ->
       callback(err, widget)

Not the prettiest code, but it beats having to write two versions of each method. Since we are using the pooled decorator, we can guarantee that the pgClient is stable for duration of this transaction.