StabilizeORM

Query Builder API

Fluent API for building complex database queries

select()

typescript
select(...fields: string[]): QueryBuilder<T>

Specifies columns to select. Defaults to *.

typescript
await repo.find().select("id", "name", "email").execute(orm.client);

where()

typescript
where(condition: string, ...params: any[]): QueryBuilder<T>

Adds a WHERE clause. Multiple calls join with AND.

typescript
await repo.find().where("age > ?", 18).where("isActive = ?", true).execute(orm.client);

orWhere()

typescript
orWhere(condition: string, ...params: any[]): QueryBuilder<T>

Adds an OR WHERE clause.

typescript
await repo.find().where("role = ?", "admin").orWhere("role = ?", "moderator").execute(orm.client);

whereIn() / whereNotIn()

typescript
whereIn(column: string, values: any[]): QueryBuilder<T>
whereNotIn(column: string, values: any[]): QueryBuilder<T>
typescript
await repo.find().whereIn("status", ["active", "pending"]).execute(orm.client);

whereNull() / whereNotNull()

typescript
whereNull(column: string): QueryBuilder<T>
whereNotNull(column: string): QueryBuilder<T>
typescript
await repo.find().whereNotNull("email").execute(orm.client);

whereBetween() / whereLike()

typescript
whereBetween(column: string, start: any, end: any): QueryBuilder<T>
whereNotBetween(column: string, start: any, end: any): QueryBuilder<T>
whereLike(column: string, pattern: string): QueryBuilder<T>
whereILike(column: string, pattern: string): QueryBuilder<T>
typescript
await repo.find().whereBetween("age", 18, 65).execute(orm.client);
await repo.find().whereLike("email", "%@example.com").execute(orm.client);

orderBy()

typescript
orderBy(column: string, direction: "ASC" | "DESC" = "ASC"): QueryBuilder<T>
typescript
await repo.find().orderBy("createdAt", "DESC").execute(orm.client);

orderByRaw() / groupByRaw() / havingRaw()

typescript
orderByRaw(expression: string, direction?: "ASC" | "DESC"): QueryBuilder<T>
groupByRaw(expression: string): QueryBuilder<T>
havingRaw(condition: string, ...params: any[]): QueryBuilder<T>

For expressions rather than column names. Raw and plain clauses compose, and raw parameters are bound in the order they appear.

typescript
await repo.find()
  .select("status", "COUNT(*) AS total")
  .groupByRaw("strftime('%Y-%m', createdAt)")
  .havingRaw("COUNT(*) > ?", 10)
  .orderByRaw("CASE WHEN status = 'urgent' THEN 0 ELSE 1 END")
  .execute(orm.client);

withRelations()

typescript
withRelations(...relations: (string | string[])[]): QueryBuilder<T>
getRelations(): string[]

Eager-loads relations onto the result, as findOne(id, { relations }) does. Nested paths use dot notation, and the loaded rows are cached with their relations.

typescript
const users = await repo.find()
  .where("isActive = ?", true)
  .withRelations("roles", "roles.permissions")
  .limit(10)
  .execute(orm.client);

join() / innerJoin() / leftJoin()

typescript
join(table: string, condition: string): QueryBuilder<T>
innerJoin(table: string, condition: string): QueryBuilder<T>
leftJoin(table: string, condition: string): QueryBuilder<T>
typescript
await repo.find().join("users", "posts.authorId = users.id").execute(orm.client);

groupBy() / having()

typescript
groupBy(...columns: string[]): QueryBuilder<T>
having(condition: string, ...params: any[]): QueryBuilder<T>
typescript
await repo.find()
  .select("categoryId", "COUNT(*) as cnt")
  .groupBy("categoryId")
  .having("COUNT(*) > ?", 5)
  .execute(orm.client);

limit() / offset() / paginate()

typescript
limit(limit: number): QueryBuilder<T>
offset(offset: number): QueryBuilder<T>
take(count: number): QueryBuilder<T>    // alias of limit
skip(count: number): QueryBuilder<T>    // alias of offset
first(): QueryBuilder<T>                // limit(1), returns the builder
paginate(page: number, pageSize: number): QueryBuilder<T>
typescript
await repo.find().limit(10).offset(20).execute(orm.client);
await repo.find().paginate(1, 20).execute(orm.client);

