Query Builder

The Query Builder provides a fluent, chainable API for building and executing queries on your models. It supports filtering, sorting, pagination, and data modification, all with a syntax inspired by SQL and modern ORMs.

Getting Started

// Fetch all users
const users = await User.query().fetch()

// Fetch users with conditions
const activeUsers = await User.query()
  .where('active', true)
  .fetch()

Filtering Records

where

Adds filter conditions to your query. Use this to select records matching specific criteria. Accepts key/value pairs, operators, objects, booleans, or callback functions for complex logic.

Parameter Table:

Parameter	Type	Description
callback	(query) => void	Group multiple conditions for advanced logic
conditions	Record<string, unknown>	Match all key-value pairs (AND logic)
key	string	Column name
value	any	Value to compare
operator	Operator (see Operators table below)	Comparison or pattern operator
boolean	boolean	true: exists, false: does not exist (primary key)

Return: this (chainable)

Overloads:

• where(callback: (query) => void): this — Group multiple conditions for advanced logic.
• where(conditions: Record<string, unknown>): this — Match all key-value pairs (AND logic).
• where<K extends string>(key: K, value: T[K]): this — Shorthand for equality.
• where<K extends string>(key: K, operator: Operator, value: any): this — Use comparison or pattern operators.
• where(value: boolean): this — true for existence, false for non-existence of primary key.

Examples:

// By key/value
User.query().where('role', 'admin')

// By operator
User.query().where('age', '>=', 18)

// By object
User.query().where({ status: 'active', type: 'premium' })

// By boolean
User.query().where(true) // where primaryKey exists
User.query().where(false) // where primaryKey does not exist

// By callback (grouped conditions)
User.query().where(q => q.where('role', 'admin').orWhere('role', 'moderator'))

andWhere / orWhere

Adds additional AND/OR conditions to your query. Use andWhere to require more conditions, or orWhere to allow alternatives. All overloads from where are available.

Parameter Table:

Parameter	Type	Description
callback	(query) => void	Group multiple conditions for advanced logic
conditions	Record<string, unknown>	Match all key-value pairs (AND/OR logic)
key	string	Column name
value	any	Value to compare
operator	Operator (see Operators table below)	Comparison or pattern operator
boolean	boolean	true: exists, false: does not exist (primary key)

Return: this (chainable)

Overloads:

• andWhere(callback: (query) => void): this — Grouped AND logic.
• andWhere(conditions: Record<string, unknown>): this — Add more AND key-value pairs.
• andWhere<K extends string>(key: K, value: T[K]): this — AND equality.
• andWhere<K extends string>(key: K, operator: Operator, value: any): this — AND with operator.
• andWhere(value: boolean): this — AND with boolean existence.
• orWhere(callback: (query) => void): this — Grouped OR logic.
• orWhere(conditions: Record<string, unknown>): this — OR with key-value pairs.
• orWhere<K extends string>(key: K, value: T[K]): this — OR equality.
• orWhere<K extends string>(key: K, operator: Operator, value: any): this — OR with operator.
• orWhere(value: boolean): this — OR with boolean existence.

Examples:

User.query().where('role', 'admin').andWhere('verified', true)
User.query().where('role', 'admin').orWhere('role', 'moderator')
User.query().andWhere(q => q.where('score', '>', 80).where('active', true))

Negation & Special Filters (All Variants)

Negation Methods

Negate any filter condition. Use these to exclude records matching certain criteria. All overloads from where/andWhere/orWhere are available as whereNot, andWhereNot, orWhereNot.

Parameter Table:

Parameter	Type	Description
callback	(query) => void	Group multiple conditions for advanced logic
conditions	Record<string, unknown>	Match all key-value pairs (AND/OR logic)
key	string	Column name
value	any	Value to compare
operator	Operator (see Operators table below)	Comparison or pattern operator
boolean	boolean	true: exists, false: does not exist (primary key)

Return: this (chainable)

Examples:

User.query().whereNot('role', 'guest')
User.query().andWhereNot({ status: 'inactive' })
User.query().orWhereNot(q => q.where('score', '<', 50))

Special Filters

Fine-tune your queries with these helpers:

Method	Parameters	Description
whereIn	key, values: any[]	Match if the column value is in the array
andWhereIn/orWhereIn	key, values: any[]	AND/OR variants
whereNotIn	key, values: any[]	Exclude values in the array
andWhereNotIn/orWhereNotIn	key, values: any[]	AND/OR variants
whereNull	key	Match records where the column is null
andWhereNull/orWhereNull	key	AND/OR variants
whereNotNull	key	Match records where the column is not null
andWhereNotNull/orWhereNotNull	key	AND/OR variants
whereBetween	key, [min, max]	Match values within a range (inclusive)
andWhereBetween/orWhereBetween	key, [min, max]	AND/OR variants
whereNotBetween	key, [min, max]	Exclude values within a range
andWhereNotBetween/orWhereNotBetween	key, [min, max]	AND/OR variants
whereLike	key, pattern	SQL LIKE pattern match (case-sensitive)
andWhereLike/orWhereLike	key, pattern	AND/OR variants
whereILike	key, pattern	Case-insensitive LIKE
andWhereILike/orWhereILike	key, pattern	AND/OR variants

Return: this (chainable)

Examples:

User.query().whereIn('role', ['admin', 'user'])
User.query().whereNotIn('status', ['inactive', 'banned'])
User.query().whereNull('deletedAt')
User.query().whereNotNull('email')
User.query().whereBetween('age', [18, 30])
User.query().whereNotBetween('score', [0, 100])
User.query().whereLike('name', 'J%')
User.query().whereILike('email', '%@gmail.com')

Operators

The following operators are available for use in where, andWhere, orWhere, and their negation variants. Use these to compare, match, or check for existence/null:

Operator	Description
=, is	Equal to
!=, is not	Not equal to
<	Less than
<=	Less than or equal to
>	Greater than
>=	Greater than or equal to
like	SQL LIKE pattern match (case-sensitive)
not like, <>	Not LIKE pattern (case-sensitive)
in	Value is in array
not in	Value is not in array
between	Value is between two values (inclusive)
not between	Value is NOT between two values
null	Value is null (use whereNull)
not null	Value is not null (use whereNotNull)
exists	Value exists (not null/undefined)
not exists	Value does not exist (null/undefined)
ilike	LIKE, case-insensitive
not ilike	NOT LIKE, case-insensitive

Chaining, Immutability, and TypeScript Tips

Chaining

All query builder methods return this, so you can chain calls to build up complex queries in a readable way.

Immutability & Side Effects

Query builders are mutable. If you reuse a builder, subsequent changes will affect all references. Use .clone() to create a new, independent builder if you want to branch your logic.

const base = User.query().where('active', true)
const admins = base.clone().where('role', 'admin').fetch() // Only active admins
const mods = base.clone().where('role', 'moderator').fetch() // Only active moderators

TypeScript Usage Tips

• Type safety is provided by the model class itself. When you call User.query(), the resulting builder is already typed for the model's fields.
• All methods are strongly typed; invalid keys/operators will be caught by TypeScript.
• Use type inference for model fields in conditions.
• If you need to use dynamic keys, cast as keyof T or use type assertions.
• For advanced use, you can explicitly type the builder:

const q: ReactiveQueryBuilder<User> = User.query().where('active', true)

• All filter methods return this for chaining.

Handling Result Types from fetch and then

The result of fetch (or using .then on the builder) depends on your query:

• fetch() returns a Promise<Array<User>> (or your model type) for multi-record queries.
• first(), last(), or similar methods return a Promise<User | undefined>.
• If you use .then directly on the builder, the resolved value matches the terminal method (e.g., fetch, first).

TypeScript Example:

// Multiple results
const users: User[] = await User.query().where('active', true).fetch()

// Single result (may be undefined)
const firstUser: User | undefined = await User.query().where('active', true).first()

// Using .then
User.query().where('active', true).fetch().then((users) => {
  // users: User[]
})
User.query().where('active', true).first().then((user) => {
  // user: User | undefined
})

• Always check for undefined when using first, last, or similar methods.
• Use explicit type annotations if you need to clarify the result type for downstream code or editors.

Query Builder Utilities

clear

Removes all conditions and clauses from the builder. Use to reset a query and start over.

const query = User.query().where('role', 'admin')
query.clear()

Parameter	Type	Description
(none)

Returns	Type	Description
this	ReactiveQueryBuilder	The same builder instance, cleared

Error Handling:

• No errors are thrown. Safe to call at any time.

clone

Creates a new builder with the same conditions. Use to branch queries without side effects.

const base = User.query().where('active', true)
const admins = base.clone().where('role', 'admin').fetch()
const mods = base.clone().where('role', 'moderator').fetch()

Parameter	Type	Description
(none)

Returns	Type	Description
builder	ReactiveQueryBuilder	A new builder instance with copied state

Error Handling:

• No errors are thrown. The clone is always safe to use.

Error Handling & Pitfalls

• Query results are not reactive unless you call .reactive(). By default, they are static snapshots.
• Most filtering is in-memory. Large datasets and complex queries may impact performance.
• Mutations (update/delete) fetch records first.
• Query builders are mutable. Use clone() to avoid side effects.

Error/Pitfall	Description	How to Avoid
Non-reactive results	Query results do not update automatically if data changes	Re-run the query to get fresh data
In-memory filtering	All filtering is done in JavaScript, not in a database	Use pagination and limit result set size
Mutations fetch first	update/delete always fetch records before mutating	Be aware of performance on large sets
Builder mutability	Query builder is mutable by default	Use clone() for branching

API Reference (Summary)

Method	Description	Chainable	Immutability
where, andWhere, orWhere, whereNot, ...	Filtering methods	Yes	No (mutates builder)
orderBy, limit, offset, forPage	Sorting & pagination	Yes	No
update, delete, fetch, first, last, count	Data modification & retrieval	No (terminates chain)	N/A
clear, clone	Utilities	Yes	clone is immutable, clear mutates

See the full API documentation for detailed method signatures and type information.

TypeScript & Chaining Tips

• All builder methods are chainable unless they return results (fetch, first, etc.).
• Use clone() to branch queries safely in TypeScript.
• The builder is generic: ReactiveQueryBuilder<T>. Type inference works with model classes.
• Use explicit types for advanced patterns:

const q: ReactiveQueryBuilder<User> = User.query().where('active', true)

• All filter methods return this for chaining.

Advanced Query Patterns

• Branching:

const base = User.query().where('active', true)
const admins = base.clone().where('role', 'admin').fetch()
const mods = base.clone().where('role', 'moderator').fetch()

• Grouped conditions:

User.query().where(q => q.where('role', 'admin').orWhere('role', 'moderator'))

• Negation:

User.query().whereNot('status', 'inactive')

• Pagination:

User.query().orderBy('created_at').forPage(2, 10)

Reactive Queries

Reactive queries provide live, event-driven updates to your query results. Instead of returning a static snapshot, a reactive query emits events when the underlying data changes (e.g., insert, delete, or any change that affects the result set). This is ideal for building real-time UIs or keeping data in sync automatically.

Usage

Call .reactive() on any query builder to get a reactive response factory. You can then call .fetch(), .first(), .last(), .forPage(), or .count() directly on the factory. The result is a reactive response object that emits events as the result set changes.

Example:

// Get a reactive collection of users
const collection = await User.query().where('active', true).reactive().fetch()
collection.value // Users[]
collection.on('next', users => {
  // users: User[]
  // Called initially and whenever the result set changes (insert/delete)
})
collection.on('error', err => { /* handle error */ })
collection.on('complete', () => { /* cleanup */ })

// Get a reactive single result
const firstUser = await User.query().where('active', true).reactive().first()
firstUser.value // User | undefined
firstUser.on('next', user => {
  // user: User | undefined
  // Called initially and whenever the first matching user changes (insert/delete)
})

// Unmount when done to clean up listeners
collection.unmount()
firstUser.unmount()

API

• .reactive().fetch() — Returns a ReactiveQueryCollection (emits next with an array of models)
• .reactive().first() — Returns a ReactiveQueryResult (emits next with a single model or undefined)
• .reactive().last() — Returns a ReactiveQueryResult (emits next with a single model or undefined)
• .reactive().forPage(page, perPage) — Returns a ReactiveQueryCollection for a specific page
• .reactive().count() — Returns a ReactiveQueryResult (emits next with a count)

Notes

Reactivity is event-driven

The next event is only emitted when the result set changes (e.g., a record is inserted or deleted, or a filter causes the set to change). Updates to the properties of a model instance do not trigger next (the model instance itself is reactive).

Unmounting

Always call .unmount() on the response when you no longer need updates to avoid memory leaks.

Type safety

The reactive response is strongly typed to match your model and query.

---

API Consistency Table

Method	AND Variant	OR Variant	Negation	In/NotIn	Null/NotNull	Between/NotBetween	Like/ILike
where	andWhere	orWhere	whereNot	whereIn/whereNotIn	whereNull/whereNotNull	whereBetween/whereNotBetween	whereLike/whereILike
	andWhereNot	orWhereNot		andWhereIn/andWhereNotIn	andWhereNull/andWhereNotNull	andWhereBetween/andWhereNotBetween	andWhereLike/andWhereILike
				orWhereIn/orWhereNotIn	orWhereNull/orWhereNotNull	orWhereBetween/orWhereNotBetween	orWhereLike/orWhereILike

All variants support the same overloads and chaining. Negation and special filters are available for all logical operators.