sql-bricks
Transparent, Schemaless SQL Generation
Last updated 2 months ago by prust .
MIT · Repository · Bugs · Original npm · Tarball · package.json
$ cnpm install sql-bricks 
SYNC missed versions from official npm registry.

SQL Bricks.js

Build Status

SQL Bricks.js is a transparent, schemaless library for building and composing SQL statements.

Comparison with popular SQL-generation libraries:

library lines files schema language other notes
Knex 3500 30 schema javascript transactions, migrations, promises, connection pooling
Squel 1000 3 schemaless coffeescript
node-sql 2600 59 schema javascript
mongo-sql 1700 49 schemaless javascript
gesundheit 1600 21 schemaless coffeescript uses Any-DB to wrap the DB driver
sql-bricks 1100 1 schemaless javascript

Related Libraries

  • 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
  • sql-bricks-sqlite adds sqlite-dialect extensions:
    • LIMIT and OFFSET
    • OR REPLACE, OR ABORT, OR ROLLBACK, OR FAIL

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:

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"]}

Documentation: http://csnw.github.io/sql-bricks

License: MIT

Current Tags

  • 2.0.5                                ...           latest (2 months ago)
  • 3.0.0-beta.1                                ...           next (2 years ago)

57 Versions

  • 2.0.5                                ...           2 months ago
  • 2.0.4                                ...           2 years ago
  • 3.0.0-beta.2                                ...           2 years ago
  • 3.0.0-beta.1                                ...           2 years ago
  • 2.0.3                                ...           3 years ago
  • 2.0.2                                ...           3 years ago
  • 2.0.1                                ...           4 years ago
  • 2.0.0                                ...           4 years ago
  • 1.5.1                                ...           4 years ago
  • 1.5.0                                ...           4 years ago
  • 1.4.0                                ...           4 years ago
  • 1.3.1                                ...           4 years ago
  • 1.3.0                                ...           4 years ago
  • 1.2.3                                ...           5 years ago
  • 1.2.2                                ...           5 years ago
  • 1.2.1                                ...           5 years ago
  • 1.2.0                                ...           5 years ago
  • 1.1.1                                ...           5 years ago
  • 1.1.0                                ...           6 years ago
  • 1.0.5                                ...           6 years ago
  • 1.0.4                                ...           6 years ago
  • 1.0.3                                ...           6 years ago
  • 1.0.2                                ...           6 years ago
  • 1.0.1                                ...           6 years ago
  • 1.0.0-beta.2                                ...           6 years ago
  • 1.0.0-beta                                ...           6 years ago
  • 0.14.0                                ...           6 years ago
  • 0.13.0                                ...           6 years ago
  • 0.12.0                                ...           6 years ago
  • 0.10.0                                ...           6 years ago
  • 0.9.0                                ...           6 years ago
  • 0.8.0                                ...           6 years ago
  • 0.7.7                                ...           6 years ago
  • 0.7.6                                ...           6 years ago
  • 0.7.5                                ...           6 years ago
  • 0.7.4                                ...           6 years ago
  • 0.7.3                                ...           6 years ago
  • 0.7.2                                ...           6 years ago
  • 0.7.1                                ...           6 years ago
  • 0.7.0                                ...           6 years ago
  • 0.5.6                                ...           6 years ago
  • 0.5.5                                ...           6 years ago
  • 0.5.4                                ...           6 years ago
  • 0.5.3                                ...           7 years ago
  • 0.5.2                                ...           7 years ago
  • 0.5.1                                ...           7 years ago
  • 0.5.0                                ...           7 years ago
  • 0.4.0                                ...           7 years ago
  • 0.3.8                                ...           7 years ago
  • 0.3.7                                ...           7 years ago
  • 0.3.6                                ...           7 years ago
  • 0.3.5                                ...           7 years ago
  • 0.3.4                                ...           7 years ago
  • 0.3.3                                ...           7 years ago
  • 0.3.2                                ...           7 years ago
  • 0.3.1                                ...           7 years ago
  • 0.3.0                                ...           7 years ago

Copyright 2014 - 2016 © taobao.org |