Skip to main content

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​

  • 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.

// After connecting with SqlDataSource
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​

Use annotate to add computed columns or SQL functions. Annotated values appear under $annotations on results.

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

When using annotate alone without select, model fields are omitted. Add select('*') to include model columns alongside annotations.

For more details, see Model Annotations.

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)