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 using the load method.

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

By default, queries return all columns from the model's table. Use select() to pick specific columns:

// Only returns name and email
const users = await User.query()
  .select('name', 'email')
  .many();

// TypeScript knows only name and email are available
users[0].name;  // ✓ OK
users[0].email; // ✓ OK
users[0].age;   // ✗ Type error - not selected

warning

The select() method only allows selecting columns that are part of the model definition. For columns outside the model (e.g., computed columns, columns from unrelated tables, or raw expressions), use selectRaw() instead:

// ❌ Error - 'metadata' is not a column in the User model
await User.query().select('metadata').many();

// ✅ Correct - use selectRaw for non-model columns
await User.query().selectRaw<{ metadata: string }>('metadata').many();

Column Selection Formats

// Direct column names with intellisense
const users = await User.query()
  .select('name', 'age', 'email')
  .many();

// Qualified columns (table.column) - useful with JOINs
const posts = await Post.query()
  .select('posts.title', 'posts.content')
  .leftJoin('users', 'users.id', 'posts.userId')
  .many();

// Column aliases using tuple syntax [column, alias]
const users = await User.query()
  .select(['name', 'userName'], ['age', 'userAge'])
  .many();

users[0].userName; // ✓ OK (string - type from name)
users[0].userAge;  // ✓ OK (number - type from age)

// Wildcards - returns all model columns
const users = await User.query().select('*').many();

Avoid Using Wildcards

Using * or table.* in select() is discouraged as it loses type safety benefits:

// ❌ Avoid: Loses type inference for other selections
const user = await User.query()
  .select("*", ["name", "aliasedName"])  // aliasedName won't be typed!
  .one();

// ✅ Preferred: Select specific columns for full type safety
const user = await User.query()
  .select("id", "name", "email", ["name", "aliasedName"])
  .one();

user.aliasedName; // ✓ Properly typed as string

When * is present, the return type becomes ModelWithoutRelations<T>, and any additional aliased columns from tuples will not be included in the type. Always prefer selecting specific columns for maximum type safety.

Wildcards and JOINs

When using wildcards with JOINs, columns from joined tables are automatically filtered out to prevent data bleeding:

// users columns are filtered out, only Post columns appear
const posts = await Post.query()
  .select('posts.*')
  .leftJoin('users', 'users.id', 'posts.userId')
  .many();

posts[0].title;   // ✓ OK (Post column)
posts[0].name;    // ✗ Filtered out (User column)

// To include columns from joined tables, use explicit aliases
const posts = await Post.query()
  .select('posts.*', ['users.name', 'authorName'])
  .leftJoin('users', 'users.id', 'posts.userId')
  .many();

posts[0].title;      // ✓ OK (Post column)
posts[0].authorName; // ✓ OK (explicitly aliased)

Chaining Selects

Multiple select() calls accumulate at runtime:

const users = await User.query()
  .select('name')
  .select('age')  // Both name and age are selected
  .many(); // returns { name: string; age: number }

Note: TypeScript reflects the type of the last select() call. If you need all columns typed, include them in a single select() call.

Type Safety in Select

The select() method is fully type-safe. TypeScript infers the return type based on which columns you select.

Basic Type Inference

// Model definition
class User extends Model {
  @column({ primaryKey: true })
  declare id: number;

  @column()
  declare name: string;

  @column()
  declare email: string;

  @column()
  declare age: number;
}

// Selecting specific columns - return type is { name: string; email: string }
const user = await User.query()
  .select('name', 'email')
  .one();

user?.name;   // ✓ string
user?.email;  // ✓ string
user?.age;    // ✗ Type error - property 'age' does not exist
user?.id;     // ✗ Type error - property 'id' does not exist

Alias Type Inference

When using [column, alias] tuple syntax, TypeScript creates the aliased property with the original column's type:

const user = await User.query()
  .select(['name', 'userName'], ['age', 'userAge'])
  .one();

user?.userName;  // ✓ string (type from 'name')
user?.userAge;   // ✓ number (type from 'age')
user?.name;      // ✗ Type error - use the alias instead

Wildcard Behavior

Using * returns the full model type:

// Returns ModelWithoutRelations<User> (all columns)
const users = await User.query()
  .select('*')
  .many();

users[0].id;     // ✓ number
users[0].name;   // ✓ string
users[0].email;  // ✓ string
users[0].age;    // ✓ number

Qualified Columns

Table-qualified columns extract the column name for typing:

const result = await User.query()
  .select('users.name', 'users.age')
  .many();

result[0].name;  // ✓ string (extracted from 'users.name')
result[0].age;   // ✓ number (extracted from 'users.age')

Custom Properties with selectRaw

For custom expressions, use the type parameter to define the return shape:

const result = await User.query()
  .select('name')
  .selectRaw<{ totalOrders: number }>('COUNT(orders.id) as totalOrders')
  .one();

result?.name;         // ✓ string (from select)
result?.totalOrders;  // ✓ number (from selectRaw type parameter)

Type Safety Summary

