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, UPDATE and DELETE (with the exception of asc/desc/collate options for orderBy(), 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)
  • OFFSET
  • ORDER BY (UPDATE / DELETE)
• sql-bricks-sqlite adds sqlite-dialect extensions:
  • LIMIT and OFFSET
  • OR REPLACE, OR ABORT, OR ROLLBACK, OR FAIL
• sql-bricks-postgres adds postgres-dialect extensions:
  • LIMIT and OFFSET
  • RETURNING
  • UPDATE ... FROM
  • DELETE ... USING
  • FROM 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:

varselect=SqlBricks.select;

In node:

varselect=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 Suor/sql-bricks-postgres#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;")varsql=require('sql-bricks');varselect=sql.select,insert=sql.insert,update=sql.update;varor=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 / HAVINGselect('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// INSERTinsert('person',{'first_name': 'Fred','last_name': 'Flintstone'});// INSERT INTO person (first_name, last_name) VALUES ('Fred', 'Flintstone')// UPDATEupdate('person',{'first_name': 'Fred','last_name': 'Flintstone'});// UPDATE person SET first_name = 'Fred', last_name = 'Flintstone'// Parameterized SQLupdate('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 paramsupdate('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 paramsupdate('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://datavjs.github.io/sql-bricks

License: MIT