SQL Bricks.js

As with other SQL generation libraries, SQL Bricks was created to help eliminate DRY violations in SQL-heavy applications. SQL statements can be easily stored, cloned, modified and passed around to other parts of an application and they can generate both parameterized and non-parameterized SQL.

In addition, SQL Bricks contains a few conveniences to aid in re-use and to make SQL generation a little less of a chore: automatic quoting of columns that collide with keywords (order, desc, etc), automatic alias expansion, user-supplied join criteria functions and pseudo-views.

SQL Bricks differs from similar libraries in that it does not require a schema and it is designed to be transparent, matching SQL so faithfully that developers with SQL experience will immediately know the API.

SQL Bricks supports the four CRUD statements (SELECT, INSERT, UPDATE, DELETE) and all of their clauses as defined by SQL-92 as well as some additional clauses supported by Postgres and SQLite. Adding support for other SQL statements (CREATE, ALTER TABLE, etc) would clutter the library without providing much real benefit.

The source is on GitHub and over a hundred and fifty tests are available for your perusal.

Use

In node, SQL Bricks can be accessed with require('sql-bricks'). In the browser, it can be accessed with the SqlBricks global. SQL Bricks' only dependency is Underscore.js. Throughout this documentation, it is assumed that SQL Bricks is set to a local sql variable and that top-level functions are also set to local variables (for example: var sql = SqlBricks, select = sql.select, or = sql.or, $in = sql.in;).

The API is designed to mirror SQL faithfully, with SQL keywords represented by chainable camelCase method and non-keywords passed as string arguments. To make the API easier to use, objects can also be passed in wherever there are key/value pairs.

select().from('user')
  .where(or({last_name: 'Rubble'}, $in('first_name', ['Fred', 'Wilma', 'Pebbles'])));
// SELECT * FROM user
// WHERE last_name = 'Rubble' OR first_name IN ('Fred', 'Wilma', 'Pebbles')

sqlsql(str)

The SQL Bricks namespace (saved to the local variable sql in these docs) can be called as a function to insert SQL into SQL Bricks somewhere that a value is expected (the right-hand side of WHERE criteria, or insert()/update() values):

select('*').from('user').where({'billing_addr_id': sql('mailing_addr_id')})
// SELECT * FROM user WHERE billing_addr_id = mailing_addr_id

valsql.val(value)

Wraps a value (user-supplied string, number, boolean, etc) so that it can be passed into SQL Bricks anywhere that a column is expected (the left-hand side of WHERE criteria and many other SQL Bricks APIs):

select().from('user').where(sql.val('Fred'), sql('first_name'));
// SELECT * FROM user WHERE 'Fred' = first_name

Statement

Statement is an abstract base class for all statements (SELECT, INSERT, UPDATE, DELETE) and should never be instantiated directly. It is exposed because it can be used with the instanceof operator to easily determine whether something is a SQL Bricks statement: my_var instanceof Statement.

clonestmt.clone()
Clones a statement so that subsequent modifications do not affect the original statement.

var active_users = select('*').from('user').where({'active': true});
// SELECT * FROM user WHERE active = true
var local_users = active_users.clone().where({'local': true});
// SELECT * FROM user WHERE active = true AND local = true

toStringstmt.toString()
Returns the non-parameterized SQL for the statement. This is called implicitly by Javascript when using a Statement anywhere that a string is expected (string concatenation, Array.join(), etc).

toParamsstmt.toParams(options)

Returns an object with two properties: a parameterized text string and a values array. The values are populated with anything on the right-hand side of a WHERE criteria, as well as any values passed into an insert() or update() (they can be passed explicitly with val() or opted out of with sql()):

update('user', {'first_name': 'Fred'}).where({'last_name': 'Flintstone'}).toParams();
// {"text": "UPDATE user SET first_name = $1 WHERE last_name = $2", "values": ["Fred", "Flintstone"]}

A placeholder option of '?' can be passed to generate placeholders compatible with node-sqlite3:

