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.
Pingback: Node.js Postgres Pooling Revisited (with transactions!) | Arg! Team Blog