criterion

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

criterion

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.

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

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:

var c = criterion(condition);

we can then compile the criterion to SQL:

c.sql();
// -> 
// '(a = ?) 
//  AND 
//  (b < ?) 
//  AND 
//  NOT ( 
//    (c IN (?, ?, ?)) 
//    OR 
//    (d IS NOT NULL) 
//  )' 

we can also get the bound parameters of the criterion:

c.params();
// -> [7, 5, 1, 2, 3] 

see the reference below for examples on how to model almost every SQL-where-condition using condition-objects !

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.

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:

c.sql();
// -> 'LOG(y, ?)' 

...and the bound parameters:

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. the .sql() method might be called with a single argument: a function escape() which takes a string and returns a string. when the escape() 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.

  • EVERY criterion:
    • criterion({x: 7})
    • criterion('LOG(y, ?)', 4)
  • EVERY mesa-query or mohair-query:
    • mesa.table('post')
    • mesa.table('post').where({id: 7})
    • mohair.table('host')
    • mohair.table('host').select('name').where({created_at: {$lt: new Date()}})
  • EVERY return value of mesa's or mohair's .raw() method:
    • mesa.raw('LOG(y, ?)', 4)
    • mohair.raw('LOG(y, ?)', 4)
  • EVERY object you create that implements the sql-fragment interface

now to the FUN part !

ANY sql-fragment can be used in place of ANY value in a condition-object:

var c = criterion({
  x: criterion('crypt(?, gen_salt(?, ?))', 'password', 'bf', 4)
});
 
c.sql();
// -> 'x = (crypt(?, gen_salt(?, ?)))' 
c.params();
// -> ['password', 'bf', 4] 

or

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:

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 !

if we wanted to support see also

 
var pgJsonGet = function(leftright) {
  var leftF = criterion.coerceToSqlFragment(left);
  var rightF = criterion.coerceToSqlFragment(right);
  return {
    sqlfunction(escape) {
      return left.sql(escape) + 
    },
    paramsfunction() {
 
    }
  };
};

and use them like this

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() methods call criterion() under the hood and forward all their arguments unmodifed to criterion(). this means that all arguments supported by criterion() are supported by .where() !

// 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:

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 !

where x = 7:

var c = criterion({x: 7});
c.sql();
// -> 'x = ?' 
c.params();
// -> [7] 

or raw:

var c = criterion('x = ?', 7);

or functional:

var c = criterion.eq(criterion.escape(x), 7);

where x != 3:

var c = criterion({x: {$ne: 3}});
c.sql();    // -> 'x != ?' 
c.params(); // -> [3] 

or raw:

var c = criterion('x != ?', 3);

or functional:

var c = criterion.ne(criterion.escape(x), 3);

where x < 3 and y <= 4:

var c = criterion({x: {$lt: 3}, y: {$lte: 4}});
c.sql();
// -> 'x < ? AND y <= ?' 
c.params();
// -> [3, 4] 

or raw:

var c = criterion('x < ? AND y <= ?', 3, 4);

or functional:

var c = criterion.and(
  criterion.lt(criterion.escape('x'), 3),
  criterion.lte(criterion.escape('y'), 4)
);

where x > 3 and y >= 4:

var c = criterion({x: {$gt: 3}, y: {$gte: 4}});
c.sql();
// -> 'x > ? AND y >= ?' 
c.params();
// -> [3, 4] 

or raw:

var c = criterion('x > ? AND y >= ?', 3, 4);

or functional:

var c = criterion.and(
  criterion.gt(criterion.escape('x'), 3),
  criterion.gte(criterion.escape('y'), 4)
);

where x is null

var c = criterion({x: {$null: true});
c.sql();
// -> 'x IS NULL' 
c.params();
// -> [] 

or raw:

var c = criterion('x IS NULL');

or functional:

var c = criterion.null(criterion.escape('x'), true);
// true is default 

where x is not null:

var c = criterion({x: {$null: false}});
c.sql();
// -> 'x IS NOT NULL' 
c.params();
// -> [] 

or raw:

var c = criterion('x IS NOT NULL');

or functional:

var c = criterion.null(criterion.escape('x'), false);

$or, $and and $not can be nested arbitrarily.

where x = 7 and y = 'a':

var c = criterion({x: 7, y: 'a'});
c.sql();
// -> 'x = ? AND y = ?' 
c.params();
// -> [7, 'a'] 

or using an array:

var c = criterion([{x: 7}, {y: 'a'}]);

or more verbose:

var c = criterion({$and: {x: 7, y: 'a'}});

or more verbose using an array:

var c = criterion({$and: [{x: 7}, {y: 'a'}]});

or raw:

var c = criterion('x = ? AND y = ?', 7, 'a');

or functional:

var c = criterion.and(
  criterion.eq(criterion.escape('x'), 7),
  criterion.eq(criterion.escape('y'), 'a')
);

where x = 7 or y = 6:

var c = criterion({$or: {x: 7, y: 6}});
c.sql();
// -> 'x = ? OR y = ?' 
c.params();
// -> [7, 6] 

or using an array:

var c = criterion({$or: [{x: 7}, {y: 6}]});

or raw:

var c = criterion('x = ? OR y = ?', 7, 6);

or functional:

var c = criterion.or(
  criterion.eq(criterion.escape('x'), 7),
  criterion.eq(criterion.escape('y'), 6)
);

where not (x > 3 and y >= 4):

var c = criterion({$not: {x: {$gt: 3}, y: {$gte: 4}}});
c.sql();
// -> 'NOT (x > ? AND y >= ?)' 
c.params();
// -> [3, 4] 

or raw:

var c = criterion('NOT (x > ? AND y >= ?)', 3, 4);

or functional:

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

where x is in [1, 2, 3]

var c = criterion({x: [1, 2, 3]});
c.sql();
// -> 'x IN (?, ?, ?)' 
c.params();
// -> [1,2,3] 

or more verbose:

var c = criterion({x: {$in: [1, 2, 3]}});

or raw:

var c = criterion('x IN (?)', [1, 2, 3]);

or functional:

var c = criterion.in(criterion.escape('x'), [1, 2, 3]);

where x is not in [1, 2, 3]

var c = criterion({x: {$nin: [1, 2, 3]}});
c.sql();
// -> 'x NOT IN (?, ?, ?)' 
c.params();
// -> [1,2,3] 

or raw:

var c = criterion('x NOT IN (?)', [1, 2, 3]);

or functional:

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

where x is in subquery:

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:

var c = criterion.in(criterion.escape('x'), subquery);

where x is not in subquery:

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:

var c = criterion.nin(criterion.escape('x'), subquery);
# TODO 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:

var c = criterion.exists(subquery);
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:

var c = criterion.any(criterion.escape('x'), subquery);

criterion supports

find published posts that were created strictly-before the user with id = 1 was created:

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] 
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.

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.

var c = criterion({x: 7, y: 'a'});
c.not().sql();
// -> 'NOT ((x = ?) AND (y = ?))' 
c.not().params();
// -> [7, 'a'] 

double negations are removed:

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:

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 ?:

var c = criterion('x = ? AND y IN (?)', 7, [8, 9, 10]);
 
c.sql();
// -> 'x = ? AND y IN (?, ?, ?)' 
c.params();
// -> [7, 8, 9, 10] 
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
  • 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 $exists 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')}})
  • added modifiers $any, $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')}})
  • sql-fragments can now be used in more places...
    • where the value would normally go in a comparison: {$lt: criterion('5 + 8')}
      • this makes row-wise comparisons with subqueries possible
    • in the arrays passed to $or 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

license: MIT

  • 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