Package Exports
- sql-bricks
- sql-bricks/sql-bricks.js
This package does not declare an exports field, so the exports above have been automatically detected and optimized by JSPM instead. If any package subpath is missing, it is recommended to post an issue to the original package (sql-bricks) to support the "exports" field. If that is not possible, create a JSPM override to customize the exports field for this package.
Readme
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:
LIMITandOFFSETOR REPLACE,OR ABORT,OR ROLLBACK,OR FAIL
- sql-bricks-postgres adds postgres-dialect extensions:
LIMITandOFFSETRETURNINGUPDATE ... 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