Socket
Socket
Sign inDemoInstall

sql-bricks

Package Overview
Dependencies
0
Maintainers
1
Versions
59
Alerts
File Explorer

Advanced tools

Install Socket

Detect and block malicious and high-risk dependencies

Install

    sql-bricks

Transparent, Schemaless SQL Generation


Version published
Weekly downloads
23K
decreased by-14.91%
Maintainers
1
Install size
298 kB
Created
Weekly downloads
 

Readme

Source

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)
  • 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:

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

Keywords

FAQs

Last updated on 30 Jul 2023

Did you know?

Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.

Install

Related posts

SocketSocket SOC 2 Logo

Product

  • Package Alerts
  • Integrations
  • Docs
  • Pricing
  • FAQ
  • Roadmap

Stay in touch

Get open source security insights delivered straight into your inbox.


  • Terms
  • Privacy
  • Security

Made with ⚡️ by Socket Inc