sql-bricks

by CSNW

Transparent, Schemaless SQL Generation

140 Stars 20 Forks Last release: Not found MIT License 333 Commits 43 Releases

Available items

No Items, yet!

The developer of this repository has not created any items for sale yet. Need a bug fixed? Help with integration? A different license? Create a request here:

SQL Bricks.js

Build Status

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)

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

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:
  • 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: http://csnw.github.io/sql-bricks

License: MIT

We use cookies. If you continue to browse the site, you agree to the use of cookies. For more information on our use of cookies please see our Privacy Policy.