scope()

typescript
scope(name: string, ...args: any[]): QueryBuilder<T>
typescript
await repo.find().scope("active").scope("byRole", "admin").execute(orm.client);

Raw & Existence

typescript
whereRaw(rawSql: string, ...params: any[]): QueryBuilder<T>
whereRef(leftCol: string, op: string, rightCol: string): QueryBuilder<T>
whereNot(condition: string, ...params: any[]): QueryBuilder<T>
whereExists(builderOrSql: string | QueryBuilder<any>): QueryBuilder<T>
whereNotExists(builderOrSql: string | QueryBuilder<any>): QueryBuilder<T>

whereRef() compares two columns instead of a column and a bound value. whereExists() accepts a nested builder, which is rendered for the dialect the builder was given.

typescript
await repo.find()
  .whereRef("posts.authorId", "=", "users.id")
  .execute(orm.client);

Aggregates & Select

typescript
select(...fields: string[]): QueryBuilder<T>
selectRaw(expression: string, ...params: any[]): QueryBuilder<T>
distinct(): QueryBuilder<T>
count(column: string = "*", alias: string = "count"): QueryBuilder<T>
sum(column: string, alias: string = "sum"): QueryBuilder<T>
avg(column: string, alias: string = "avg"): QueryBuilder<T>
min(column: string, alias: string = "min"): QueryBuilder<T>
max(column: string, alias: string = "max"): QueryBuilder<T>

The aggregate helpers add a column to the SELECT list; they do not execute anything. Read the value off the returned row.

typescript
const [row] = await repo.find().count("id", "total").execute(orm.client);
// row.total


await repo.find().selectRaw("COALESCE(SUM(amount), 0) AS revenue").execute(orm.client);

Joins, Locking & Set Operations

typescript
fullJoin(table: string, condition: string): QueryBuilder<T>
crossJoin(table: string): QueryBuilder<T>


lock(mode: LockMode = "FOR UPDATE"): QueryBuilder<T>
forUpdate(): QueryBuilder<T>
forShare(): QueryBuilder<T>


union(builder: QueryBuilder<any>): QueryBuilder<T>
unionAll(builder: QueryBuilder<any>): QueryBuilder<T>


with(name: string, builder: QueryBuilder<any>): QueryBuilder<T>
withRecursive(name: string, builder: QueryBuilder<any>): QueryBuilder<T>

lock() is emitted only for Postgres and MySQL; the repository's lockForUpdate() skips it elsewhere. with() and withRecursive() build CTEs.

typescript
const sub = new QueryBuilder("posts").select("authorId").where("published = ?", true);


await repo.find().union(sub).execute(orm.client);
await repo.find().forShare().execute(orm.client);

Dialect, Aliasing & Cloning

typescript
withDialect(dialect: DBType): QueryBuilder<T>
as(alias: string): QueryBuilder<T>
clone(): QueryBuilder<T>
build(dialect?: DBType): { query: string; params: any[] }
toSQL(dialect?: DBType): { query: string; params: any[] }

clone() copies the builder so it can be reused —each() and eachBatch() rely on this to page a query without mutating it. toSQL() is an alias of build().

typescript
const base = repo.find().where("isActive = ?", true);


const { query, params } = base.clone().orderBy("name").toSQL(DBType.Postgres);

build() / execute()

typescript
build(dialect?: DBType): { query: string; params: any[] }
async execute(client: DBClient, cache?: Cache, cacheKey?: string): Promise<T[]>
async countExec(client: DBClient): Promise<number>
async existsExec(client: DBClient): Promise<boolean>

execute() stamps the dialect from the client. It applies the row transform before writing to the cache and loads relations before the cache write, so a cache hit returns the same shape as a fresh read. Reads taken inside a transaction are never cached. countExec() and existsExec() are what the repository's count() and exists() call.

typescript
const { query, params } = repo.find().where("id = ?", 1).build();
const results = await repo.find().where("id = ?", 1).execute(orm.client);