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

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

Best Practice: Use ModelQueryBuilder for all application logic and model-based queries.

Example Usage​

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

Filtering​

const users = await User.query().where('age', '>', 18).andWhere('isActive', true).many();

Selecting Columns​

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

Pagination​

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

Joins​

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

Annotations​

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

CTEs (Common Table Expressions)​

const users = await User.query()
  .with('active_users', (cte) =>
    cte.newCte('users_cte', (b) => b.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​

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

Comparison to QueryBuilder​

  • Type Safety: ModelQueryBuilder is fully typed, QueryBuilder is not.
  • Decorator/Relation Support: Only ModelQueryBuilder supports model decorators and relations.
  • Use Case: Use ModelQueryBuilder for all model logic; use QueryBuilder for raw SQL.

Full API Reference​

Filtering​

  • where, orWhere, andWhere, whereIn, whereNotIn, whereNull, whereNotNull, whereBetween, whereNot, whereLike, whereNotLike, andWhereLike, andWhereNotLike, orWhereLike, orWhereNotLike, whereExists, whereNotExists
await User.query().where('email', 'like', '%@example.com').many();
await User.query().whereIn('status', ['active', 'pending']).many();
await User.query().whereNull('deletedAt').many();
await User.query().whereBetween('age', [18, 30]).many();

Subqueries & Nested Conditions​

  • whereSubQuery, andWhereSubQuery, orWhereSubQuery, whereBuilder, andWhereBuilder, orWhereBuilder
await User.query().whereSubQuery('id', (sub) => sub.select('userId').from('posts').where('published', true)).many();
await User.query().whereBuilder((qb) => qb.where('age', '>', 18).orWhere('isActive', true)).many();

Joins​

  • join, leftJoin, rightJoin, innerJoin (by table name or model)
await Post.query().join('users', 'posts.userId', 'users.id').many();
await Post.query().leftJoin(User, 'id', 'userId').many();

Group By & Having​

  • groupBy, having
await User.query().groupBy('status').having('COUNT(*)', '>', 1).many();

Unions​

  • union, unionAll
await User.query().select('name').union((qb) => qb.select('authorName')).many();

Aggregates​

  • getCount, getMax, getMin, getAvg, getSum
const count = await User.query().getCount();
const maxAge = await User.query().getMax('age');

Select & Raw Select​

  • select, selectRaw, clearSelect
await User.query().select('name', 'email').many();
await User.query().selectRaw('count(*) as count').first();
await User.query().clearSelect().many();

Annotations​

  • annotate, removeAnnotations, clearRemoveAnnotations
await User.query().annotate('COUNT(*)', 'count').first();
await User.query().removeAnnotations().first();

Pluck​

  • pluck
const names = await User.query().pluck('name');

Pagination​

  • paginate, limit, offset
const page = await User.query().paginate(1, 10);
await User.query().limit(5).offset(10).many();

Locking​

  • lockForUpdate, forShare
await User.query().lockForUpdate().many();
await User.query().forShare().many();

CTEs (Common Table Expressions)​

  • with
await User.query().with('normal', (cte) => cte.newCte('users_cte', (b) => b.select('name'))).many();

Relation Loading (ModelQueryBuilder only)​

  • withRelation, clearRelations
await User.query().withRelation('posts').many();
await User.query().withRelation('addresses', Address, (qb) => qb.withRelation('users')).many();

Copying & Query Output​

  • copy, toQuery, unWrap
const qb = User.query().where('isActive', true);
const qbCopy = qb.copy();
const sql = qb.toQuery();

ModelQueryBuilder Only​

  • removeAnnotations, clearRemoveAnnotations, withRelation, clearRelations

Next: QueryBuilder (Raw SQL)