Security News
Fluent Assertions Faces Backlash After Abandoning Open Source Licensing
Fluent Assertions is facing backlash after dropping the Apache license for a commercial model, leaving users blindsided and questioning contributor rights.
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
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:
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
well tested
philosophy
parses SQL-where-conditions from a mongodb-like query-language into objects which it can compile to SQL
a powerful SQL-query-builder with a fluent, functional, side-effect-free API.
uses criterion to build and combine its SQL-where-clauses.
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]
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.
criterion({x: 7})
criterion('LOG(y, ?)', 4)
mesa.table('post')
mesa.table('post').where({id: 7})
mohair.table('host')
mohair.table('host').select('name').where({created_at: {$lt: new Date()}})
.raw()
method:
mesa.raw('LOG(y, ?)', 4)
mohair.raw('LOG(y, ?)', 4)
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(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.
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]
.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.
.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.
.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']
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]
sql()
method$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')}})
$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')}})
{$lt: criterion('5 + 8')}
$or
and $and
: {$or [{a: 7}, criterion('b < ?', 5)]}
FAQs
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
The npm package criterion receives a total of 23 weekly downloads. As such, criterion popularity was classified as not popular.
We found that criterion demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 1 open source maintainer collaborating on the project.
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.
Security News
Fluent Assertions is facing backlash after dropping the Apache license for a commercial model, leaving users blindsided and questioning contributor rights.
Research
Security News
Socket researchers uncover the risks of a malicious Python package targeting Discord developers.
Security News
The UK is proposing a bold ban on ransomware payments by public entities to disrupt cybercrime, protect critical services, and lead global cybersecurity efforts.