Comparison Operators

Each operator is typed by the field it applies to, and only the operators that fit a field’s type are accepted:

• Every field: $eq, $ne, $not, $in, $nin, $isNull, $isNotNull (a bare array is an implicit $in on scalar fields).
• Comparable fields (string, number, bigint, Date): $lt, $lte, $gt, $gte, $between.
• String fields: $like, $ilike, $regex, $startsWith, $istartsWith, $endsWith, $iendsWith, $includes, $iincludes.
• Array fields: $all, $size, $elemMatch.

An inapplicable combination, such as { age: { $like: '3%' } }, { active: { $gt: false } } or { name: { $size: 3 } }, is a compile error.

Name	Description
$eq	Equal to.
$ne	Not equal to (null-safe: rows where the column is NULL still match when the value is not null).
$lt	Less than.
$lte	Less than or equal to.
$gt	Greater than.
$gte	Greater than or equal to.
$like	SQL LIKE pattern match (case sensitive). E.g. { name: { $like: 'John%' } }.
$ilike	SQL ILIKE pattern match (case insensitive). E.g. { name: { $ilike: 'john%' } }.
$regex	Regular expression match. E.g. { name: { $regex: '^test' } }. On MSSQL it needs a 2025 server at compatibility level 170.
$startsWith	Starts with (case-sensitive).
$istartsWith	Starts with (case-insensitive).
$endsWith	Ends with (case-sensitive).
$iendsWith	Ends with (case-insensitive).
$includes	Contains substring (case-sensitive).
$iincludes	Contains substring (case-insensitive).
$in	Value matches any in a given array.
$nin	Value does not match any in a given array.
$between	Value is between two bounds (inclusive). E.g. { age: { $between: [18, 65] } }.
$isNull	Field is null. E.g. { deletedAt: { $isNull: true } }.
$isNotNull	Field is not null. E.g. { email: { $isNotNull: true } }.
$all	Array contains all specified values. E.g. { tags: { $all: ['ts', 'orm'] } }.
$size	Array has the specified length. Accepts a number for exact match ({ tags: { $size: 3 } }) or comparison operators ({ tags: { $size: { $gte: 2 } } }). Also filters by a relation’s size; to return or rank by that size instead, see counting.
$elemMatch	Array contains an element matching the condition. Object elements take per-key conditions ({ addresses: { $elemMatch: { city: 'NYC' } } }); scalar elements take an operator map ({ tags: { $elemMatch: { $startsWith: 'ad' } } }).
$text	Full-text search. See Full-Text Search for per-dialect SQL and index requirements.

Practical Example

You write

import { pool } from './uql.config.js';

import { User } from './shared/models/index.js';

const users = await pool.findMany(User, {
  $select: { id: true, name: true },
  $where: {
    name: { $istartsWith: 'Some', $ne: 'Something' },
    age: { $gte: 18, $lte: 65 },
  },
  $sort: { name: 'asc' },
  $limit: 50,
});

Context-Aware SQL Generation

The case-insensitive operators reach the same rows on every engine, by whichever route that engine has: PostgreSQL and CockroachDB have ILIKE; SQLite’s LIKE already ignores case on both sides; the MySQL family has neither, so both sides are lowered explicitly, which is why you see LOWER(...) there and nowhere else. $ne diverges further: IS DISTINCT FROM, NOT (a <=> b) and IS NOT are three spellings of the same null-safe inequality.

PostgreSQL

SELECT "id", "name" FROM "User"
WHERE ("name" ILIKE $1 AND "name" IS DISTINCT FROM $2) AND ("age" >= $3 AND "age" <= $4)
ORDER BY "name"
LIMIT 50

MySQL / MariaDB

SELECT `id`, `name` FROM `User`
WHERE (LOWER(`name`) LIKE ? AND NOT (`name` <=> ?)) AND (`age` >= ? AND `age` <= ?)
ORDER BY `name`
LIMIT 50

SQLite

SELECT `id`, `name` FROM `User`
WHERE (`name` LIKE ? AND `name` IS NOT ?) AND (`age` >= ? AND `age` <= ?)
ORDER BY `name`
LIMIT 50

What case-insensitive costs, per engine

On the MySQL family only an expression index over LOWER(column) can serve these. That costs an index for $istartsWith alone: the other patterns lead with %, which no plain index could serve anyway. Their default collation is case-insensitive, so $startsWith matches either case there and keeps its index, at the price of matching case-sensitively on PostgreSQL. On SQLite the folding is ASCII-only whichever way you write it, since the engine has no case mapping for accented characters without ICU.

$between: Range Queries

You write

const users = await pool.findMany(User, {
  $where: { age: { $between: [18, 65] } },
});

PostgreSQL

SELECT * FROM "User" WHERE "age" BETWEEN $1 AND $2

JSONB Dot-Notation Operators

The operators above also work on nested JSON paths with dot-notation (e.g. 'settings.isArchived': { $ne: true }) on PostgreSQL, MySQL, MariaDB and SQLite. JSON / JSONB covers filtering, the $set/$unset/$push/$pull update operators, and sorting by JSON paths.

---

Next Steps

• Logical Operators: Compose conditions with $and, $or, $not, $nor.
• Deep Relations: Filter and count across related entities.
• Counting: Count matches, test existence, and tally relations.
• JSON / JSONB: The same operators on nested JSON paths.
• Querier API: The full query API these operators live in.