Docs / Build an application

GuidesSupported

Short, task-shaped answers to things people actually search for. Each one is a working snippet against the real API, plus the reason it is written that way.

Querying#

Conditional filters	Building a `where` from optional inputs without dropping `0` and `''`
Count rows	`hasMore` is free, a total is not, and `aggregate` is how you get one
Cursor-based pagination	Keyset pagination, and why `OFFSET` gets slower
`EXISTS` subqueries	`whereExists` and correlated subqueries
Dynamic queries	Composing a builder across functions

Writing#

Increment / decrement	Repository `increment` and expression-valued patches
Toggle a boolean	Atomic `not()` through `update` / `updateMany`
Bulk update	One shared patch ships; different values per row remain ToDo
Upsert	Typed conflict targets and expression-valued update fields

Increment and toggle use the same closed expression vocabulary in UpdateBuilder.set() and BaseRepository patches. Updating different rows to different values is a separate, wider CASE / VALUES problem; updateMany covers one validated patch over all matching rows.

Schema#

Array and JSON defaults	`defaultTo` with a JSON value, and the shared-reference trap
Timestamp defaults	`now()` in the database versus `new Date()` in the process
Case-insensitive unique	Expression index on the PostgreSQL family/SQLite; generated column on the MySQL family/SQL Server
Full-text search with generated columns	`tsvector` via `generatedColumnDdl`
Vector search	ToDo — typed distance ships; complete runnable guide remains
PostGIS	ToDo — geometry predicates ship; full guide remains

Local development#

Local Postgres	Docker, `psql`, and a test database that resets fast
Local MySQL	Same, plus the collation settings that matter
PGlite	Postgres in-process, for tests

The three facts behind most of these#

Worth knowing before reading any individual page:

Operator is SQL, not an abbreviation. where('age', '>=', 18), not 'gte'. The DTO form uses { age: { gte: 18 } } — the two layers spell it differently, deliberately.
References is a tag, not a function. authorId: number & Sql<'integer'> & References<'users.id'>. The target is a table.column string literal, so nothing has to be imported — and nothing

cross-checks it either.

The builder is immutable. Every where/orderBy/limit returns a new builder, so b.where(...) without reassigning does nothing.

---

See also: Tutorials · Query Builder · Gotchas

← Previous

Building CLI Applications

Conditional filters