Query Builder Basics

Hysteria ORM provides two main query APIs:

• QueryBuilder: A low-level, Knex-like builder for raw SQL queries. Not type-safe, works with any table.
• ModelQueryBuilder: A high-level, type-safe builder for model-based queries. All model .query() calls return a ModelQueryBuilder.

Best Practice: Use Model.query() for type safety and rich features. Use sql.query('table') for raw, flexible queries.

Basic Usage

// Type-safe (ModelQueryBuilder)
const users = await User.query().where('status', 'active').many();

// Not type-safe (QueryBuilder)
const users = await sql.query('users').where('status', 'active').many();

Selecting Columns

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

Filtering

const users = await User.query().where('age', '>', 18).andWhere('isActive', true).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();

Advanced Features

annotate (Column Aliasing)

Add computed columns or aliases to your result set.

const userCount = await User.query()
  .annotate('count', 'id', 'total') // with sql method
  .first();

const user = await User.query()
  .annotate('id', 'userId') // only alias
  .first();

console.log(userCount.$annotations.total);
console.log(userCount.$annotations.userId);

removeAnnotations

Remove all annotations from the result.

const user = await User.query()
  .annotate('count', 'id', 'total')
  .removeAnnotations()
  .first();

console.log(user.$annotations); // undefined

pluck

Extract a single column as an array.

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

increment / decrement

Atomically increment or decrement a column.

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

lockForUpdate / forShare

Apply row-level locking (Postgres/MySQL only).

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

with (Common Table Expressions, CTE)

Use CTEs for advanced queries.

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

clearSelect

Reset selected columns to default (all columns).

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

orderBy

Sort results by a column.

const users = await User.query().orderBy('name', 'asc').many();

truncate

Delete all rows from a table.

await User.query().truncate();

softDelete

Mark rows as deleted by setting a column (e.g., deletedAt).

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

ModelQueryBuilder vs QueryBuilder

Feature	ModelQueryBuilder	QueryBuilder (sql.query)
Type Safety	✅ Yes	❌ No
Model Awareness	✅ Yes	❌ No
Decorator Support	✅ Yes	❌ No
Raw SQL Flexibility	⚠️ Limited	✅ Full
Use Case	App logic, relations	Raw SQL, migrations, performance critical queries

---

Next: Model Query Builder