update('user', {'first_name': 'Fred'}).where({'last_name': 'Flintstone'}).toParams({placeholder: '?'});
// {"text": "UPDATE user SET first_name = ?1 WHERE last_name = ?2", "values": ["Fred", "Flintstone"]}

select

constructorselect(columns)

sql.select() returns a new select statement, seeded with a set of columns. It can be used with or without the new keyword. Columns can be passed in here (or appended later via sel.select() or sel.distinct()) via multiple arguments or a comma-delimited string or an array.

If no columns are specified, toString() will default to SELECT *.

selectsel.select(columns)
Appends additional columns to an existing query. Columns can be passed as multiple arguments, a comma-delimited string or an array.

distinctsel.distinct(columns)
Makes the query a SELECT DISTINCT query. For convenience, any columns passed will be appended to the query (they can be passed in the same ways as to select()).

intosel.into(tbl) Alias: intoTable

Makes the query a SELECT ... INTO query (which creates a new table with the results of the query).

intoTempsel.intoTemp(tbl) Alias: intoTempTable

Does the same as .into(), but with the addition of the TEMP keyword, making it a temporary table.

fromsel.from(tbls)
Table names can be passed in as multiple string arguments, a comma-delimited string or an array.

join, leftJoin, rightJoin, fullJoin, crossJoin
sel.join(tbl[, onCriteria])
Aliases: innerJoin, leftOuterJoin, rightOuterJoin, fullOuterJoin

Adds the specified join to the query. tbl can include an alias after a space or after the 'AS' keyword ('my_table my_alias'). onCriteria is optional if a joinCriteria function has been supplied.

select().from('user').join('address', {'user.addr_id': 'address.id'});
// SELECT * FROM user INNER JOIN address ON user.addr_id = address.id

onsel.on(onCriteria)

Note: onCriteria can also be passed as the second argument to join.

select('*').from('user').innerJoin('address').on('user.addr_id', 'address.id');
// SELECT * FROM user INNER JOIN address ON user.addr_id = address.id

select('*').from('user').join('address').on({'user.addr_id': 'address.id'});
// SELECT * FROM user INNER JOIN address ON user.addr_id = address.id

whereAlias: and
sel.where(column, value)
sel.where(criteria)
sel.where(whereExpr)

Sets or extends the WHERE clause. If there already is a WHERE clause, the new criteria will be added to a top-level AND group.

select('*').from('user').where('first_name', 'Fred');
// SELECT * FROM user WHERE first_name = 'Fred'

select('*').from('user').where('last_name', 'Flintstone').and('first_name', 'Fred');
// SELECT * FROM user WHERE last_name = 'Flintstone' AND first_name = 'Fred'

Multiple key/value pairs in a criteria object are ANDed together:

select('*').from('user').where({'last_name': 'Flintstone', 'first_name': 'Fred'});
// SELECT * FROM user WHERE last_name = 'Flintstone' AND first_name = 'Fred'

For nested groupings and for relational operators beyond =, a whereExpr can be passed in:

select('*').from('user').where(or(like('last_name', 'Flint%'), {'first_name': 'Fred'}));
// SELECT * FROM user WHERE last_name LIKE 'Flint%' OR first_name = 'Fred'

groupBysel.groupBy(columns) Alias: group
Sets or extends the GROUP BY columns. Can take multiple arguments, a single comma-delimited string or an array.

having
sel.having(column, value)
sel.having(criteria)
sel.having(whereExpr)

Sets or extends the HAVING criteria. Like where(), it takes a column / value pair, a criteria object or a whereExpr:

select('city', 'max(temp_lo)').from('weather')
  .groupBy('city').having(lt('max(temp_lo)', 40))
// SELECT city, max(temp_lo) FROM weather
// GROUP BY city HAVING max(temp_lo) < 40

(union, intersect, minus, except)[All]sel.union([stmt, ...])

Creates a compound statement by fusing together two or more queries, returns the last statement. If no stmt is passed, a new select() will be created and returned:

