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​

  • 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();

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​

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('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​

  • 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');

Annotations​

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

Select & Raw Select​

For models, only annotated columns are available and will be added to the $annotations property of the model. Everything else will be ignored, if you need a query like selectRaw you can use the QueryBuilder instead.

  • select, selectRaw, clearSelect
await User.query().select('name', 'email').many();
await User.query().selectRaw('count(*) as count').first(); // will be ignored and `$additional` will be empty
await User.query().clearSelect().many();

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, withRecursive, withMaterialized
await User.query().with('users_cte', (qb) => qb.select('name')).many();

Relation Loading (ModelQueryBuilder only)​

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

Relation Existence Filters (havingRelated / notHavingRelated)​

  • havingRelated, orHavingRelated, notHavingRelated, orNotHavingRelated

Check whether a relation exists for the current model rows. You can pass either:

  • a callback to add filters on the related query, or
  • an operator/value pair to add a COUNT-based HAVING on the related rows.

By default, when no callback/operator/value is provided, the filter only checks that at least one related row exists.

// Base case: users that have at least one post
await User.query().havingRelated('posts').many();

// With a callback: users that have at least one published post
await User.query().havingRelated('posts', (qb) => {
  qb.where('published', true);
}).many();

// With operator/value: users that have more than 5 posts
await User.query().havingRelated('posts', '>', 5).many();

// Negative variants
await User.query().notHavingRelated('posts').many(); // users with no posts
await User.query().orNotHavingRelated('posts', (qb) => qb.where('published', true)).many();

Supported relation types: hasOne, belongsTo, hasMany, and manyToMany.

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, load, clearRelations

Next: QueryBuilder (Raw SQL)