Selection	Return Type
No select()	ModelWithoutRelations<T> (all columns)
select('*')	ModelWithoutRelations<T> (all columns)
select('col1', 'col2')	{ col1: Type1; col2: Type2 }
select(['col', 'alias'])	{ alias: ColType }
select('table.col')	{ col: ColType }
selectRaw<T>(...)	Merges T with current selection

Instance Methods on Query Results

All query results from the ModelQueryBuilder include instance methods (save, update, delete, softDelete, refresh, mergeProps) regardless of whether you use .select() or not:

// Without select - full model with instance methods
const user = await User.query().where('id', 1).one();
if (user) {
  await user.update({ name: "New Name" });
  await user.refresh();
  await user.delete();
}

// With select - partial data but still has instance methods
const user = await User.query()
  .select('id', 'name')
  .where('id', 1)
  .one();

if (user) {
  user.mergeProps({ name: "Updated" });
  await user.save();     // ✓ Available
  await user.update({ name: "Another" }); // ✓ Available
  await user.delete();   // ✓ Available
  await user.refresh();  // ✓ Available
}

note

Instance methods require the primary key to be present in the result. If you use .select() without including the primary key, methods like save(), update(), and delete() will throw an error.

SQL Functions with selectFunc

Hysteria ORM provides a unified selectFunc() method for SQL functions with auto-inferred return types:

// Return types are automatically inferred!
const result = await User.query()
  .selectFunc("count", "*", "totalUsers")  // totalUsers: number
  .selectFunc("avg", "age", "avgAge")      // avgAge: number
  .selectFunc("upper", "name", "upperName") // upperName: string
  .one();

result?.totalUsers; // number
result?.avgAge;     // number
result?.upperName;  // string

Supported Functions

Functions	Return Type
count, sum, avg, min, max, length, abs, round, ceil, floor, sqrt	number
upper, lower, trim	string
Custom functions	any

Basic Usage

// COUNT all rows
const result = await User.query()
  .selectFunc("count", "*", "totalUsers")
  .one();

console.log(result?.totalUsers); // number

// SUM
const result = await Order.query()
  .selectFunc("sum", "amount", "totalRevenue")
  .one();

// AVG
const result = await Product.query()
  .selectFunc("avg", "price", "averagePrice")
  .one();

// MIN / MAX
const result = await User.query()
  .selectFunc("min", "age", "youngestAge")
  .selectFunc("max", "age", "oldestAge")
  .one();

Combining Aggregates

const stats = await User.query()
  .selectFunc("count", "*", "totalUsers")
  .selectFunc("min", "age", "minAge")
  .selectFunc("max", "age", "maxAge")
  .selectFunc("avg", "age", "avgAge")
  .one();

// { totalUsers: 100, minAge: 18, maxAge: 85, avgAge: 35.5 }

With GROUP BY

const salesByCategory = await Product.query()
  .select("category")
  .selectFunc("sum", "price", "totalSales")
  .selectFunc("count", "*", "productCount")
  .groupBy("category")
  .orderBy("totalSales", "desc")
  .many();

Alternative: getCount, getSum, etc.

For simple cases where you just need a single aggregate value:

// Using getCount (returns just the number)
const count = await User.query()
  .where("status", "active")
  .getCount();
// Returns: 42

// Equivalent using selectFunc (returns object with property)
const result = await User.query()
  .where("status", "active")
  .selectFunc("count", "*", "count")
  .one();
// Returns: { count: 42 }

For more SQL functions (string, numeric, null handling) and selectRaw, see: SQL Functions

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

Basic Joins

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

Joins with Additional Conditions

You can add additional conditions to the join ON clause by passing a callback as the last parameter:

const postsWithActiveUsers = await Post.query()
  .select('posts.*')
  .join(
    'users',
    'users.id',
    'posts.userId',
    (q) => q.where('users.isActive', true)
  )
  .many();

// This generates SQL similar to:
// SELECT posts.* FROM posts
// INNER JOIN users ON users.id = posts.userId AND users.isActive = true

The callback receives a JoinOnQueryBuilder that supports all where methods:

// Multiple conditions
await Post.query()
  .join(
    'users',
    'users.id',
    'posts.userId',
    (q) => q
      .where('users.isActive', true)
      .andWhere('users.verified', true)
  )
  .many();

// Using different operators
await Post.query()
  .innerJoin(
    User,
    'id',
    'userId',
    (q) => q
      .whereIn('users.status', ['active', 'pending'])
      .andWhere('users.age', '>=', 18)
  )
  .many();

// Works with all join types: join, innerJoin, leftJoin, rightJoin, fullJoin
await Post.query()
  .leftJoin(
    'comments',
    'comments.postId',
    'posts.id',
    (q) => q.where('comments.approved', true)
  )
  .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, selectFunc
• Select: select, selectRaw, selectFunc, clearSelect
• Pagination: paginate, paginateWithCursor, limit, offset, chunk
• Relations: load, clearRelations, havingRelated, notHavingRelated

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

---

Next: QueryBuilder (Raw SQL)