@genebe/sql-where-parser

Parser for SQL-like filters (WHERE conditions with an optional ORDER BY) used by GeneBe. It turns text written by a user (or an LLM) into a JSON condition tree. No SQL is executed here, and there is no dependency on a database.

import { parseWhere, ParseError } from '@genebe/sql-where-parser';

const query = parseWhere("gene IN ('GJB2','BRCA1') AND NOT (AF_gnomad > 0.01) ORDER BY AF_gnomad DESC");
// {
//   op: 'AND',
//   en: [
//     { column: 'gene', operator: 'IN', value: ['GJB2', 'BRCA1'] },
//     { op: 'NOT', en: [{ column: 'AF_gnomad', operator: 'GREATER_THAN', value: 0.01 }] }
//   ],
//   sort: [{ name: 'AF_gnomad', ascending: false }]
// }

try {
    parseWhere('a == 1');
} catch (e) {
    if (e instanceof ParseError) console.log(e.message);
    // Syntax error at line 1:3 – unexpected '='; expected ... (use a single '=' for equality)
}

parseWhere always returns a node or throws ParseError (it never returns undefined). Error messages contain the position, what was expected and a hint for common mistakes, so they can be shown to a user or fed back to an LLM.

Back to text

toSql(node, { quote }) is the inverse of parseWhere: for every tree produced by the parser, parseWhere(toSql(tree)) equals the tree.

import { toSql } from '@genebe/sql-where-parser';

toSql(parseWhere("(a=1 OR b=2) AND NOT c LIKE 'x%' ORDER BY a"));
// (a = 1 OR b = 2) AND NOT c LIKE 'x%' ORDER BY a
toSql(tree, { quote: 'always' }); // "a" = 1 ...

Columns are quoted only when necessary (spaces, keywords, odd characters); parentheses appear only where precedence (NOT > AND > OR) or the tree structure requires them; EQUAL/NOT_EQUAL with no value become IS NULL/IS NOT NULL; numbers never use an exponent. Operators without a text form (e.g. NOOP), a missing value or an empty group throw an Error. ORDER BY prints NULLS FIRST|LAST whenever nullsLast is set.

Result

WhereNode: a logic node { op: 'AND' | 'OR' | 'NOT', en: WhereNode[] } or a condition { column, operator, value? }. The top level is always a logic node (a single condition is wrapped in AND), or { sort } when only ORDER BY was given; sort ({ name, ascending?, nullsLast? }[]) sits on the top-level node. Operators: EQUAL NOT_EQUAL LESS_THAN LESS_THAN_OR_EQUAL GREATER_THAN GREATER_THAN_OR_EQUAL LIKE NOT_LIKE LIKE_ANY NOT_LIKE_ANY ILIKE NOT_ILIKE ILIKE_ANY NOT_ILIKE_ANY CONTAINS NOT_CONTAINS CONTAINS_ANY NOT_CONTAINS_ANY IN NOT_IN EMPTY NOT_EMPTY. BETWEEN is expanded to >= / <=.

Language

| Construct | Notes | |---|---| | col = <> != < <= > >= literal | literal: number (1, -0.5, 1e-5), 'string' ('' escapes a quote), TRUE/FALSE | | col [NOT] LIKE 'a%', [NOT] ILIKE | ILIKE ignores case | | col [NOT] LIKE\|ILIKE ANY ('a%','b%') | any pattern matches | | col [NOT] IN (v1, v2) | non-empty list | | col [NOT] BETWEEN a AND b | | | col IS [NOT] NULL | = NULL is rejected | | col [NOT] CONTAINS 'x', [NOT] CONTAINS ANY (...) | extension: substring / list membership | | AND, OR, NOT, ( ) | precedence NOT > AND > OR | | ORDER BY col [ASC\|DESC] [NULLS FIRST\|LAST], ... | | | leading WHERE, trailing ;, -- and /* */ comments | accepted |

Columns: name, table.name, or "quoted name". Keywords are case-insensitive. Not supported (with an explanatory error): SELECT/FROM/JOIN/GROUP BY, subqueries, functions, arithmetic, column-to-column comparison, LIMIT/OFFSET, ==. Integers beyond ±2^53 are rejected.

Semantics of the consuming GeneBe backend worth knowing: <>, NOT IN, NOT LIKE, NOT CONTAINS (and ANY forms) also return rows where the column is NULL, whereas NOT (...) follows SQL three-valued logic; on list columns =/IN mean exact list equality and CONTAINS ANY is membership.

Development

The grammar (FilterQuery.g4) is shared with the Java implementation in the same repository, which is the source of truth. npm run build generates the TypeScript lexer/parser from it with antlr4ng-cli (needs a JVM; consumers of the published package do not). npm test runs the shared conformance corpus used by the Java tests too.