select().from('user').where({'last_name': 'Flintstone'}).union()
  .select().from('user').where({'last_name': 'Rubble'});
// SELECT * FROM user WHERE last_name = 'Flintstone' UNION
// SELECT * FROM user WHERE last_name = 'Rubble'

orderBysel.orderBy(columns) Alias: order

Sets or extends the list of columns in the ORDER BY clause. Columns can be passed as multiple arguments, a single comma-delimited string or an array.

limitsel.limit(count)

Adds a LIMIT clause.

offsetsel.offset(position)

Adds an OFFSET clause.

forUpdate, noWaitsel.forUpdate([tbl, ...]) / sel.noWait()

Add the FOR UPDATE clause to lock all selected records from all tables in the select (or just the tables specified), along with an optional NO WAIT at the end:

select('addr_id').from('user').forUpdate('addr_id').noWait();
// SELECT addr_id FROM user FOR UPDATE addr_id NO WAIT

joinViewsel.joinView(view_name[, onCriteria, join_type])

Joins to the main tables of the pseudo-view and merges into the query all of the view's joins and where clauses (with joined tables prefixed by the view's alias).

view_name can include an alias after a space or after the AS keyword ('my_view my_alias' or 'my_view AS my_alias'). onCriteria is optional if a joinCriteria function has been supplied. join_type defaults to 'inner' and must be supplied for other types of joins.

sql.addView('localUser',
  select().from('user')
    .join('address').on({'user.addr_id': 'address.id'})
    .where({'address.local': true})
);

select('*').from('person').joinView('localUser l_usr', {'person.usr_id': 'l_usr.id'});
// SELECT * FROM person
// INNER JOIN user l_usr ON person.usr_id = l_usr.id
// INNER JOIN address l_usr_address ON l_usr.addr_id = l_usr_address.id
// WHERE l_usr_address.local = true

insert

constructorAlias: insertInto insert(tbl[, values])
insert(tbl[, columns])

sql.insert() returns a new INSERT statement. It can be used with or without the new operator.

insert('user', {'first_name': 'Fred', 'last_name': 'Flintstone'});
// INSERT INTO user (first_name, last_name) VALUES ('Fred', 'Flintstone')

It can take a values object or a columns list. Passing a set of columns (as multiple arguments, a comma-delimited string or an array) will put the statement into split keys/values mode, where a matching array of values is expected in values():

insertInto('user', 'first_name', 'last_name').values('Fred', 'Flintstone');
// INSERT INTO user (first_name, last_name) VALUES ('Fred', 'Flintstone')

orReplace, orRollback, orAbort, orFail, orIgnore ins.orReplace(), ins.orRollback(), ins.orAbort(), ins.orFail(), ins.orIgnore()

Adds appropriate SQLite/MySQL clause to the INSERT statement:

insert().orReplace().into('user').values({'first_name': 'Fred', 'id': 33});
// INSERT OR REPLACE INTO user (first_name, id) VALUES ('Fred', 33)

intoins.into(tbl)

insertInto() (or the shorter alias insert()) is preferred over insert().into(). .into() is kept as a way to match the SQLite dialect INSERT OR REPLACE INTO:

insert().orReplace().into('user').values({'first_name': 'Fred', 'last_name': 'Flintstone'});
// INSERT OR REPLACE INTO user (first_name, last_name) VALUES ('Fred', 'Flintstone')

valuesins.values(values)

values can be an object of key/value pairs or a set of values matching a set of keys passed into insert():

insertInto('user', 'first_name', 'last_name').values('Fred', 'Flintstone');
// INSERT INTO user (first_name, last_name) VALUES ('Fred', 'Flintstone')

insertInto('user').values({'first_name': 'Fred', 'last_name': 'Flintstone'});
// INSERT INTO user (first_name, last_name) VALUES ('Fred', 'Flintstone')

Note: values can also be passed as the second argument to insert().

selectins.select(columns)

Returns a new SELECT statement that is connected to the INSERT statement, allowing chaining of select methods. When toString() / toParams() is called, it will render them both statements together as an INSERT INTO ... SELECT statement.

Note that if you want to call a method on the insert object, you have to save a reference to it before calling select(), since select() returns a reference to a child Select statement instead of the parent Insert statement.

returningins.returning(columns)

Adds a RETURNING clause to the INSERT statement.

var ins = insert('user', 'first_name, last_name');
ins.select('first_name, last_name')
.from('account');
ins.returning('account.pk');
// INSERT INTO user (first_name, last_name)
// SELECT first_name, last_name
// FROM account
// RETURNING account.pk

update

constructorupdate(tbl[, values])

sql.update() returns a new UPDATE statement. It can be used with or without the new operator.

update('user', {'first_name': 'Fred', 'last_name': 'Flintstone'});
// UPDATE user SET first_name = 'Fred', last_name = 'Flintstone'

orReplace, orRollback, orAbort, orFail, orIgnore upd.orReplace(), upd.orRollback(), upd.orAbort(), upd.orFail(), upd.orIgnore()

Adds appropriate SQLite/MySQL clause to the UPDATE statement:

update('user').orReplace().set({'first_name': 'Fred', 'id': 33});
// UPDATE OR REPLACE user SET first_name = 'Fred', id = 33

setAlias: values
upd.set(column, value)
upd.set(values)

Sets or extends the values of the UPDATE statement with a column, value pair or a values object.

update('user').set('first_name', 'Fred').set('last_name', 'Flintstone');
// UPDATE user SET first_name = 'Fred', last_name = 'Flintstone'

update('user').set({'first_name': 'Fred', 'last_name': 'Flintstone'});
// UPDATE user SET first_name = 'Fred', last_name = 'Flintstone'

Note: values can also be passed as the second argument to update().

whereAlias: and
upd.where(column, value)
upd.where(criteria)
upd.where(whereExpr)

Sets or extends the WHERE clause. See select.where() for more details.

delete

constructordelete(tbl)Alias: deleteFrom

sql.delete() returns a new DELETE statement. It can be used with or without the new operator.

fromdel.from(tbl)

Convenience method to make the delete API match the select API more closely.

usingdel.using(tbl[, ...])

Sets or extends the list of tables in the USING clause. This is DELETE's equivalent of SELECT's multi-table FROM clause.

whereAlias: and
del.where(column, value)
del.where(criteria)
del.where(whereExpr)

Sets or extends the WHERE clause. See select.where() for more details.

Where Expressions

Object{ ... }

When a non-expression object is passed somewhere a whereExpression is expected, each key/value pair will be ANDed together:

select().from('user').where({'first_name': 'Fred', 'last_name': 'Flintstone'});
// SELECT * FROM user WHERE first_name = 'Fred' AND last_name = 'Flintstone'

andand(whereExpr[, ...])

Joins the passed expressions with AND

oror(whereExpr[, ...])

Joins the passed expressions with OR:

select().from('user').where(or(like('last_name', 'Flint%'), {'first_name': 'Fred'}));
// SELECT * FROM user WHERE last_name LIKE 'Flint%' OR first_name = 'Fred'

An object with multiple key/value pairs will generate an OR:

select().from('user').where(or({'first_name': 'Fred', 'last_name': 'Rubble'}));
// SELECT * FROM user WHERE first_name = 'Fred' OR last_name = 'Rubble'

notnot(whereExpr)

Negates the expression by wrapping it in NOT (...) (if it is at the top level, the parentheses are unnecessary and will be omitted):

select().from('user').where(not($in('name', ['Fred', 'Barney', 'Wilma', 'Pebbles'])));
// SELECT * FROM user WHERE NOT name IN ('Fred', 'Barney', 'Wilma', 'Pebbles')

eq, notEq, lt, lte, gt, gte
eq(column, value)
notEq(column, value)
lt(column, value)
lte(column, value)
gt(column, value)
gte(column, value)

Generates the appropriate relational operator (=, <>, <, <=, > or >=).

