Query operators reference

Query operators reference Every non-trivial where clause in prorm is expressed with operators. Write one by name, inside the condition object for a column: That is the form to reach for. Three others mean exactly the same thing and all still work, so existing code needs no change: All four compile to the same SQL. Op is exported from src/operators.ts (also as Operators), alongside helper functions such as and, or, eq, like, between. A bare value is shorthand for equality: { name: 'John' } is identical to { name: { eq: 'John' } }. Note: the tables below name each operator with its $ prefix, because that is the canonical internal form. Drop the $ and you have the plain name — $gte is gte, $notILike is notILike. Where a name is read as an operator Inside a column's condition — the { … } after a column name — every key is an operator, so any operator name is understood there. At the top level of a where, keys are column names, so only the logical operators and, or and not are read as operators. A column called contains or like therefore keeps working: A column actually named and, or or not has to be reached another way — they are SQL reserved words, so this is unlikely to come up. Note: before this, a bare name was silently treated as a value — { age: { gte: 18 } } compiled to age = '{"gte":18}', a query that ran and quietly matched nothing rather than raising an error. If you have a query that mysteriously returned no rows, this may be why. Comparison operators The core comparison operators map directly to SQL comparison operators. Each takes a single value. Helper equivalents: eq, ne, gt, gte, lt, lte. Each is called as helper(column, value): Any comparison value may be a column reference (Op.col) or a raw literal (Op.literal) instead of a bound value — see Column & literal references. Range & set operators in / notIn take an array of candidate values. between / notBetween take a two-element [start, end] tuple. Helpers: inOp (exported as in), notIn, between(column, start, end), notBetween(column, start, end). NULL checks isNull and isNotNull emit IS NULL / IS NOT NULL. In symbol form pass true; the value is only used to pick the direction ({ [Op.isNull]: false } compiles to IS NOT NULL). Helpers isNull(column) and isNotNull(column) are the concise form: Op.is and Op.not also exist. Op.not used as a field key negates equality ({ status: { [Op.not]: 'active' } } → status != ?); Op.not used as a top-level key negates a whole condition (see Logical operators). Pattern matching (LIKE) like / notLike are case-sensitive; iLike / notILike are the case-insensitive (ILIKE) variants. In symbol form you supply the full pattern including % wildcards. The helper functions (like, notLike, iLike, notILike) wrap the value in %…% for you (contains matching), so you pass the bare term: Affix helpers — startsWith / endsWith / contains These helpers build one-sided or two-sided LIKE patterns without you writing %: substring is the "contains" helper (identical to like). notSubstring is its negation. Regular-expression operators regexp / notRegexp and their case-insensitive forms iRegexp / notIRegexp emit database-specific REGEXP clauses. Values may be a string or a RegExp. Helpers: regexp, notRegexp, iRegexp, notIRegexp — each helper(column, pattern). Logical operators and, or, not combine or negate conditions. The helper functions are the ergonomic form: and(...conditions), or(...conditions), not(condition). The where({ AND: [...] }) / where({ OR: [...] }) object form is also accepted and normalized to $and / $or. The Op.and / Op.or / Op.not symbols work as keys too, but the helper functions are clearer. Existence & subquery operators exists / notExists wrap a correlated subquery (a SQL string or Op.literal). Useful for semi-joins and anti-joins. inSubquery(column, subquery) and notInSubquery(column, subquery) build IN (…) / NOT IN (…) against a subquery instead of a value array: For join-based exclusion there are also antiJoin(model, options) and its alias exclude(model, options), used inside include. Array operators (PostgreSQL) For PostgreSQL array columns: Each has a matching helper (arrayContains, arrayContainedBy, arrayOverlaps, arrayAny, arrayAll). The generic Op.any / Op.all symbols (helpers anyOp/allOp, exported as any/all) build = ANY(...) / = ALL(...) comparisons. JSON / JSONB operators For JSON and JSONB columns. The most common are contains, containedBy, keyExists, and overlap: To query a value at a JSON path, use Op.json(path) as a computed key, or Op.key: PostgreSQL-specific JSONB operators are also available as symbols: jsonContains (@>), jsonHasKey (?), jsonbExtract (->), jsonbExtractText (->>), jsonbExtractPath (#>), jsonbExtractPathText (#>>), jsonConcat (||), jsonDelete (-), jsonDeletePath (#-), jsonPathExists (@?), jsonPathQuery (@@), and jsonTypeOf (jsontypeof). A legacy Op.$json symbol also exists. Column & literal references Op.col(name) references another column; Op.literal(sql) embeds a raw SQL expression. Both are values, usable anywhere a comparison value is expected. Op.where(column, value) or Op.where(column, operator, value) builds an explicit clause, accepting either a column name or Op.col(...) and string operators ('=', '!=', '>', 'like', 'between', 'is null', …) or operator symbols: Full-text search Op.match / helper match(column, term) build a database-appropriate full-text clause. For MySQL, Op.matchAgainst(columns, { mode }) and Op.matchFulltext(columns) produce MATCH … AGAINST … keys; for PostgreSQL, Op.toTsvector(column, config) and Op.toTsquery(query, config) build totsvector / totsquery expressions. Spatial / geometric operators Symbol operators for PostGIS / MySQL spatial columns: stDistance, stWithin, stContains, stIntersects, stDWithin, stCrosses, stOverlaps, stTouches, stEquals, stIsValid. Range/interval operators strictLeft, strictRight, noExtendRight, noExtendLeft, adj, notAdj are likewise available as symbols for PostgreSQL range types. Expression & ordering helpers Op also carries expression builders used in attributes and order, not just where: Op.conv(value, from, to) builds a MySQL CONVERT / cast expression. Cheat sheet Grouped rather than listed, so you can find the row you want. Every operator below is documented in full further up this page. Comparison Operator | SQL | --- | --- | Op.eq Op.ne | = ? · != ? | Op.gt Op.gte | > ? · >= ? | Op.lt Op.lte | < ? · <= ? | Sets, ranges and null Operator | SQL | --- | --- | Op.in Op.notIn | IN (…) · NOT IN (…) | Op.between Op.notBetween | BETWEEN ? AND ? · NOT BETWEEN ? AND ? | Op.isNull Op.isNotNull | IS NULL · IS NOT NULL | Pattern matching Operator | SQL | --- | --- | Op.like Op.notLike | LIKE ? · NOT LIKE ? | Op.iLike Op.notILike | ILIKE ? · NOT ILIKE ? (PostgreSQL) | startsWith endsWith substring | LIKE 'v%' · LIKE '%v' · LIKE '%v%' | Op.regexp Op.iRegexp | REGEXP · case-insensitive REGEXP | Logic and subqueries Operator | SQL | --- | --- | Op.and Op.or Op.not | AND · OR · NOT (…) | Op.exists Op.notExists | EXISTS · NOT EXISTS | inSubquery notInSubquery | IN (SELECT …) · NOT IN (SELECT …) | Arrays — PostgreSQL Operator | SQL | --- | --- | Op.arrayContains Op.arrayContainedBy | @> · <@ | Op.arrayOverlaps | && | Op.any Op.all | = ANY(…) · ALL(…) | JSON Operator | SQL | --- | --- | Op.contains Op.containedBy | @> · <@ | Op.keyExists Op.overlap | key exists · && | Op.json(path) Op.key | path extraction | Op.jsonHasKey Op.jsonbExtract Op.jsonPathExists | ? · -> · @? (PostgreSQL) | Expressions and ordering Operator | SQL | --- | --- | Op.col Op.literal Op.where | a column · raw SQL · a bare condition | Op.cast Op.extract Op.conv | CAST · EXTRACT · CONVERT | Op.asc Op.desc Op.random | ORDER BY … ASC · DESC · RANDOM() | Op.match Op.matchAgainst | MATCH … AGAINST · @@ | Op.stWithin Op.stDWithin Op.stIntersects | the ST spatial functions | Next: Associations — the four relationship shapes and which side owns the foreign key. Related reading Querying — where these operators are used SQL functions — fn(), col() and literal()