node package manager


pgsubst Build Status NPM version

pgsubst substitutes named parameters with values within PostgreSQL queries.


pgsubst(sql, params)

Substitutes named parameters with values within PostgreSQL queries.

var pgsubst = require("pgsubst");
var query = pgsubst("select name from product where id = :id", { id: 300 });

The value of query will be:

select name from product where id = 300

It’s easy to use pgsubst with node-postgres:

  pgsubst("select name from product where id = :id", {
  id: 300
  }), function(err, result) {
    // do something…

Strings and other quoted data types will be escaped correctly:

pgsubst("select id from product where name = :name", { name: "'test'" });

will yield

select id from product where name = E'\'test\''

The same parameter can be used any number of times:

pgsubst("select name from product where id = :id or other_id = :id", { id: 300 });

will yield

select name from product where id = 300 or other_id = 300


Converts JavaScript objects into equivalent strings that can be safely included in PostgreSQL query strings. pgsubst(sql, params) uses pgsubst.format(val) internally to substitute values.


will yield

pgsubst.format(new Date(1260434555444))

will yield

"E'2009-12-10 08:42:35'::timestamp with time zone"
pgsubst.format([1, 2, 3])

will yield

pgsubst.format({ id: 300 })

will yield


Supported Types

  • Array (of any of the supported types)
  • Boolean
  • Date (and moment.js moments)
  • Number
  • Object (converted to JSON)
  • String

Bugs, Contributions, etc.

Bug reports and contributions are welcome on the github page: