Query Builder

Build complex queries with a fluent, chainable API.
The QueryBuilder provides a type-safe, database-agnostic interface for constructing SQL SELECT queries programmatically.

Basic Usage

example/basic-query.tstypescript

const userRepository = stabilize.getRepository(User);

// Find all users (SELECT * FROM users)
const allUsers = await userRepository.find().execute();

// Select specific columns
const users = await userRepository
  .find()
  .select("id", "name", "email")
  .where("isActive = ?", true)
  .orderBy("createdAt DESC")
  .limit(10)
  .execute(dbClient);

Where Clauses

example/where-query.tstypescript

// Add a single where clause
.find().where("age > ?", 18)

// Multiple conditions (AND)
.find()
.where("age > ?", 18)
.where("isActive = ?", true)

// All conditions are joined with AND
// WHERE age > ? AND isActive = ?

Joins

example/join-query.tstypescript

// LEFT JOIN example
const usersWithPosts = await userRepository
  .find()
  .join("posts", "posts.userId = users.id")
  .select("users.id", "users.name", "posts.title")
  .execute();

Ordering, Limiting & Offsetting

example/order-limit-offset.tstypescript

const page = 2;
const perPage = 10;

const users = await userRepository
  .find()
  .orderBy("lastName ASC")
  .limit(perPage)
  .offset((page - 1) * perPage)
  .execute();

Scopes

Apply reusable query fragments using scopes defined on your model:

example/scope.tstypescript

// Assuming a "active" scope is defined on User model
const activeUsers = await userRepository
  .find()
  .scope("active")
  .limit(10)
  .execute();

Caching

Use built-in cache-aside logic to speed up repeated queries:

example/cache.tstypescript

const users = await userRepository
  .find()
  .where("isActive = ?", true)
  .limit(10)
  .execute(dbClient, cache, "active_users_page_1"); // cacheKey is optional

Generated SQL Example

example/built-query.tstypescript

const qb = userRepository
  .find()
  .select("id", "email")
  .where("isActive = ?", true)
  .orderBy("createdAt DESC")
  .limit(5);

const { query, params } = qb.build();
// query: SELECT id, email FROM users WHERE isActive = ? ORDER BY createdAt DESC LIMIT 5
// params: [true]

Full QueryBuilder API

query-builder.tstypescript

queryBuilder
  .select(...fields: string[])
  .where(condition: string, ...params: any[])
  .join(table: string, condition: string)
  .orderBy(clause: string)
  .limit(limit: number)
  .offset(offset: number)
  .scope(name: string, ...args: any[])
  .build()
  .execute(client: DBClient, cache?: Cache, cacheKey?: string): Promise<T[]>