criterion allows you to work with (build, combine, reuse, ...) SQL-where-conditions ('x = 5 AND y IS NOT NULL'...) as data (goodbye string-concatenation) and compile them to SQL: it has a succinct mongodb-like query-language, a simple and elegant function
npm install criterion


#### ABOUT THIS VERSION !
**this is the readme for criterion version 0.4.0 - a work in progress.
release candidate 0.4.0-rc.1 is published to npm:
the api is stable, the implementation complete, the tests numerous and passing,
the documentation (this readme) still needs some love.
to see the readme for 0.3.3 click here !
to see what has changed in 0.4.0 click here.**
> criterion allows you to work with (build, combine, reuse, ...) SQL-where-conditions
> (x = 5 AND y IS NOT NULL...) as data
> (goodbye string-concatenation)
> and compile them to SQL:
> it has a succinct mongodb-like query-language,
> a simple and elegant functional API,
> is reasily extended
> just by implementing 2 functions
> and gets out of your way: you can always drop down to raw-sql.
- background
- introduction
- install (npm install criterion)
- require (var criterion = require('criterion');)
- condition-objects (var c = criterion({x: 7, y: {$lt: 5}});)
- raw-sql (var c = criterion('LOG(y, ?)', 5);)
- the sql-fragment interface
- for users of mesa and mohair
- reference by example
- how to read this reference
- comparisons
- equal ({x: 1} -> x = ?)
- not equal ({x: {$ne: 1}} -> x != ?)
- lower than ({x: {$lt: 1}} -> x < ?, {x: {$lte: 1}} -> x <= ?)
- greater than ($gt -> >, $gte -> >=
- null ({$null: true} -> IS NULL)
- not null ({$null: false} -> IS NOT NULL)
- boolean operations
- and ({x: 1, y: {$lt: 2}} -> (x = ?) AND (y < ?))
- or ({$or: {x: 1, y: {$lt: 2}}} -> (x = ?) OR (y < ?))
- not ({$not: {x: 1}} -> NOT (x = ?))
- lists of scalar expressions
- [in list ({x: [1, 2, 3]} -> x IN (?, ?, ?))](#in-list)
- [not in list ({x: {$nin: [1, 2, 3]}} -> x NOT IN (?, ?, ?))](#not-in-list)
- subqueries
- in subquery ({x: subquery} -> x IN subquery)
- not in subquery ({x: {$nin: subquery}} -> x NOT IN subquery)
- exists - whether subquery returns any rows
- row-wise comparison with subqueries
- advanced topics
- combining criteria with .and()
- combining criteria with .or()
- negating criteria with .not()
- escaping column names with .sql(escape)
- param array explosion
- changelog
- license: MIT
criterion is part of three libraries for nodejs that make SQL with nodejs:
- simple,
- DRY
- extendable
- well documented
> make SQL with Nodejs
> succinct,
> DRY,
> functional
> data-driven
> composable
> flexible
- free
- close to the metal (sql, database, database-driver)
- and FUN !
- succinct
- FUN !
used in production
short code
high quality
- few lines of high quality code
well tested
philosophy
#### CRITERION <- you are looking at it
parses SQL-where-conditions from a mongodb-like query-language into
objects which it can compile to SQL
#### MOHAIR
a powerful SQL-query-builder with a fluent, functional, side-effect-free API.
uses criterion to build and combine its SQL-where-clauses.
#### MESA
helps as much as possible with the construction, composition and execution of SQL-queries while not restricting full access to the database in any way.
is not an ORM !
uses mohair to build its SQL-queries.
uses criterion (through mohair) to build and combine its SQL-where-clauses.
```
npm install criterion
` js`
var criterion = require('criterion');
criterion exports a single function criterion() which
can be called either with a condition-object
or with raw-sql:
a condition-object describes an SQL-where-condition
as data using a query-language that is inspired by the
mongodb query language.
let's make a condition-object:
` js`
var condition = {
a: 7,
b: {$lt: 5},
$not: {
$or: {
c: [1, 2, 3],
d: {$null: false}
}
}
};
you see that the query-language uses special modifier-keys to model comparisons ($lt), boolean operations ($not, $or)
and much much more (not unlike the mongodb query language).
now we can make a criterion from the condition-object:
` js`
var c = criterion(condition);
we can then compile the criterion to SQL:
` js`
c.sql();
// ->
// '(a = ?)
// AND
// (b < ?)
// AND
// NOT (
// (c IN (?, ?, ?))
// OR
// (d IS NOT NULL)
// )'
we can also get the bound parameters of the criterion:
`js`
c.params();
// -> [7, 5, 1, 2, 3]
raw-sql is a string of SQL followed by some optional parameter bindings.
use raw-sql for those rare cases where condition-objects and you have to fall back to using strings.
note that condition-objects and raw-sql can be mixed to keep raw-sql to a minimum.
` js`
var c = criterion('LOG(y, ?)', 4);
a criterion made from raw-sql behaves exactly like one made from
a condition-object:
you can get the sql:
`js`
c.sql();
// -> 'LOG(y, ?)'
...and the bound parameters:
`js`
c.params();
// -> [4]
in fact both the criterion made from raw-sql and one made from
a condition-object are sql-fragments:
in
mesa,
mohair
and
criterion
every object that has a .sql() method and a .params() method
is an implements the sql-fragment interface
and is an sql-fragment.
more precisely:
the .sql() method should return a string of valid SQL..sql()
the method might be called with a single argument:escape()
a function which takes a string and returns a string.escape()
when the function is present then the .sql() method should call it
to transform table- and column-names in the returned SQL.
if .sql() constructs the SQL on-the-fly that should be straightforward.
in the case of raw-sql escaping is complex, ambigous and not worth the effort.
the .params() method takes no arguments and must return an array.
#### things that are sql-fragments (already)
- EVERY criterion:
- criterion({x: 7})criterion('LOG(y, ?)', 4)
- mesa.table('post')
- EVERY mesa-query or mohair-query:
- mesa.table('post').where({id: 7})
- mohair.table('host')
- mohair.table('host').select('name').where({created_at: {$lt: new Date()}})
- .raw()
- EVERY return value of mesa's or mohair's method:mesa.raw('LOG(y, ?)', 4)
- mohair.raw('LOG(y, ?)', 4)
-
- EVERY object you create that implements the sql-fragment interface
#### mixing condition-objects and sql-fragments
now to the FUN part !
ANY sql-fragment can be used in place of ANY value in a condition-object:
` js
var c = criterion({
x: criterion('crypt(?, gen_salt(?, ?))', 'password', 'bf', 4)
});
c.sql();
// -> 'x = (crypt(?, gen_salt(?, ?)))'
c.params();
// -> ['password', 'bf', 4]
`
or
` js
var c = criterion({x: {$ne: criterion('LOG(y, ?)', 4)}});
c.sql();
// -> 'x != LOG(y, ?)'
c.params();
// -> [4]
`
you see how this allows mixing condition-objects with arbitrary sql: use it to keep raw-sql to a minimum !
sql-fragments can be mixed with condition-objects inside boolean operations:
` js
var c = criterion({
$or: [
criterion('x BETWEEN ? AND ?', 5, 10),
{y: {$ne: 12}}
[
criterion('x != LOG(y, ?)', 4)}}),
{x: {$lt: 10}}
]
]
});
c.sql();
// ->
// '(x BETWEEN ? AND ?)
// OR
// (y != ?)
// OR
// (
// (x != LOG(y, ?))
// AND
// (x < ?)
// )'
c.params();
// -> [5, 10, 12, 4, 10]
`
last but not least:
the fact that mohair/mesa-queries are sql-fragments
allows you to model subqueries with mohair/mesa
and then use them directly in condition-objects.
this makes the creation of SQL-where-conditions that contain subqueries quite elegant:
see the examples !
#### making your own fragments
if we wanted to support see also
` js
var pgJsonGet = function(left, right) {
var leftF = criterion.coerceToSqlFragment(left);
var rightF = criterion.coerceToSqlFragment(right);
return {
sql: function(escape) {
return left.sql(escape) +
},
params: function() {
}
};
};
`
and use them like this
` js`
var c = criterion({
$or: [
criterion('x BETWEEN ? AND ?', 5, 10),
{y: {$ne: 12}}
[
criterion('x != LOG(y, ?)', 4)}}),
{x: {$lt: 10}}
]
]
});
there is a library that does that for you.
EVERYTHING possible with criterion is possible
for the where conditions in
mesa
and mohair !
the criterion reference completes mesa's and mohair's documentation !
here's why:
the criterion module exports a single function: var criterion = require('criterion')
mesa's and mohair's fluent .where() methodscriterion()
call under the hood and forward all their arguments unmodifed to criterion().criterion()
this means that all arguments supported by are supported by .where() !
` js
// same condition-object
var condition = {x: 7};
// criterion
var criterion = require('criterion');
var c = criterion(condition);
c.sql();
// -> 'x = ?'
c.params();
// -> [7]
// mohair
var mohair = require('mohair');
var query = mohair
.table('post')
.where(condition);
query.sql();
// -> 'SELECT * FROM post WHERE x = ?'
query.params();
// -> [7]
`
if .where() is called more than once the resulting criteria are ANDed together:
` js
var mohair = require('mohair');
var postTable = mohair.table('post')
var queryAlpha = postTable.where({x: 7});
var queryBravo = queryAlpha.where('y IN (?)', [1, 2]);
postTable.sql();
// -> 'SELECT * FROM post'
postTable.params();
// -> []
queryAlpha.sql();
// -> 'SELECT * FROM post WHERE x = ?'
queryAlpha.params();
// -> [7]
queryBravo.sql();
// -> 'SELECT * FROM post WHERE x = ? AND y IN (?, ?)'
queryBravo.params();
// -> [7, 1, 2]
`
calling methods on does not but
refines
this is one of the nice properties of mohair and mesa.
*for each section several examples are given and seperated by "or".
the criteria created in the examples behave identical.
the first example in each section
uses condition-objects and is always the preferred way of doing things !*
#### equal
where x = 7:
` js`
var c = criterion({x: 7});
c.sql();
// -> 'x = ?'
c.params();
// -> [7]
or raw:
` js`
var c = criterion('x = ?', 7);
or functional:
` js`
var c = criterion.eq(criterion.escape(x), 7);
#### not equal
where x != 3:
` js`
var c = criterion({x: {$ne: 3}});
c.sql(); // -> 'x != ?'
c.params(); // -> [3]
or raw:
` js`
var c = criterion('x != ?', 3);
or functional:
` js`
var c = criterion.ne(criterion.escape(x), 3);
#### lower than
where x < 3 and y <= 4:
` js`
var c = criterion({x: {$lt: 3}, y: {$lte: 4}});
c.sql();
// -> 'x < ? AND y <= ?'
c.params();
// -> [3, 4]
or raw:
` js`
var c = criterion('x < ? AND y <= ?', 3, 4);
or functional:
` js`
var c = criterion.and(
criterion.lt(criterion.escape('x'), 3),
criterion.lte(criterion.escape('y'), 4)
);
#### greater than
where x > 3 and y >= 4:
` js`
var c = criterion({x: {$gt: 3}, y: {$gte: 4}});
c.sql();
// -> 'x > ? AND y >= ?'
c.params();
// -> [3, 4]
or raw:
` js`
var c = criterion('x > ? AND y >= ?', 3, 4);
or functional:
` js`
var c = criterion.and(
criterion.gt(criterion.escape('x'), 3),
criterion.gte(criterion.escape('y'), 4)
);
#### null
where x is null
` js`
var c = criterion({x: {$null: true});
c.sql();
// -> 'x IS NULL'
c.params();
// -> []
or raw:
` js`
var c = criterion('x IS NULL');
or functional:
` js`
var c = criterion.null(criterion.escape('x'), true);
// true is default
#### not null
where x is not null:
` js`
var c = criterion({x: {$null: false}});
c.sql();
// -> 'x IS NOT NULL'
c.params();
// -> []
or raw:
` js`
var c = criterion('x IS NOT NULL');
or functional:
` js`
var c = criterion.null(criterion.escape('x'), false);
$or, $and and $not can be nested arbitrarily.
#### and
where x = 7 and y = 'a':
` js`
var c = criterion({x: 7, y: 'a'});
c.sql();
// -> 'x = ? AND y = ?'
c.params();
// -> [7, 'a']
or using an array:
` js`
var c = criterion([{x: 7}, {y: 'a'}]);
or more verbose:
` js`
var c = criterion({$and: {x: 7, y: 'a'}});
or more verbose using an array:
` js`
var c = criterion({$and: [{x: 7}, {y: 'a'}]});
or raw:
` js`
var c = criterion('x = ? AND y = ?', 7, 'a');
or functional:
` js`
var c = criterion.and(
criterion.eq(criterion.escape('x'), 7),
criterion.eq(criterion.escape('y'), 'a')
);
#### or
where x = 7 or y = 6:
` js`
var c = criterion({$or: {x: 7, y: 6}});
c.sql();
// -> 'x = ? OR y = ?'
c.params();
// -> [7, 6]
or using an array:
` js`
var c = criterion({$or: [{x: 7}, {y: 6}]});
or raw:
` js`
var c = criterion('x = ? OR y = ?', 7, 6);
or functional:
` js`
var c = criterion.or(
criterion.eq(criterion.escape('x'), 7),
criterion.eq(criterion.escape('y'), 6)
);
#### not
where not (x > 3 and y >= 4):
` js`
var c = criterion({$not: {x: {$gt: 3}, y: {$gte: 4}}});
c.sql();
// -> 'NOT (x > ? AND y >= ?)'
c.params();
// -> [3, 4]
or raw:
` js`
var c = criterion('NOT (x > ? AND y >= ?)', 3, 4);
or functional:
` js`
var c = criterion.not(
criterion.and(
criterion.eq(criterion.escape('x'), 3),
criterion.eq(criterion.escape('y'), 4)
)
);
$or, $and and $not can be nested arbitrarily.
see also the postgres documentation on row and array comparisons
#### in list
where x is in [1, 2, 3]
` js`
var c = criterion({x: [1, 2, 3]});
c.sql();
// -> 'x IN (?, ?, ?)'
c.params();
// -> [1,2,3]
or more verbose:
` js`
var c = criterion({x: {$in: [1, 2, 3]}});
or raw:
` js`
var c = criterion('x IN (?)', [1, 2, 3]);
or functional:
` js`
var c = criterion.in(criterion.escape('x'), [1, 2, 3]);
#### not in list
where x is not in [1, 2, 3]
` js`
var c = criterion({x: {$nin: [1, 2, 3]}});
c.sql();
// -> 'x NOT IN (?, ?, ?)'
c.params();
// -> [1,2,3]
or raw:
` js`
var c = criterion('x NOT IN (?)', [1, 2, 3]);
or functional:
` js`
var c = criterion.nin(criterion.escape('x'), [1, 2, 3]);
var subquery in the examples below can be any sql-fragment.
the fact that mohair/mesa-queries are sql-fragments
allows you to model subqueries with mohair/mesa
and then use them directly in condition-objects.
this makes the creation of SQL-where-conditions that contain subqueries quite elegant.
see also the postgres documentation on row and array comparisons
#### in subquery
where x is in subquery:
` js
var subquery = mohair
.table('post')
.where({is_published: true})
.select('id');
var c = criterion({x: {$in: subquery}});
c.sql();
// -> 'x IN (SELECT id FROM post WHERE is_published = ?)'
c.params();
// -> [true]
`
or functional:
` js`
var c = criterion.in(criterion.escape('x'), subquery);
#### not in subquery
where x is not in subquery:
` js
var subquery = mohair
.table('post')
.where({is_published: true})
.select('id');
var c = criterion({x: {$nin: subquery}});
c.sql();
// -> 'x NOT IN (SELECT id FROM post WHERE is_published = ?)'
c.params();
// -> [true]
`
or functional:
` js`
var c = criterion.nin(criterion.escape('x'), subquery);
#### subquery returns any rows
` jsTODO this isnt right
var subquery = mohair
.table('post')
.where({is_published: false})
.where({user_id: mohair.raw('id')})
var c = criterion({$exists: subquery})
c.sql();
// -> 'EXISTS (SELECT * FROM post WHERE is_published = ?)'
c.params();
// -> [true]
`
or functional:
` js`
var c = criterion.exists(subquery);
#### compare to any/all in subquery
` js
var subquery = mohair
.table('post')
.select('id')
.where({is_published: false})
var c = criterion({x: {$any: subquery}})
c.sql();
// -> 'x = ANY (SELECT * FROM post WHERE is_published = ?)'
c.params();
// -> [true]
`
or functional:
` js`
var c = criterion.any(criterion.escape('x'), subquery);
criterion supports
#### row-wise comparison with subqueries
find published posts that were created strictly-before the user with id = 1 was created:
` js
var mohair = require('mohair');
var creationDateOfUserWithId1 = mohair
.table('user')
.where({id: 1})
.select('created_at');
var postsCreatedBeforeUser = mohair
.table('post')
.where({is_published: true})
.where({created_at: {$lt: creationDateOfUserWithId1}});
postsCreatedBeforeUser.sql();
// ->
// 'SELECT *
// FROM post
// WHERE is_published = ?
// AND created_at < (SELECT created_at FROM user WHERE id = ?)'
postsCreatedBeforeUser.params();
// -> [true, 1]
`
` js
var alpha = criterion({x: 7, y: 'a'});
var bravo = criterion('z = ?', true);
alpha.and(bravo).sql();
// -> '(x = ?) AND (y = ?) AND (z = ?)'
alpha.and(bravo).params();
// -> [7, 'a', true]
`
and(), or() and not() return new objects.
no method ever changes the object it is called on.
` js
var alpha = criterion({x: 7, y: 'a'});
var bravo = criterion('z = ?', true);
bravo.or(alpha).sql();
// -> '(z = ?) OR (x = ? AND y = ?)'
bravo.or(alpha).params();
// -> [true, 7, 'a']
`
and(), or() and not() return new objects.
no method ever changes the object it is called on.
` js`
var c = criterion({x: 7, y: 'a'});
c.not().sql();
// -> 'NOT ((x = ?) AND (y = ?))'
c.not().params();
// -> [7, 'a']
double negations are removed:
` js`
var c = criterion({x: 7, y: 'a'});
c.not().not().sql();
// -> '(x = ?) AND (y = ?)'
c.not().not().params();
// -> [7, 'a']
you can pass a function into any sql() method to escape column names:
` js
var c = criterion({x: 7, y: 8});
var escape = function(x) {
return '"' + x + '"';
};
c.sql(escape);
// -> '"x" = ? AND "y" = ?' <- x and y are escaped !
c.params();
// -> [7, 8]
`
if a parameter binding for raw sql is an array then
the corresponding binding ? is exploded into a list of ?:
` js
var c = criterion('x = ? AND y IN (?)', 7, [8, 9, 10]);
c.sql();
// -> 'x = ? AND y IN (?, ?, ?)'
c.params();
// -> [7, 8, 9, 10]
`
` js
var c = criterion('x = ? AND (y && ARRAY[?])', 7, [8, 9, 10]);
c.sql();
// -> 'x = ? AND (y && ARRAY[?, ?, ?])'
c.params();
// -> [7, 8, 9, 10]
`
- to escape column names in the resulting SQL an escape function can now be passed as an argument into any sql() method$exists
- sql fragments are now always wrapped in parentheses before pasting them into a query.
- doesn't break anything and makes subqueries work without further changes.
- added which can be used with mesa/mohair queries (or any object that responds to an sql() method): criterion({$exists: mohair.table('post').where({id: 7})})$in
- and $nin now support not just lists of values but also subqueries:criterion({id: {$in: mohair.table('post').where({is_active: true}).select('id')}})
- $any
- added modifiers , $neAny, $ltAny, $gtAny, $gteAny, $all, $neAll, $ltAll, $lteAll, $gtAll, $gteAll to be used with subqueries:criterion({created_at: {$gteAll: mohair.table('post').where({is_active: true}).select('updated_at')}})
- {$lt: criterion('5 + 8')}
- sql-fragments can now be used in more places...
- where the value would normally go in a comparison: $or
- this makes row-wise comparisons with subqueries possible
- in the arrays passed to and $and: {$or [{a: 7}, criterion('b < ?', 5)]}`
- ...
- bugfixes
- made some (exotic) condition-objects work which didn't work before
- improved implementation and based everything on a DSL which is also exposed
- major improvements to
- code quality
- tests
- terminology
- documentation
- test dsl
- document dsl
- read through the code again
- often the left side of an operation is just a column or a table qualified column
- atoms are treated as values
- dontWrap
- says how outer fragments should handle this fragment
- things are only wrapped when inside of something
- finish the readme
- test left operands
- reverse operands