select().from('user').where(gt('access', 5));
// SELECT * FROM user WHERE access > 5

betweenbetween(column, value1, value2)

Generates a BETWEEN:

select().from('user').where(between('access', 1, 5));
// SELECT * FROM user WHERE access BETWEEN 1 AND 5

isNull, isNotNullisNull(column), isNotNull(column)

Generates IS NULL and IS NOT NULL expressions:

select().from('user').where(isNull('name'));
// SELECT * FROM user WHERE name IS NULL

likelike(column, value)

Generates a LIKE expression.

select('*').from('user').where(or(like('last_name', 'Flint%'), {'first_name': 'Fred'}));
// SELECT * FROM user WHERE last_name LIKE 'Flint%' OR first_name = 'Fred'

existsexists(stmt)

Takes a stmt subquery and generates an EXISTS expression:

select().from('user').where(exists(
  select().from('address').where({'address.id': sql('user.addr_id')})));
// SELECT * FROM user WHERE EXISTS
// (SELECT * FROM address WHERE address.id = user.addr_id)

in
in(column, values)
in(column, stmt)

Generates an IN expression based on a set of values (can be an array or multiple arguments) or a stmt subquery:

select().from('user').where($in('first_name', 'Fred', 'Barney', 'Wilma'));
// SELECT * FROM user WHERE first_name IN ('Fred', 'Barney', 'Wilma')
select().from('user').where($in('addr_id', select('id').from('address')));
// SELECT * FROM user WHERE addr_id IN (SELECT id FROM address)

eqAll, notEqAll, ltAll, lteAll, gtAll, gteAll
eqAll(stmt), notEqAll(stmt), ltAll(stmt), lteAll(stmt), gtAll(stmt), gteAll(stmt)

Pairs the appropriate relational operator with the ALL keyword.

eqAny, notEqAny, ltAny, lteAny, gtAny, gteAny
eqAny(stmt), notEqAny(stmt), ltAny(stmt), lteAny(stmt), gtAny(stmt), gteAll(stmt)
Alias: eqSome, notEqSome, ltSome, lteSome, gtSome, gteSome

Pairs the appropriate relational operator with the ANY keyword:

select().from('user').where(eqAny('user.id', select('user_id').from('address')));
// SELECT * FROM user WHERE user.id = ANY (SELECT user_id FROM address)

Conveniences

aliasExpansionssql.aliasExpansions(expansions)
Registers a set of frequently-used table aliases with SQL Bricks. These table aliases can then be used by themselves in from(), join(), etc and SQL Bricks will automatically expand them to include the table name as well as the alias:

sql.aliasExpansions({'usr': 'user', 'addr': 'address', 'zip': 'zipcode', 'psn': 'person'});

select().from('usr').join('addr', {'usr.addr_id': 'addr.id'});
// SELECT * FROM user usr INNER JOIN address addr ON usr.addr_id = addr.id

joinCriteriasql.joinCriteria(func)

Sets a user-supplied function to automatically generate the .on() criteria for joins whenever it is not supplied explicitly:

var alias_expansions = {'usr': 'user', 'addr': 'address', 'zip': 'zipcode', 'psn': 'person'};
var table_to_alias = _.invert(alias_expansions);
sql.joinCriteria(function(left_tbl, left_alias, right_tbl, right_alias) {
  var criteria = {};
  criteria[left_alias + '.' + table_to_alias[right_tbl] + '_id'] = right_alias + '.id';
  return criteria;
});

select().from('user').join('address');
// SELECT * FROM user INNER JOIN address ON user.addr_id = address.id

The left_tbl passed to the join criteria generator function will always be the most recently used table -- either the most recently joined table or, if there is none, the main table in the statement. If you want to perform a "chain" of joins, where each table joins from the previous one, you can call .join() multiple times, but if you want to join from one table directly to a number of related tables, you can call .join() once and pass the table names in as separate arguments:

select().from('usr').join('addr').join('zip');
// SELECT * FROM user usr
// INNER JOIN address addr ON usr.addr_id = addr.id
// INNER JOIN zipcode zip ON addr.zip_id = zip.id

