SQL Bricks.js
SQL Bricks.js is a transparent, schemaless library for building and composing SQL statements.
- The core library supports all SQL-92 clauses for
SELECT,INSERT,UPDATEandDELETE(with the exception of asc/desc/collate options fororderBy(), see #73)- Postgres extensions are at https://github.com/Suor/sql-bricks-postgres
- MySQL extensions are at https://github.com/tamarzil/mysql-bricks
- SQLite extensions are at https://github.com/CSNW/sql-bricks-sqlite
- Over 200 tests
- Easy-to-use, comprehensive docs
- Single source file (~1,100 lines)
- No production dependencies and only 1 dev dependency (Mocha.js)
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 |
Related Libraries
-
mysql-bricks adds mysql-dialect extensions:
INSERT ... ON DUPLICATE KEY UPDATE ...INSERT IGNORE ...LIMIT (SELECT / UPDATE / DELETE)OFFSETORDER BY (UPDATE / DELETE)
-
sql-bricks-sqlite adds sqlite-dialect extensions:
-
LIMITandOFFSET -
OR REPLACE,OR ABORT,OR ROLLBACK,OR FAIL
-
-
sql-bricks-postgres adds postgres-dialect extensions:
-
LIMITandOFFSET RETURNINGUPDATE ... FROMDELETE ... USINGFROM VALUES
-
-
pg-bricks adds:
- connections
- transactions
- query execution
- data accessors
-
A Layer Above Database Connectors adds:
- A common way to access to relational databases (SQLite & Postgres as of Oct 2019)
- A pool of connections in order to allow transactions in an asynchronous context;
- A way to augment your connector with your SQL query builder (has a sql-bricks plugin)
Use
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:
- It provides robust protection against SQL injection attacks (toString() just does basic escaping)
- It provides better support for complex data types (objects, arrays, etc, are passed directly to your database driver instead of being "stringified")
- It provides more helpful error messages (see https://github.com/Suor/sql-bricks-postgres/issues/10)
Examples
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: https://csnw.github.io/sql-bricks
License: MIT