Query Builder API

Fluent API for building complex database queries

QueryBuilder Class

query-builder.tstypescript

export class QueryBuilder<T> {
  private table: string;
  private selectFields: string[] = ["*"];
  private joins: string[] = [];
  private whereConditions: string[] = [];
  private whereParams: any[] = [];
  private orderByClause: string | null = null;
  private limitValue: number | null = null;
  private offsetValue: number | null = null;
  // ...methods...
}

select()

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

Specifies the columns to select. Defaults to *.

const users = await userRepository
  .find()
  .select("id", "name", "email")
  .where("isActive = ?", true)
  .execute(client);

where()

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

Adds a WHERE clause to the query. Multiple calls are joined with AND.

Parameters:

conditionSQL condition with placeholders (?)
paramsValues to replace placeholders

const users = await userRepository
  .find()
  .where("age > ?", 18)
  .where("isActive = ?", true)
  .execute(client);

orWhere()

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

Adds an OR WHERE clause to the query.

const users = await userRepository
  .find()
  .where("role = ?", "admin")
  .orWhere("role = ?", "moderator")
  .execute(client);

whereIn()

whereIn(column: string, values: any[]): QueryBuilder<T>

Adds a WHERE IN clause.

const users = await userRepository
  .find()
  .whereIn("status", ["active", "pending", "verified"])
  .execute(client);

whereLike()

whereLike(column: string, pattern: string): QueryBuilder<T>

Adds a WHERE LIKE clause for pattern matching.

const users = await userRepository
  .find()
  .whereLike("email", "%@example.com")
  .execute(client);

whereBetween()

whereBetween(column: string, min: any, max: any): QueryBuilder<T>

Adds a WHERE BETWEEN clause.

const users = await userRepository
  .find()
  .whereBetween("age", 18, 65)
  .execute(client);

orderBy()

orderBy(clause: string): QueryBuilder<T>

Adds an ORDER BY clause (pass e.g. "createdAt DESC").

const users = await userRepository
  .find()
  .orderBy("createdAt DESC")
  .orderBy("name ASC")
  .execute(client);

limit() & offset()

limit(count: number): QueryBuilder<T>
offset(count: number): QueryBuilder<T>

Limits the number of results and sets the offset for pagination.

const users = await userRepository
  .find()
  .limit(10)
  .offset(20)
  .execute(client);

join()

join(table: string, condition: string): QueryBuilder<T>

Adds a LEFT JOIN clause.

const users = await userRepository
  .find()
  .join("posts", "users.id = posts.userId")
  .execute(client);

scope()

scope(name: string, ...args: any[]): QueryBuilder<T>

Applies a named scope defined on your model.

const users = await userRepository
  .find()
  .scope("active")
  .execute(client);

build()

build(): { query: string; params: any[] }

Returns the final SQL query string and parameters before execution.

const qb = userRepository.find().select("id", "email").where("isActive = ?", true);
const { query, params } = qb.build();
// query: SELECT id, email FROM users WHERE isActive = ?
// params: [true]

execute()

async execute(client: DBClient, cache?: Cache, cacheKey?: string): Promise<T[]>

Executes the query using the provided DBClient. Optionally supports cache.

const users = await userRepository
  .find()
  .where("isActive = ?", true)
  .limit(10)
  .execute(client, cache, "active_users_page_1");