ModelQueryBuilder (Type-Safe)

The ModelQueryBuilder is the primary, partially type-safe query API for Hysteria ORM models. All calls to User.query(), Post.query(), etc., return a ModelQueryBuilder instance. The query builder is partially type safe, triggering intellisense for developer experience but still allowing the developer to write whatever he wants. The ModelQueryBuilder by default returns a ModelWithoutRelations<T> type, which is a type that does not have any relations. You can add Relations or Annotations using the load and annotate methods.

Key Features

• Fully partially type-safe queries and results
• Decorator and relation support
• Rich API for filtering, selecting, joining, and more
• Supports advanced features like CTEs, annotations, pagination, and soft deletes
• Model columns are parsed to database columns based on the case convention of the model, this applies both on column names like isActive and on table.column scenarios like users.isActive

Example Usage

const users = await User.query().where('status', 'active').many();

Filtering

// Here if model has snake case convention, the columns will be parsed to snake case
const users = await User.query().where('age', '>', 18).andWhere('isActive', true).andWhere('users.isActive', true).many();

Raw right-hand side with rawStatement

When you need to compare a column to another column or an expression without creating a binding, use sql.rawStatement. Identifiers inside the raw string are automatically quoted per database dialect.

import { sql } from 'hysteria-orm';

await User.query()
  .where('id', sql.rawStatement('user.id'))
  .many();

Selecting Columns

• In ModelQueryBuilder, select now only accepts model keys or "*". Non-model or computed columns must be added using annotate.
• select can be stacked; the resulting TypeScript type reflects only the selected model columns. If no select is used, the full model type (without relations) is returned as before.

// OK: model columns
const user = await User.query().select('id', 'name').first();

// OK: all model columns
const userAll = await User.query().select('*').first();

// For non-model columns use annotate (see below)

Annotations

const user = await User.query()
  .annotate('count', '*', 'count') // count(*) as count
  .first();
console.log(user.$annotations.count);

Annotate rules and edge cases

• Use annotate to project non-model columns or SQL functions. Annotated values are exposed under $annotations on model results.
• When the builder has no explicit select (default select *), using annotate alone will not mix annotation fields into the model shape. Hysteria will keep model fields empty and only include $annotations for that query response; add select('*') if you also want model columns.

// Only annotation, no explicit select → model fields omitted, only $annotations present
const r1 = await User.query().annotate('count', '*', 'total').first(); // { $annotations: { total: number } }
console.log(r1.$annotations.total);

// Include full model by selecting '*'
const r2 = await User.query().select('*').annotate('count', '*', 'total').first();
console.log(r2.id, r2.$annotations.total);

Pagination

const page = await User.query().paginate(1, 10);
console.log(page.data, page.paginationMetadata);

Joins

• Joins do not use the joined table hooks, only the caller model hooks are used
• Joined model columns are cased based on the case convention of the model

const postsWithUsers = await Post.query()
  .select('posts.*', 'users.name')
  .join('users', 'posts.userId', 'users.id')
  .many();

const postsWithUsersFromModel = await Post.query()
  .select('posts.*', 'users.name')
  .join(User, 'userId', 'id')
  .many();

CTEs (Common Table Expressions)

const users = await User.query()
  .with('users_cte', (qb) => qb.select('name').where('isActive', true))
  .many();

Pluck

const names = await User.query().pluck('name'); // string[]

Increment / Decrement

await User.query().increment('age', 1);
await User.query().decrement('age', 1);

Locking

const users = await User.query().lockForUpdate().many();
const users = await User.query().forShare().many();

clearSelect

const users = await User.query().select('name').clearSelect().many();

Soft Delete

await User.query().softDelete({ column: 'deleted_at' });

Type Safety Example

// users: ModelWithoutRelations<User>
const users = await User.query().where('email', 'like', '%@example.com').many();

Comparison to QueryBuilder

For raw SQL reference (filtering, joins, pagination, streaming, chunking), see: QueryBuilder (Raw SQL)

Selected API Highlights

• Filtering: where, orWhere, andWhere, whereIn, whereNull, whereBetween, whereLike, whereExists
• Joins: join, leftJoin, rightJoin, innerJoin
• Aggregates: getCount, getMax, getMin, getAvg, getSum
• Pagination: paginate, paginateWithCursor, limit, offset, chunk
• Annotations: annotate, removeAnnotations, clearRemoveAnnotations
• Relations: load, clearRelations, havingRelated, notHavingRelated

Refer to the tests under test/sql for comprehensive examples aligned with the implementation.

---

Next: QueryBuilder (Raw SQL)