A tool for creating hierarchical logical conditions and expressions, mainly to be used in - but not limited to - an SQL where statement.
Expression is the base building block. It consists of key, operator and value.
`a=b`;Condition is a hierarchical collection of one or more expressions or conditions
joined by a logical join operator: and, or, andNot, or orNot.
// condition with one expression
`a=b`;
// condition with 2 expressions joined by `or`
`a=b or c=d`;
// condition of multiple hierarchically structured expressions and conditions
`a=b or (c>d and (e<f or g!=h))`;deno
deno add jsr:@marianmeres/condition-buildernodejs
npm i @marianmeres/condition-builderimport { Condition } from "@marianmeres/condition-builder";The core api consists of 2 methods and(...) and or(...). For the first call you can
use any one of them.
const c = new Condition();
c.and("a", OPERATOR.eq, "b");
// c.or("a", OPERATOR.eq, "b"); // same effect as above for the first call
assertEquals(c.toString(), "a=b");
c.or("c", OPERATOR.neq, "d");
assertEquals(c.toString(), "a=b or c!=d");
c.or(
new Condition()
.and("e", OPERATOR.lt, "f")
.and("g", OPERATOR.eq, "h")
.or(
new Condition()
.and("i", OPERATOR.match, "j")
.and("k", OPERATOR.nmatch, "l"),
),
);
assertEquals(c.toString(), "a=b or c!=d or (e<f and g=h or (i~*j and k!~*l))");
// dump & restore
const c2 = Condition.restore(c.dump());
assertEquals(c2.toString(), "a=b or c!=d or (e<f and g=h or (i~*j and k!~*l))");
// or export the condition as POJO structure for manual processing (eg evaluation)
const structure = c.toJSON();SQL's AND binds tighter than OR. Since the builder API is left-associative
(each chained call folds into a running accumulator), the rendered string is
auto-parenthesized to preserve the order in which calls were made:
const c = new Condition()
.and("a", OPERATOR.eq, "1")
.or("b", OPERATOR.eq, "2")
.and("c", OPERATOR.eq, "3");
c.toString(); // "(a=1 or b=2) and c=3"Without the extra parentheses the string would be a=1 or b=2 and c=3, which
SQL parses as a=1 OR (b=2 AND c=3) — a different query. The library emits
only the parentheses that are necessary to disambiguate.
When the operator is in or nin and the value is an array, it is rendered
as a parenthesized, comma-separated list. Each element passes through
renderValue individually, so escaping/parameterization applies.
new Condition()
.and("id", OPERATOR.in, [1, 2, 3])
.toString(); // "id in (1,2,3)"Point of this package is to create a textual representation of the logical conditions blocks to be used in an sql where statement. By default, the package is content and dialect agnostic. Just renders the input as is, which may not be always desired.
To validate the condition, you must provide the validate function which will validate
every expression before being added to the condition.
const c = new Condition({
// this example will allow only a known keys to be set
validate: (ctx: ExpressionContext) => {
const { key } = ctx;
const keyWhitelist = ["foo"];
if (!keyWhitelist.includes(key)) {
throw new TypeError(`Key '${key}' not allowed`);
}
},
});
// `foo` key is allowed
c.and("foo", OPERATOR.eq, "1");
// `bar` is not
assertThrows(() => c.and("bar", OPERATOR.neq, "2"));To match the textual representation for any specific format you must provide any of the
renderKey, renderValue, or renderOperator functions.
For example for postgresql dialect you may use something like this:
const c = new Condition({
// escape identifiers in postgresql dialect
renderKey: (ctx: ExpressionContext) => `"${ctx.key.replaceAll('"', '""')}"`,
// escape values in postgresql dialect
renderValue: (ctx: ExpressionContext) =>
`'${ctx.value.toString().replaceAll("'", "''")}'`,
// read below
// renderOperator(ctx: ExpressionContext): string
});
c.and('fo"o', OPERATOR.eq, "ba'r");
assertEquals(c.toString(), `"fo""o"='ba''r'`);There is a default built-in operator-to-symbol replacement logic (targeting postgresql dialect), loosely inspired by postgrest.
Any found operator in the map below will be replaced with its symbol. If the operator is
not found in the map, no replacement will happen. You can customize this logic by
providing your own custom renderOperator function.
// default opinionated conversion map of operators to operator symbols.
{
eq: "=",
neq: "!=",
gt: ">",
gte: ">=",
lt: "<",
lte: "<=",
like: " ilike ",
nlike: " not ilike ",
match: "~*",
nmatch: "!~*",
is: " is ",
nis: " is not ",
in: " in ",
nin: " not in ",
// PostgreSQL ltree operators
ltree: "~",
ancestor: "@>",
descendant: "<@",
};
// but you can safely use any operator you see fit...
const e = new Expression("foo", "==", "bar");
assertEquals(e.toString(), "foo==bar");For PostgreSQL output, two presets are shipped:
Double-quotes identifiers and single-quote-escapes string literals. Handles
null, booleans, numbers, and bigints natively.
import { Condition, OPERATOR, pgRenderers } from "@marianmeres/condition-builder";
const c = new Condition()
.and('fo"o', OPERATOR.eq, "ba'r")
.and("id", OPERATOR.in, [1, 2, 3])
.or("active", OPERATOR.is, null);
c.toString(pgRenderers);
// "fo""o"='ba''r' and "id" in (1,2,3) or "active" is nullEmits $1, $2, … placeholders and collects values into a shared array.
Values never touch the SQL string, which makes this the safest option for
input you don't control.
import { Condition, OPERATOR, pgParameterized } from "@marianmeres/condition-builder";
const { options, params } = pgParameterized();
const c = new Condition()
.and("id", OPERATOR.in, [1, 2, 3])
.and("name", OPERATOR.eq, "'; drop table users; --");
const sql = c.toString(options);
// "id" in ($1,$2,$3) and "name"=$4
// params: [1, 2, 3, "'; drop table users; --"]
// Pass to your driver:
// await client.query(`select * from users where ${sql}`, params);Pass a custom startIndex if you're stitching the condition into a larger
query that already has parameters.
See CHANGELOG.md for release notes and breaking-change details.
See API.md for the complete API documentation.