pg-bind - bind PostgreSQL queries from objects
This extremely small, zero-dependency tool helps you create PostgreSQL queries easier by using objects and automated objects to values bindings. Forget the dollar sign $1
- use pg-bind.
Jump to:
Quick start
Install dependency:
npm install --save pg-bind
Require library in your code:
const bind = require('pg-bind');const query = bind('SELECT * FROM table WHERE age = :age', {age: 1});
Features:
Bind queries
With pg-bind
you can do that:
const bind = require('pg-bind');const obj = {a: 1, b: 2, c: 3};const query = bind('SELECT :a, :b, :c', obj); // Result is gonna be the same // Query: {text: 'SELECT $1, $2, $3', values: [1, 2, 3]}
And if query gets even more complicated (and it usually is):
// How would you count binding here? Aha!const bind = require('pg-bind');const query = ` WITH prepared_statement AS ( SELECT :blah, col1 FROM some_table WHERE col2 = :kek ) SELECT :colOne FROM prepared_statement`; const prepared = bind(query, { blah: 'blah', kek: 'lol', colOne: 'one'});
As you can see binding of queries is easy and queries themselves are more readable than with $ (dollar sign) bindings.
Bind multiple insert queries
If you ever wanted to insert multiple rows with one INSERT statement and concatenated strings (or joined an array of strings) for that - I'm deeply sorry.
With pg-bind
you can do bind.insert(query, arrayOfObjects)
:
// We've got an insert list somewhereconst employees = [ {name: 'lol', age: 228, role: 'loler'}, {name: 'kek', age: 1488, role: 'keker'}]; const insertQuery = bind.insert(` INSERT INTO some_table (group_id, name, age, role, whatever) VALUES (1, :name, :age, :role, 'whatever')`, employees); // insertQuery will be:// {// text: `// INSERT INTO some_table (group_id, name, age, role, whatever)// VALUES (1, $1, $2, $3, 'whatever'),// (1, $4, $5, $6, 'whatever')`,// values: ['lol', 228, 'loler', 'kek', 1488, 'keker']// }
Is it hard? Nope. Is it hard to do it on your own? Probably.
API
bind(query: String, values: Object) -> {text: String, values: Array}
Create query object supported by node-postgres with text and values properties.
- All the binding parameters must be prefixed with ':' sign (i.e.
:param
). - Bound params
- If param is not found/present in object -
null
is put instead. - If object is not passed (or is falsy) into
bind()
, an Error is thrown.
Examples:
const bind = require('pg-bind'); bind(`SELECT * FROM table WHERE col = :colVal`, {colVal: 'sammy'}]);// Result: {// text: `SELECT * FROM table WHERE col = $1`// values: ['sammy']// } bind('UPDATE TABLE somethings SET thing = :thing WHERE some = :some', {some: 'kek', thing: 'lol'});// Result: {// text: 'UPDATE TABLE somethings SET thing = $1 WHERE some = $2',// values: ['lol', 'kek']// }
bind.insert(query: String, values: Object[]) -> {text: String, values: Array}
Alias: bind.bindInsert
Create multiple/single insert query from pattern.
- Query MUST have
VALUES (...)
block, as it is used to create statements. - All binding parameters MUST be prefixed with ':' sign. However, as you can see in example above you can put any value into VALUES statement and it will be saved in each insert block
- If
values
is not an Array but an Object ->bind()
is used. So it's pretty safe on single inserts.
Examples:
const bind = require('pg-bind'); bind.insert(`INSERT INTO user_roles (user_id, role) VALUES (:userId, 'default')`, [{userId: 1}, {userId: 2}]);// Result: {// text: `INSERT INTO user_roles (user_id, role) VALUES ($1, 'default'), ($2, 'default')`// values: [1, 2]// }
Documentation
To generate documentation (and open it) run:
npm run docs # just generate docs npm run docs && open docs/index.html # generate and open docs in default application for HTML ext
Coverage
To generate coverage report (and open it) run:
npm run coverage # generate report npm run coverare && open coverage/index.html # generate and open in default application for HTML ext
Tests
To run tests type:
npm test