gem-hunter

Named parameters for node-postgres.

Want to use postgres with node? node-postgres has you covered. Want named parameters?

Well, postgres itself doesn't support them, and brianc has sagely opted to keep his library small and close to the postgres specification.

But SQL replete with meaningless numeric tokens (e.g. $1) isn't very readable. This module lets you monkeypatch node-postgres or node-postgres-pure to support named parameters.

Go from this...

client.query('SELECT name FROM person WHERE name = $1 AND tenure <= $2 AND age <= $3',
             ['Ursus', 2.5, 24],
             function (results) { console.log(results); });
             

to this...

client.query('SELECT name FROM person WHERE name = $name AND tenure <= $tenure AND age <= $age',
             {'name': 'Ursus', 'tenure': 2.5, 'age': 24},
             function (results) { console.log(results); });
     

Tokens are identified with \$[a-zA-Z]([a-zA-Z0-9_\-]*)\b. In other words: they must begin with a letter, and can contain only alphanumerics, underscores, and dashes.

Execution of prepared statements is also supported:

client.query({
  name   : 'select.person.byNameTenureAge',
  text   : "SELECT name FORM person WHERE name = $name AND tenure <= $tenure AND age <= $age",
  values : { 'name': 'Ursus Oestergardii',
             'tenure': 3,
             'age': 24 }
}, function (results) { console.log(results); });

Create a client as usual, then call the patch function on it. It will be patched in-place.

var pg = require('pg'); 
var named = require('node-postgres-named');
var client = new pg.Client(conString);
named.patch(client);

Now both of the above call styles (with a list of values, or a dictionary of named parameters) will work.

Inspiration provided by a conversation with Mike "ApeChimp" Atkins. Support for prepared statements added by nuarhu. Critical connection-pooling bugfix tediously diagnosed and patched by Tony "tone81" Nguyen. Mike "mfine15" Fine righted my unaesthetic mixing of double and single-quotes, and Victor Quinn fixed a spelling error.