pgsubst

pgsubst substitutes named parameters with values within PostgreSQL queries.

Usage

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:

pgClient.query(
  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

pgsubst.format(val)

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.

pgsubst.format('abc')

will yield

"E'abc'"

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

"ARRAY[1,2,3]"

pgsubst.format({ id: 300 })

will yield

"E'{id:300}'::json"

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: https://github.com/drjokepu/pgsubst