Brick
Build SQL strings without attempting to abstract away SQL.
Philosophy:
Writing raw SQL is fun, but there are occasional pain points, such as conditionally appending expressions or converting an object into a WHERE clause. Brick makes those things easier, but otherwise gets out of your way.
A brick in its simplest form is an array of SQL strings and an array of parameters. Bricks can be composed from strings, objects, or other bricks, but ultimately they are reduced down to an SQL string and an array of parameters.
If any parameters are present, the SQL expression must specify their position
with a placeholder ?
. Parameters can be scalar values or bricks. If a brick
is passed as a parameter, it will replace the placeholder when the query is
built.
Usage:
Simple brick
var brick = require('brick')
var id = 1
var query = brick('SELECT * FROM events WHERE id = ?', id)
query.build() // => { text: 'SELECT * FROM events WHERE id = $1', params: [1] }
Compose bricks with other bricks
var brick = require('brick')
var conditions = []
conditions.push(brick('category = ?', 'Blues'))
var query = brick('SELECT * FROM events WHERE ?', brick('category = ?', 'Blues'))
query.build() // => { text: 'SELECT * FROM events WHERE category = $1', params: ['Blues'] }
Write subqueries
var brick = require('brick')
var cities = brick('SELECT id FROM cities WHERE name = ?', 'Ann Arbor')
var query = brick('SELECT * FROM events WHERE city_id in (?)', cities)
query.build() // => { text: 'SELECT * FROM events WHERE city_id in (SELECT id FROM cities WHERE name = $1)', params: ['Ann Arbor'] }
Join bricks
var brick = require('brick')
var columns = []
columns.push(brick('id as event_id'))
columns.push('headline')
columns.push('city_id')
var query = brick('SELECT ? FROM events', brick.join(columns))
query.build() // => { text: 'SELECT id as event_id, headline, city_id FROM events', params: [] }
Conditions
var brick = require('brick')
var where = brick.conditions({
city: 'Ann Arbor',
category: 'Jazz',
deleted_at: null
})
var query = brick('SELECT * FROM events WHERE ?', where)
query.build() // => { text: 'SELECT * FROM events WHERE city = ? AND category = ? AND deleted_at IS NULL', params: ['Ann Arbor', 'Jazz'] }
Complex conditions
var brick = require('brick')
var searches = [
{ generic_type: 128, specific_type: 256 },
{ generic_type: 128, specific_type: null },
]
var clauses = searches.map(function(search) {
return brick.fn.wrap(brick.conditions(search))
})
var where = brick.join(clauses, 'OR')
var query = brick('SELECT * FROM devices WHERE ?', where)
query.build() // => { text: 'SELECT * FROM devices WHERE (generic_type = ? AND specific_type = ?) OR (generic_type = ? AND specific_type IS NULL)', params: ['128', '256'] }
Using Brick with Postgresql
var pg = require('pg')
var brick = require('brick')
var query = brick(/* ... */)
pg.connect('database', function(client, done) {
client.query(query.build('pg'), function(result) {
// handle result...
done()
})
})