Package Exports

pg-bind

This package does not declare an exports field, so the exports above have been automatically detected and optimized by JSPM instead. If any package subpath is missing, it is recommended to post an issue to the original package (pg-bind) to support the "exports" field. If that is not possible, create a JSPM override to customize the exports field for this package.

Readme

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:

API
Docs
Tests

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 somewhere
const 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