sqb

SQB

SQB is a lightweight, multi-dialect SQL query builder for JavaScript;

Note: SQB is in alpha state. Use it only for testing purposes only!

require('sqb-serializer-oracle'); /* Loads Oracle serialization plug-in */
const sqb = require('sqb'),
    /* Shortcuts for more clear coding */
    innerJoin = sqb.innerJoin,
    select = sqb.select,
    raw = sqb.raw,
    serializer = sqb.serializer({
      dialect:'oracle',   // Use Oracle serializer
      prettyPrint: true,  // Pretty sql output
      namedParams: false  // Serialize named params (:prm) except (?)
    });

let statement =
        select(
            'b.ID as book_id', 'b.name book_name', 'c.name category_name',
            select(raw('count(*)')).from('articles a')
                .where(['a.book_id', '=', raw("b.id")]).alias('article_count')
        ).from('BOOKS b')
            .join(
                innerJoin('category c')
                    .on(['c.id', '=', raw('b.category_id')], ['c.kind', 'science'])
            )
            .where(
                ['b.id', '>=', 1],
                ['b.id', '<', 100],
                ['b.name', 'like', /name/],
                [
                    ['release_date', 'between', /release_date/], 'or',
                    ['release_date', 'between', [new Date(2015, 0, 1, 0, 0, 0, 0), new Date(2016, 0, 1, 0, 0, 0, 0)]],
                ],
                ['c.name', ['novel', 'horror', 'child']],
                [select('name').from('author').where(['id', raw('b.author_id')]), '=', 'Jessica Parker']
            )
            .orderBy("c.name", "b.release_date desc");

let result = serializer.build(statement,
    {
        name: 'WIHTE DOG',
        release_date: [new Date(2000, 0, 1, 0, 0, 0, 0), new Date(2001, 0, 1, 0, 0, 0, 0)]
    });
console.log(result.sql);
console.log(result.params);

SQL output

select b.ID book_id, b.name book_name, c.name category_name, 
    (select count(*) from articles a where a.book_id = b.id) article_count
from BOOKS b
  inner join category c on c.id = b.category_id and c.kind = 'science'
where b.id >= 1 and b.id < 100 and b.name like ? and (release_date between ? and ?
        or release_date between to_date('2015-01-01', 'yyyy-mm-dd') and to_date('2016-01-01', 'yyyy-mm-dd'))
    and c.name in ('novel','horror','child')
    and (select name from author where id = b.author_id) = 'Jessica Parker'
order by c.name, b.release_date desc

Paremeters output

[ 'WIHTE DOG', 2000-01-01T00:00:00.000Z, 2001-01-01T00:00:00.000Z ]

Getting started

Codding pattern in SQB is very similar to standard sql language.

Insert statements

SQB supports both Array and Object argument to pass values into Insert statements.

let statement = sqb.insert('id', 'name', 'address').into('BOOKS')
    .values([1, 'Hello SQB', 'The Earth']);

or

let statement = sqb.insert('id', 'name', 'address').into('BOOKS')
    .values({
      id:-1, 
      name: 'Hello SQB', 
      address:'The Earth'
    });

let result = statement.build();

result.sql output will be

insert into BOOKS (id, name, address) values (1, 'Hello SQB', 'The Earth')

Serialising with parameters

SQB can generate parameter placeholders except serializing values. To do this, just place field names in RegExp pattern.

let statement = sqb.insert('id', 'name', 'address').into('BOOKS')
    .values([/id/, /name/, /address/]);

let result = statement.build(
    {namedParams: false}, 
    [1, 'Hello SQB', 'The Earth']);

console.log(result.sql);
console.log(result.params);

result.sql output will be

insert into books (id, name, address) values (?, ?, ?)

result.params output will be

[ 1, 'Hello SQB', 'The Earth' ]

Node Compatibility

• node >= 6.x;

License

MIT