Operators and Conditions

.where() (on Select, Update and Delete) and .on() (on join elements) accept operator instances built with the functions below, plain object literals (a shorthand that's expanded into the same operator instances), Raw fragments, or nested And/Or groups. Multiple arguments — and multiple calls to .where() — are combined with an implicit top-level And.

import { Select, Eq, And, Or } from '@sqb/builder';

Select().from('customers').where(Eq('active', true), Eq('country', 'US'));
// where active = true and country = 'US'

Every operator listed here has its own API reference page under API Reference → Operators.

The object-literal shorthand

Instead of calling operator functions directly, you can pass plain objects to .where(). Each own-property is expanded into a comparison. The key is <field> [operator suffix]; the suffix defaults to eq (or in when the value is an array):

Select().from('customers').where({ id: 1, 'name !=': 'John', age: [18, 19, 20] });
// where id = 1 and name != 'John' and age in (18,19,20)

The suffix maps to an operator through this table (from Operators in op.ns.ts):

Suffix(es)	Operator
(none, non-array value)	Eq
(none, array value)	In
=, eq	Eq
!=, ne	Ne
>, gt	Gt
>=, gte	Gte
<, lt	Lt
<=, lte	Lte
between, btw	Between
!between, !btw, notBetween, nbtw	NotBetween
in	In
!in, notIn, nin	NotIn
like	Like
!like, notLike, nlike	NotLike
ilike	ILike
!ilike, notILike, nilike	NotILike
is	Is
!is, isNot	IsNot

The two keys and/or (case-insensitive) and exists/!exists are special-cased instead of being suffixes:

Select().from('customers').where({ and: [{ id: 1 }, { id: 2 }] });
// where (id = 1 and id = 2)

Select().from('customers').where({ OR: [{ id: 1 }, { id: 2 }] });
// where (id = 1 or id = 2)

Select().from('customers').where({ exists: Select().from('orders') });
// where exists (select * from orders)

Select().from('customers').where({ '!exists': Select().from('orders') });
// where not exists (select * from orders)

An unrecognized suffix throws:

Select().from('customers').where({ 'id non': 3 });
// Error: Unknown operator "non"

and() / or()

Combine any number of operators, Raw fragments, or nested And/Or groups. Falsy items (null, undefined, 0) are silently skipped.

import { And, Or, Eq } from '@sqb/builder';

Select().from('customers').where(And(Eq('id', 1), Eq('id', 2)));
// where (id = 1 and id = 2)

Select().from('customers').where(Or(And(Eq('id', 1), Eq('id', 2)), Eq('id', 3)));
// where ((id = 1 and id = 2) or id = 3)

Both .where() on a query and And/Or themselves accept a mix of operator instances and plain object literals — objects are expanded with the same shorthand rules described above.

eq() / ne() / gt() / gte() / lt() / lte()

Comparison operators. The left-hand side can be a field-name string (optionally with a trailing [] to mark it an array field) or an SQL element (Field, Raw, sub-Select); the right-hand side can be a literal, Param, Raw, or sub-Select.

import { Eq, Ne, Gt, Gte, Lt, Lte } from '@sqb/builder';

Eq('id', 1); // id = 1
Ne('id', 1); // id != 1
Gt('age', 18); // age > 18
Gte('age', 18); // age >= 18
Lt('price', 100); // price < 100
Lte('price', 100); // price <= 100

Comparing two columns

A bare string on the right-hand side is treated as a literal value, not a column — it gets quoted like any other string. To compare against another column, wrap it in Field so it's serialized as an identifier instead:

import { Eq, Field } from '@sqb/builder';

Eq('customer_id', 'customers.id'); // customer_id = 'customers.id'  (a string literal!)
Eq('customer_id', Field('customers.id')); // customer_id = customers.id  (a column reference)

This is exactly why join conditions and correlated sub-queries always wrap the outer column in Field(...) — see Joins.

between() / notBetween()

import { Between, NotBetween } from '@sqb/builder';

Between('id', 10, 20); // id between 10 and 20
Between('id', [10, 20]); // same, as an array
Between('id', [10]); // id between 10 and 10 (missing upper bound repeats the lower)
NotBetween('id', 10, 20); // id not between 10 and 20

in() / notIn()

import { In, NotIn } from '@sqb/builder';

In('id', [1, 2, 3]); // id in (1,2,3)
NotIn('id', [1, 2, 3]); // id not in (1,2,3)

An empty array is special-cased to avoid silently dropping the filter: In('id', []) serializes to the literal 1=0 (always false) and NotIn('id', []) to 1=1 (always true).

like() / notLike() / ilike() / notILike()

import { Like, NotLike, ILike, NotILike } from '@sqb/builder';

Like('name', 'John%'); // name like 'John%'
NotLike('name', 'John%'); // name not like 'John%'
ILike('name', 'john%'); // name ilike 'john%' (case-insensitive)
NotILike('name', 'john%'); // name not ilike 'john%'

ILike/NotILike emit the Postgres-style ilike/not ilike keywords by default; a dialect that lacks native ILIKE (e.g. by registering a SerializerExtension) can rewrite this to upper(name) like upper('john%') or similar.

is() / isNot()

import { Is, IsNot } from '@sqb/builder';

Is('deleted_at', null); // deleted_at is null
IsNot('deleted_at', null); // deleted_at is not null

exists() / notExists()

Takes a Select or Raw sub-query — anything else throws a TypeError.

import { Exists, NotExists, Select, Field } from '@sqb/builder';

const hasOrders = Select().from('orders').where(Eq('customer_id', Field('customers.id')));

Select().from('customers').where(Exists(hasOrders));
// where exists (select * from orders where customer_id = customers.id)

Select().from('customers').where(NotExists(hasOrders));
// where not exists (...)

not()

Negates a single operator or Raw expression by prefixing it with not. This is different from Ne/IsNot: it wraps an arbitrary expression rather than comparing two values.

import { Not, Eq } from '@sqb/builder';

Select().from('customers').where(Not(Eq('active', true)));
// where not active = true

match()

A comparison operator intended as an extension point for dialect-specific full-text search (MATCH() AGAINST() in MySQL, @@ in Postgres, CONTAINS() in SQL Server, ...). By itself it serializes like Eq (field = 'value'); a SerializerExtension registered for SerializationType.COMPARISON_EXPRESSION can inspect _operatorType === OperatorType.match (or the customArgs you pass as the third argument) and render the dialect-specific syntax instead.

import { Match } from '@sqb/builder';

Match('description', 'wireless mouse');
// falls back to: description = 'wireless mouse'

See Generating SQL per dialect for how dialect packages hook into serialization.

Nesting and sub-queries

Any operator's left or right side may itself be a Select, Raw, or another operator/field — the builder parenthesizes sub-queries automatically:

Select().from('customers').where(Eq('id', Select('id').from('vip_customers')));
// where id = (select id from vip_customers)