SQL Bricks.js

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('person')
  .where(or({last_name: 'Rubble'}, $in('first_name', ['Fred', 'Wilma', 'Pebbles'])));
// SELECT * FROM person
// WHERE last_name = 'Rubble' OR first_name IN ('Fred', 'Wilma', 'Pebbles')

sqlsql(str[, values])

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('person').where({'billing_addr_id': sql('mailing_addr_id')})
// SELECT * FROM person WHERE billing_addr_id = mailing_addr_id

Multiple `values` can be passed in as separate arguments or as an array (this example uses the postgresql [jsonb contains operator](http://www.postgresql.org/docs/9.4/static/functions-json.html#FUNCTIONS-JSONB-OP-TABLE)):

select().where(sql('field @> $ and field @> $', { key: 'value' }, { key: 'value2' })).toParams()
// {"text": "SELECT * WHERE field @> $1 and field @> $2", "values": [{"key": "value"}, {"key": "value2"}]}

Note that numbered params can be used in the literal SQL string and these refer to the position in the current `sql()` call, not the position in the statement as a whole:

select().where({name: 'Fred'}).and(sql('f1 @> $2 and f2 @> $1', [{key: 'value' }, {key: 'value2'}])).toParams()
// {"text": "SELECT * WHERE name = $1 AND f1 @> $3 and f2 @> $2", "values": ["Fred", {"key": "value"}, {"key": "value2"}]}

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('person').where(sql.val('Fred'), sql('first_name'));
// SELECT * FROM person 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_persons = select('*').from('person').where({'active': true});
// SELECT * FROM person WHERE active = TRUE
var local_persons = active_persons.clone().where({'local': true});
// SELECT * FROM person 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).
While toString() is easy to use, it is not recommended in most cases because:

• It doesn't provide robust protection against SQL injection attacks (it just does basic escaping)
• It doesn't provide as much support for complex data types (objects, arrays, etc, are "stringified" before being passed to your database driver, which then has to interpret them correctly)
• It does not provide the same level of detail in error messages (see this issue)

For the above reasons, it is usually better to use toParams().

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('person', {'first_name': 'Fred'}).where({'last_name': 'Flintstone'}).toParams();
// {"text": "UPDATE person SET first_name = $1 WHERE last_name = $2", "values": ["Fred", "Flintstone"]}

A placeholder option of '?%d' can be passed to generate placeholders compatible with node-sqlite3 (%d is replaced with the parameter #):

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

Or node-mysql:

update('person', {'first_name': 'Fred'}).where({'last_name': 'Flintstone'}).toParams({placeholder: '?'});
// {"text": "UPDATE person SET first_name = ? WHERE last_name = ?", "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.

Note: crossJoin will ignore any onCriteria argument, if supplied.

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

onsel.on(onCriteria)

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

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

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

usingsel.using(columnList)

Joins using USING instead of ON. columnList can be passed in as one or more string arguments, a comma-delimited string, or an array.

select('*').from('person').innerJoin('address').using('address_id');
// SELECT * FROM person INNER JOIN address USING (address_id)

select('*').from('person').join('address').using('address_id', 'country_id');
// SELECT * FROM person INNER JOIN address USING (address_id, country_id)

select('*').from('person').join('address').using('address_id, country_id');
// SELECT * FROM person INNER JOIN address USING (address_id, country_id)

select('*').from('person').join('address').using(['address_id', 'country_id']);
// SELECT * FROM person INNER JOIN address USING (address_id, country_id)

Note: columnList can also be passed as the second argument to join - in which case it must be specified as an array of one or more string values.

select('*').from('person').join('address', ['address_id', 'country_id']);
// SELECT * FROM person INNER JOIN address USING (address_id, country_id)

naturalJoin, naturalLeftJoin, naturalRightJoin, naturalFullJoin
sel.naturalJoin(tbl)
Aliases: naturalInnerJoin, naturalLeftOuterJoin, naturalRightOuterJoin, naturalFullOuterJoin

Adds the specified natural join to the query. tbl can include an alias after a space or after the 'AS' keyword ('my_table my_alias').

select().from('person').naturalJoin('address');
// SELECT * FROM person NATURAL INNER JOIN address

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('person').where('first_name', 'Fred');
// SELECT * FROM person WHERE first_name = 'Fred'

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

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

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

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

select('*').from('person').where(or(like('last_name', 'Flint%'), {'first_name': 'Fred'}));
// SELECT * FROM person 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('person').where({'last_name': 'Flintstone'}).union()
  .select().from('person').where({'last_name': 'Rubble'});
// SELECT * FROM person WHERE last_name = 'Flintstone' UNION
// SELECT * FROM person 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.

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('person').forUpdate().of('addr_id').noWait();
// SELECT addr_id FROM person FOR UPDATE OF addr_id NO WAIT

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('person', {'first_name': 'Fred', 'last_name': 'Flintstone'});
// INSERT INTO person (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('person', 'first_name', 'last_name').values('Fred', 'Flintstone');
// INSERT INTO person (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('person', 'first_name', 'last_name').values('Fred', 'Flintstone');
// INSERT INTO person (first_name, last_name) VALUES ('Fred', 'Flintstone')

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

values can also be an array of objects or an array of arrays matching a set of keys passed into insert():

insertInto('person', 'first_name', 'last_name').values([['Fred', 'Flintstone'], ['Wilma', 'Flintstone']]);
// INSERT INTO person (first_name, last_name) VALUES ('Fred', 'Flintstone'), ('Wilma', 'Flintstone')

insertInto('person').values([{'first_name': 'Fred', 'last_name': 'Flintstone'}, {'first_name': 'Wilma', 'last_name': 'Flintstone'}]);
// INSERT INTO person (first_name, last_name) VALUES ('Fred', 'Flintstone'), ('Wilma', '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.

update

constructorupdate(tbl[, values])

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

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

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('person').set('first_name', 'Fred').set('last_name', 'Flintstone');
// UPDATE person SET first_name = 'Fred', last_name = 'Flintstone'

update('person').set({'first_name': 'Fred', 'last_name': 'Flintstone'});
// UPDATE person 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('person').where({'first_name': 'Fred', 'last_name': 'Flintstone'});
// SELECT * FROM person 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('person').where(or(like('last_name', 'Flint%'), {'first_name': 'Fred'}));
// SELECT * FROM person WHERE last_name LIKE 'Flint%' OR first_name = 'Fred'

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

select().from('person').where(or({'first_name': 'Fred', 'last_name': 'Rubble'}));
// SELECT * FROM person 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('person').where(not($in('name', ['Fred', 'Barney', 'Wilma', 'Pebbles'])));
// SELECT * FROM person 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('person').where(gt('access', 5));
// SELECT * FROM person WHERE access > 5

betweenbetween(column, value1, value2)

Generates a BETWEEN:

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

isNull, isNotNullisNull(column), isNotNull(column)

Generates IS NULL and IS NOT NULL expressions:

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

likelike(column, value)

Generates a LIKE expression.

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

existsexists(stmt)

Takes a stmt subquery and generates an EXISTS expression:

select().from('person').where(exists(
  select().from('address').where({'address.id': sql('person.addr_id')})));
// SELECT * FROM person WHERE EXISTS
// (SELECT * FROM address WHERE address.id = person.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('person').where($in('first_name', 'Fred', 'Barney', 'Wilma'));
// SELECT * FROM person WHERE first_name IN ('Fred', 'Barney', 'Wilma')
select().from('person').where($in('addr_id', select('id').from('address')));
// SELECT * FROM person 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('person').where(eqAny('person.id', select('person_id').from('address')));
// SELECT * FROM person WHERE person.id = ANY (SELECT person_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({'psn': 'person', 'addr': 'address', 'zip': 'zipcode', 'usr': 'user'});

select().from('psn').join('addr', {'psn.addr_id': 'addr.id'});
// SELECT * FROM person psn INNER JOIN address addr ON psn.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 = {'psn': 'person', 'addr': 'address', 'zip': 'zipcode', 'usr': 'user'};
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('person').join('address');
// SELECT * FROM person INNER JOIN address ON person.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('psn').join('addr').join('zip');
// SELECT * FROM person psn
// INNER JOIN address addr ON psn.addr_id = addr.id
// INNER JOIN zipcode zip ON addr.zip_id = zip.id

select().from('psn').join('addr', 'usr');
// SELECT * FROM person psn
// INNER JOIN address addr ON psn.addr_id = addr.id
// INNER JOIN "user" usr ON psn.usr_id = usr.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('psn').join('usr', 'addr').join('zip');
// SELECT * FROM person psn
// INNER JOIN "user" usr ON psn.usr_id = usr.id
// INNER JOIN address addr ON psn.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.

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('person').where({'access_level': 3});
// SELECT COUNT(*) FROM person 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('person').where({'level_text':
  sql("CASE WHEN level=1 THEN 'one' WHEN level=2 THEN 'two' ELSE 'other' END")});
// SELECT * FROM person 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('person');
// SELECT COUNT("order") FROM person

AS Keyword

The AS keyword is treated like SQL functions. It can be included in column strings and Sql-Bricks will parse it and handle it properly:

select('person.name AS personname').from('person');
// SELECT person.name AS personname FROM person

This doesn't break automatic column quoting of column names that are reserved words or are capitalized:

select('person.order AS person_order').from('person');
// SELECT person."order" AS person_order FROM person

That said, if you do anything fancier (SQL functions, etc) then you will need to quote any column names that need quoting manually -- SQL-Bricks only provides the auto-quoting convenience for the most common cases.

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-92 will probably not be merged. If you would like support for a different dialect, you are welcome to maintain a dialect-specific fork or a library that extends sql-bricks.

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

Thanks to Suor for taking over maintenance of the postgres-dialect extension library: sql-bricks-postgres

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.