select().from('usr').join('addr', 'psn');
// SELECT * FROM user usr
// INNER JOIN address addr ON usr.addr_id = addr.id
// INNER JOIN person psn ON usr.psn_id = psn.id

If multiple tables are passed to .join(), the last one is the most recently used one and it will be used as the basis for the next .join():

select().from('usr').join('psn', 'addr').join('zip');
// SELECT * FROM user usr
// INNER JOIN person psn ON usr.psn_id = psn.id
// INNER JOIN address addr ON usr.addr_id = addr.id
// INNER JOIN zipcode zip ON addr.zip_id = zip.id

Note that this scheme doesn't support complex JOIN table layouts: if you do something like .join('psn', 'addr').join('zip') above, it is impossible to also join something to the 'psn' table. This could be achieved by adding a way to explicitly specify the table you're joining from: .join('psn', 'addr').join('zip').join('psn->employer'), but this hasn't been implemented.

addViewsql.addView(stmt)

For those databases where native views have performance issues (SQLite and MySQL, for instance), sql-bricks provides pseudo-views. (Native views are treated as subqueries in SQLite; see the "Subquery Flattening" section of the SQLite Query Planner for an idea of the performance problems.)

The stmt that will be saved as a pseudo-view must consists of a main table and, optionally, join tables and where criteria. Queries can then join to this pseudo-view via joinView().

getViewsql.getView()

Returns a view that has been previously registered with addView() so that it can be used directly or cloned and modified:

sql.addView('activeUsers', select().from('usr').where({'usr.active': true}));

sql.getView('activeUsers').clone().where({'usr.local': true})
// SELECT * FROM user usr WHERE usr.active = true AND usr.local = true

SQL Functions

There are 95 SQL functions defined in SQL-92, including AVG(), COUNT(), MIN(), MAX(), SUM(), COALESCE(), CASE(), LTRIM(), RTRIM(), UPPER() and LOWER(), among others. These can be easily used in SQL Bricks anywhere that a sql string is expected, such as in a SELECT list:

select('COUNT(*)').from('user').where({'access_level': 3});
// SELECT COUNT(*) FROM user WHERE access_level = 3

SQL functions can also be used anywhere a value is expected (in the values for an INSERT or UPDATE or in the right-hand side of a WHERE expression) via wrapping a string in the sql() function:

select().from('user').where({'level_text':
  sql("CASE WHEN level=1 THEN 'one' WHEN level=2 THEN 'two' ELSE 'other' END")});
// SELECT * FROM user WHERE level_text =
// CASE WHEN level=1 THEN 'one' WHEN level=2 THEN 'two' ELSE 'other' END

Note that column names inside SQL functions that collide with SQL keywords will not be automatically escaped -- you have to do it manually, like this:

select('COUNT("order")').from('user');
// SELECT COUNT("order") FROM user

Contributing

Before sending a pull request, please verify that all the existing tests pass and add new tests for the changes you are making. The tests can be run in node with npm test (provided npm install has been run to install the dependencies) or they can be run in the browser with browser-tests.html. All of the examples in the documentation are run as tests, in addition to the tests in tests.js.

Note that pull requests for additional SQL dialects or extensions beyond ANSI SQL, Postgres and SQLite will probably not be merged. If you would like support for a different dialect, you are welcome to maintain a dialect-specific fork.

Also, pull requests for additional SQL statements beyond the four CRUD statements (SELECT, UPDATE, INSERT, DELETE) will probably not be merged. Other SQL statements do not benefit as much from re-use and composition; the goal being to keep SQL Bricks small, sharp and low-maintenance.

Acknowledgments

Huge thanks to Brian C for his hard work supporting and maintaining node-sql and node-postgres and especially for his patience with me and my pull requests.

Also, thanks to jashkenas for underscore, which SQL Bricks depends on -- and for its documentation, which served as a model and template for these docs.

License

SQL Bricks is MIT licensed.