Share your code. npm Orgs help your team discover, share, and reuse code. Create a free org »

    sql-brickspublic

    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

    install

    npm i sql-bricks

    Downloadsweekly downloads

    4,797

    version

    2.0.3

    license

    MIT

    repository

    githubgithub

    last publish

    collaborators

    • avatar