Transparent, Schemaless SQL Generation
SQL Bricks.js is a transparent, schemaless library for building and composing SQL statements.
SELECT,
INSERT,
UPDATEand
DELETE(with the exception of asc/desc/collate options for
orderBy(), see #73)
Comparison with other SQL-generation JS libraries:
library |
lines | files | schema | other notes |
---|---|---|---|---|
Knex | 20k | ~50 | schema | transactions, migrations, promises, connection pooling |
Squel | 1.7k | 1 | schemaless | |
node-sql | 2.6k | ~60 | schema | |
mongo-sql | 1.7k | ~50 | schemaless | |
sql-bricks | 1.1k | 1 | schemaless |
INSERT ... ON DUPLICATE KEY UPDATE ...
INSERT IGNORE ...
LIMIT (SELECT / UPDATE / DELETE)
OFFSET
ORDER BY (UPDATE / DELETE)
LIMITand
OFFSET
OR REPLACE,
OR ABORT,
OR ROLLBACK,
OR FAIL
LIMITand
OFFSET
RETURNING
UPDATE ... FROM
DELETE ... USING
FROM VALUES
SQLBricks' only dependency is Underscore.js.
In the browser:
var select = SqlBricks.select;
In node:
var select = require('sql-bricks').select;
A simple select via
.toString()and
.toParams():
select().from('person').where({last_name: 'Rubble'}).toString(); // "SELECT * FROM person WHERE last_name = 'Rubble'"select().from('person').where({last_name: 'Rubble'}).toParams(); // {"text": "SELECT * FROM person WHERE last_name = $1", "values": ["Rubble"]}
While
toString()is slightly easier,
toParams()is recommended because:
The SQLBricks API is comprehensive, supporting all of SQL-92 for select/insert/update/delete. It is also quite flexible; in most places arguments can be passed in a variety of ways (arrays, objects, separate arguments, etc). That said, here are some of the most common operations:
// convenience variables (for node; for the browser: "var sql = SqlBricks;") var sql = require('sql-bricks'); var select = sql.select, insert = sql.insert, update = sql.update; var or = sql.or, like = sql.like, lt = sql.lt;// WHERE: (.toString() is optional; JS will call it automatically in most cases) select().from('person').where({last_name: 'Rubble'}).toString(); // SELECT * FROM person WHERE last_name = 'Rubble'
// JOINs: select().from('person').join('address').on({'person.addr_id': 'address.id'}); // SELECT * FROM person INNER JOIN address ON person.addr_id = address.id
// Nested WHERE criteria: select('*').from('person').where(or(like('last_name', 'Flint%'), {'first_name': 'Fred'})); // SELECT * FROM person WHERE last_name LIKE 'Flint%' OR first_name = 'Fred'
// GROUP BY / HAVING 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
// INSERT insert('person', {'first_name': 'Fred', 'last_name': 'Flintstone'}); // INSERT INTO person (first_name, last_name) VALUES ('Fred', 'Flintstone')
// UPDATE update('person', {'first_name': 'Fred', 'last_name': 'Flintstone'}); // UPDATE person SET first_name = 'Fred', last_name = 'Flintstone'
// Parameterized 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"]}
// SQLite-style params 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"]}
// MySQL-style params update('person', {'first_name': 'Fred'}).where({'last_name': 'Flintstone'}).toParams({placeholder: '?'}); // {"text": "UPDATE person SET first_name = ? WHERE last_name = ?", "values": ["Fred", "Flintstone"]}
Full documentation: http://csnw.github.io/sql-bricks
License: MIT