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!

const sqb = require('sqb'),
    /* Shortcuts for more clear coding */
    and = sqb.and,
    or = sqb.or,
    innerJoin = sqb.innerJoin,
    select = sqb.select,
    raw = sqb.raw,
    serializer = sqb.serializer({
      dialect:'oracle',
      prettyPrint: true,
      namedParams: false
    });

let statement =
    select(
        'b.ID as book_id', 'b.name book_name', 'c.name category_name',
        select(raw('count(*)')).from('articles a')
            .where(and('a.book_id', '=', raw("b.id"))).alias('article_count')
    )
        .from('BOOKS b')
        .join(
            innerJoin('category c')
                .on(and('c.id', '=', raw('b.category_id')), and('c.kind', 'science'))
        )
        .where(
            and('name', 'like', /name/),
            and([
                or('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)),
            ]),
            and([
                or('c.name', '=', 'novel'),
                or('c.name', '=', 'horror'),
                or('c.name', '=', 'child'),
                or(select('name').from('category').where(and('id', 5)))
            ])
        )
        .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 name like ? and (release_date between ? and ?
        or release_date between '2015-01-01' and '2016-01-01')
    and (c.name = 'novel' or c.name = 'horror' or c.name = 'child'
        or (select name from category where id = 5) = null)
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