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 (?, ?, ?))
    • not in list ({x: {$nin: [1, 2, 3]}} -> x NOT IN (?, ?, ?))
  • 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

background

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.

introduction

install

npm install criterion

require

var criterion = require('criterion');

criterion exports a single function criterion() which can be called either with a condition-object or with raw-sql:

condition-objects

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

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:

the sql-fragment interface

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.

things that are sql-fragments (already)

• 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

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:

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 !

making your own fragments

if we wanted to support see also

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

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.

for users of mesa and mohair

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.

reference by example

how to read this reference

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 !

comparisons

equal

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);

not equal

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);

lower than

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)
);

greater than

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)
);

null

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

not null

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);

boolean operations

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

and

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')
);

or

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)
);

not

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.

lists of scalar expressions

see also the postgres documentation on row and array comparisons

in list

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]);

not in list

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]);

subqueries

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:

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);

not in 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);

subquery returns any rows

# 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);

compare to any/all in 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

row-wise comparison with subqueries

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]

advanced topics

combining criteria with .and()

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.

combining criteria with .or()

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.

negating criteria with .not()

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

escaping column names

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]

param array explosion

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]

changelog

0.4.0

• 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

TODO

• 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