GitHub - datavjs/sql-bricks: Transparent, Schemaless SQL Generation

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:

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 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;")
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://datavjs.github.io/sql-bricks

License: MIT