QueryBuilder API Reference

QueryBuilder is returned by Model.query(). All filter and shape methods are chainable. Terminal methods (get, first, count, etc.) execute the query against D1 and return a Promise.

const users = await User.query()
  .whereEq('status', 'active')
  .orderBy('created_at', 'DESC')
  .limit(25)
  .get(env.DB)

Filter Methods

where(col, op, val)

.where(col: string, op: string, val: unknown): this

Adds a WHERE col op ? clause. op can be any SQL comparison operator (=, !=, <, >, <=, >=, LIKE, IS, IS NOT).

User.query().where('age', '>', 18)

---

orWhere(col, op, val)

.orWhere(col: string, op: string, val: unknown): this

Adds an OR col op ? clause.

User.query().whereEq('role', 'admin').orWhere('role', '=', 'moderator')

---

whereEq(col, val)

.whereEq(col: string, val: unknown): this

Shorthand for .where(col, '=', val).

User.query().whereEq('email', 'alice@example.com')

---

whereLike(col, pattern)

.whereLike(col: string, pattern: string): this

Adds a WHERE col LIKE ? clause. Use % as the wildcard character.

User.query().whereLike('name', 'Ali%')

---

whereIn(col, vals)

.whereIn(col: string, vals: unknown[]): this

Adds a WHERE col IN (?, ?, ...) clause. The binding list is constructed from the provided array.

User.query().whereIn('status', ['active', 'pending'])

---

whereRaw(sql, bindings?)

.whereRaw(sql: string, bindings?: unknown[]): this

Appends a raw SQL fragment to the WHERE clause. Use ? placeholders for values; pass the corresponding values as bindings.

User.query().whereRaw('json_extract(metadata, "$.tier") = ?', ['pro'])

---

whereGroup(fn)

.whereGroup(fn: (qb: QueryBuilder<TModel>) => void): this

Groups a set of conditions in parentheses. Pass a callback that calls filter methods on the inner qb.

User.query()
  .whereGroup(qb => {
    qb.whereEq('status', 'active').orWhere('role', '=', 'admin')
  })
  .get(env.DB)
// WHERE (status = ? OR role = ?)

---

orWhereGroup(fn)

.orWhereGroup(fn: (qb: QueryBuilder<TModel>) => void): this

Same as whereGroup but connects the group with OR instead of AND.

User.query()
  .whereEq('role', 'admin')
  .orWhereGroup(qb => {
    qb.whereEq('role', 'editor').whereEq('status', 'active')
  })
  .get(env.DB)
// WHERE role = ? OR (role = ? AND status = ?)

Shape Methods

select(cols)

.select(cols: string[]): this

Limits the columns included in the SELECT. By default all columns are selected (*).

User.query().select(['id', 'name', 'email'])

---

join(table, col1, op, col2)

.join(table: string, col1: string, op: string, col2: string): this

Adds an INNER JOIN clause.

Post.query()
  .join('users', 'posts.user_id', '=', 'users.id')
  .select(['posts.*', 'users.name as author_name'])

---

leftJoin(table, col1, op, col2)

.leftJoin(table: string, col1: string, op: string, col2: string): this

Adds a LEFT JOIN clause.

Post.query()
  .leftJoin('comments', 'posts.id', '=', 'comments.post_id')

---

groupBy(col)

.groupBy(col: string): this

Adds a GROUP BY col clause. Typically combined with an aggregate terminal method.

Post.query()
  .select(['user_id'])
  .groupBy('user_id')
  .count(env.DB)

---

orderBy(col, dir?)

.orderBy(col: string, dir?: 'ASC' | 'DESC'): this

Adds an ORDER BY col dir clause. Defaults to ASC when dir is omitted.

Post.query().orderBy('created_at', 'DESC')

---

limit(n)

.limit(n: number): this

Adds a LIMIT n clause.

Post.query().limit(20)

---

offset(n)

.offset(n: number): this

Adds an OFFSET n clause. Use with limit for pagination.

Post.query().orderBy('created_at', 'DESC').limit(20).offset(40)

Soft Delete Scopes

These methods apply only when the model has softDeletes = true. By default, queries on a soft-deleting model automatically append WHERE deleted_at IS NULL, excluding soft-deleted rows.

withTrashed()

.withTrashed(): this

Removes the deleted_at IS NULL filter so soft-deleted rows are included alongside normal rows.

Post.query().withTrashed().whereEq('user_id', userId).get(env.DB)

---

onlyTrashed()

.onlyTrashed(): this

Restricts results to soft-deleted rows only (WHERE deleted_at IS NOT NULL).

Post.query().onlyTrashed().get(env.DB)

Eager Loading

with(relations)

.with(relations: string[]): this

Eager-loads the named relations after the primary query executes. Each name must match a key in the model's static eagerLoaders map. Throws a runtime error naming any unknown relation.

// Model definition
class Post extends BaseModel<PostAttrs> {
  static table = 'posts'
  static eagerLoaders = {
    comments: (post: Post) => hasMany(Comment, 'post_id').on(post),
    author:   (post: Post) => belongsTo(User, 'user_id').on(post),
  }
}

// Query
const posts = await Post.query()
  .with(['comments', 'author'])
  .limit(10)
  .get(env.DB)

// posts[0].relations.comments  → Comment[]
// posts[0].relations.author    → User | null

See BaseModel.eagerLoaders for eager loading configuration.

Terminal Methods

Terminal methods execute the query and return a Promise. They accept db: D1Database as their only parameter (unless noted).

get(db)

.get(db: D1Database): Promise<TModel[]>

Executes the query and returns all matching rows as model instances.

const posts = await Post.query().whereEq('user_id', userId).get(env.DB)

---

first(db)

.first(db: D1Database): Promise<TModel | null>

Executes the query with an implicit LIMIT 1 and returns the first row, or null if no rows match.

const post = await Post.query().whereEq('slug', slug).first(env.DB)

---

count(db)

.count(db: D1Database): Promise<number>

Executes SELECT COUNT(*) FROM ... and returns the count as a number.

const total = await Post.query().whereEq('user_id', userId).count(env.DB)

---

sum(db, col)

.sum(db: D1Database, col: string): Promise<number>

Executes SELECT SUM(col) FROM ... and returns the sum.

const totalViews = await Post.query().sum(env.DB, 'view_count')

---

avg(db, col)

.avg(db: D1Database, col: string): Promise<number>

Executes SELECT AVG(col) FROM ... and returns the average.

const avgScore = await Post.query().whereEq('user_id', userId).avg(env.DB, 'score')

---

min(db, col)

.min(db: D1Database, col: string): Promise<number>

Executes SELECT MIN(col) FROM ... and returns the minimum value.

const earliest = await Post.query().min(env.DB, 'created_at')

---

max(db, col)

.max(db: D1Database, col: string): Promise<number>

Executes SELECT MAX(col) FROM ... and returns the maximum value.

const latest = await Post.query().max(env.DB, 'created_at')