3.0.1 • Published 8 months ago

sql-bricks v3.0.1

Weekly downloads
16,366
License
MIT
Repository
github
Last release
8 months ago

SQL Bricks.js

Build Status

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

Comparison with other SQL-generation JS libraries:

librarylinesfilesschemaother notes
Knex20k~50schematransactions, migrations, promises, connection pooling
Squel1.7k1schemaless
node-sql2.6k~60schema
mongo-sql1.7k~50schemaless
sql-bricks1.1k1schemaless

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:

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

License: MIT

3.0.1

8 months ago

3.0.0

2 years ago

2.0.5

4 years ago

2.0.4

5 years ago

3.0.0-beta.2

5 years ago

3.0.0-beta.1

6 years ago

2.0.3

7 years ago

2.0.2

7 years ago

2.0.1

7 years ago

2.0.0

7 years ago

1.5.1

7 years ago

1.5.0

8 years ago

1.4.0

8 years ago

1.3.1

8 years ago

1.3.0

8 years ago

1.2.3

8 years ago

1.2.2

9 years ago

1.2.1

9 years ago

1.2.0

9 years ago

1.1.1

9 years ago

1.1.0

9 years ago

1.0.5

9 years ago

1.0.4

9 years ago

1.0.3

9 years ago

1.0.2

10 years ago

1.0.1

10 years ago

1.0.0-beta.2

10 years ago

1.0.0-beta

10 years ago

0.14.0

10 years ago

0.13.0

10 years ago

0.12.0

10 years ago

0.10.0

10 years ago

0.9.0

10 years ago

0.8.0

10 years ago

0.7.7

10 years ago

0.7.6

10 years ago

0.7.5

10 years ago

0.7.4

10 years ago

0.7.3

10 years ago

0.7.2

10 years ago

0.7.1

10 years ago

0.7.0

10 years ago

0.5.6

10 years ago

0.5.5

10 years ago

0.5.4

10 years ago

0.5.3

10 years ago

0.5.2

10 years ago

0.5.1

10 years ago

0.5.0

10 years ago

0.4.0

10 years ago

0.3.8

10 years ago

0.3.7

10 years ago

0.3.6

10 years ago

0.3.5

10 years ago

0.3.4

10 years ago

0.3.3

10 years ago

0.3.2

10 years ago

0.3.1

10 years ago

0.3.0

10 years ago