QueryBuilder (Raw SQL)

The QueryBuilder is a low-level, Knex-like API for building raw SQL queries. Access it via sql.query('table'). It's suitable for performance crucial database queries since there is no serialization and the driver output is directly returned from the query.

Key Features

• Works with any table (even without a model)
• Not type-safe (returns plain objects)
• Flexible for migrations, admin scripts, or advanced SQL
• Supports filtering, selecting, joining, pagination, CTEs, truncate, softDelete, and more

Best Practice: Use QueryBuilder for raw SQL, migrations, or when want the max performance in a query. For app logic, prefer the ModelQueryBuilder.

Basics

For type-safe application queries use the ModelQueryBuilder. This page focuses on raw SQL with sql.query(table).

Basic Usage

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

Pagination with Cursor

Paginate with cursor is a pagination method that allows you to paginate the results with a cursor that does not use the offset clause (more efficient for large datasets). Discriminator is used to identify the unique value to paginate by. By default it generates an order by clause to the query orderBy(discriminator, "asc") if not already present.

// Get the first page
const [users, cursor] = await User.query().paginateWithCursor(1, { discriminator: "age" });

// Get the second page
const [users2, cursor2] = await User.query().paginateWithCursor(1, { discriminator: "age" }, cursor);

Joins

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

// Alias are supported in `join` and `from`
const postsWithUsersWithAlias = await Post.query()
  .join('users as u', 'posts.userId', 'u.id')
  .select('posts.*', 'u.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); // error in ts

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('users_cte', (qb) => qb.select('name').where('isActive', true))
  .many();

Example Usage

import { sql } from "hysteria-orm";
const users = await sql.query("users").where("status", "active").many();

Filtering

const users = await sql.query("users").where("age", ">", 18).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 sql.query("users")
  .where("id", sql.rawStatement("user.id"))
  .many();

Selecting Columns

const names = await sql.query("users").select("name").many();

Pagination

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

Advanced Pagination

Hysteria ORM provides several methods for working with large datasets efficiently. See chunk, paginate and other helpers below.

Chunking Large Datasets

The chunk method is a powerful async generator that helps you process large datasets in manageable pieces without loading everything into memory at once:

import { User } from './models/user';

// Process all users in chunks of 250
async function processAllUsers() {
  for await (const users of User.query().chunk(250)) {
    // Each iteration gets a new batch of up to 250 users
    await processUserBatch(users);
    console.log(`Processed ${users.length} users`);
  }
}

You can also use chunk with the raw query builder:

import { sql } from 'hysteria-orm';

for await (const records of sql.query('large_table').chunk(1000)) {
  await someAsyncProcessingFunction(records);
}

When to Use Different Pagination Methods

Method	Use Case	Return Value
paginate(page, perPage)	API responses with page metadata	{ data: T[], paginationMetadata: {...} }
limit(n).offset(m)	Simple manual pagination	Array of results
chunk(size)	Processing large datasets	Async iterable of result arrays

Best Practices

1. Use chunk for background jobs, data migrations, or any large dataset processing
2. Use paginate for API responses where you need page numbers and metadata
3. Use limit/offset when you need manual control over pagination

From

const users = await sql
  .query("users")
  .from(
    (qb) => {
      qb.select("name").from("users as internal_users").where("internal_users.age", ">", 18);
    },
    "external_users"
  )
  .many();

Joins

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

CTEs (Common Table Expressions)

const users = await sql
  .query("users")
  .with("users_cte", (qb) => qb.select("name").where("isActive", true))
  .many();

Truncate

await sql.query("users").truncate();

Soft Delete

await sql.query("users").softDelete({ column: "deleted_at" });

Limitations

• No type safety: results are plain objects
• No decorator or relation support
• Use with caution in app logic

Comparison to ModelQueryBuilder

• Type Safety: QueryBuilder is not typed, ModelQueryBuilder is.
• Decorator/Relation Support: Only ModelQueryBuilder supports model decorators and relations.
• Use Case: Use QueryBuilder for raw SQL, migrations, or admin scripts.

Full API Reference

Filtering

• where, orWhere, andWhere, whereNot, andWhereNot, orWhereNot, whereIn, whereNotIn, whereNull, whereNotNull, whereBetween, whereLike, whereNotLike, andWhereLike, andWhereNotLike, orWhereLike, orWhereNotLike, whereExists, whereNotExists

await sql.query("users").where("email", "like", "%@example.com").many();
await sql.query("users").whereIn("status", ["active", "pending"]).many();
await sql.query("users").whereNull("deletedAt").many();
await sql.query("users").whereBetween("age", [18, 30]).many();
await sql.query("users").whereNot("name", "Alice").many();

Subqueries & Nested Conditions

• Overloads on where/andWhere/orWhere and whereIn/whereNotIn support callbacks and subqueries.

// Grouped conditions with a callback, this ignores everything that is not a where clause
await sql
  .query("users")
  .where((qb) => {
    qb.where("age", ">", 18).orWhere("isActive", true);
  })
  .many();

// Column compared to a subquery (defaults to IN)
await sql
  .query("users")
  .where("id", (sub) => sub.select("userId").from("posts").where("published", true))
  .many();

// Column compared to a subquery with explicit operator
await sql
  .query("users")
  .where("id", "not in", (sub) => sub.select("userId").from("posts"))
  .many();

// OR with subquery
await sql
  .query("users")
  .orWhere("id", (sub) => sub.select("ownerId").from("teams"))
  .many();

Joins

• join, leftJoin, rightJoin, innerJoin

await sql.query("posts").join("users", "posts.userId", "users.id").many();

Group By & Having

• groupBy, having

await sql.query("users").groupBy("status").having("COUNT(*)", ">", 1).many();

Unions

• union, unionAll

await sql
  .query("users")
  .select("name")
  .union("SELECT name FROM users")
  .many();

Aggregates

• getCount, getMax, getMin, getAvg, getSum

const count = await sql.query("users").getCount();
const maxAge = await sql.query("users").getMax("age");

Select & Raw Select

• select, selectRaw, clearSelect

await sql.query("users").select("name", "email").many();
await sql.query("users").selectRaw("count(*) as count").first();
await sql.query("users").clearSelect().many();

Annotations

• annotate, removeAnnotations, clearRemoveAnnotations

await sql.query("users").annotate("COUNT(*)", "count").first();
await sql.query("users").removeAnnotations().first();

Pluck

• pluck

const names = await sql.query("users").pluck("name");

Pagination

• paginate, limit, offset, chunk

const page = await sql.query("users").paginate(1, 10);
await sql.query("users").limit(5).offset(10).many();

// Process large datasets in chunks
for await (const users of sql.query("users").chunk(100)) {
  // Process each chunk of 100 users
  console.log(`Processing ${users.length} users...`);
}

Streaming (experimental)

• Allows you to process the query results as a stream not all at once.
• It does not run any hooks nor any serialization.

// Async generator for one-by-one processing
for await (const user of await sql.query("users").stream()) {
  console.log(`Processing user: ${user.name}`);
}

// Node.js Readable stream
const stream = await sql.query("users").stream();
stream.on('data', (user) => console.log(user));
stream.on('end', () => console.log('Finished processing all users'));
stream.on('error', (error) => console.error('Stream error:', error));

Chunking Large Datasets

The chunk method is an async generator that helps you process large datasets without loading everything into memory at once:

import { sql } from "hysteria-orm";

// Process large datasets efficiently with controlled memory usage
async function processAllUsers() {
  // Process users in chunks of 500
  for await (const users of sql.query("users").chunk(500)) {
    // Each iteration gets a new array of up to 500 users
    await processUserChunk(users);
  }
}

// The chunk method handles pagination automatically
// and stops when no more records are available

Locking

• lockForUpdate, forShare

await sql.query("users").lockForUpdate().many();
await sql.query("users").forShare().many();

CTEs (Common Table Expressions)

• with, withRecursive, withMaterialized

await sql
  .query("users")
  // Normal CTE
  .with("users_cte", (qb) => qb.select("name"))
  // Recursive CTE
  .withRecursive("users_cte", (qb) => qb.select("name"))
  // Materialized CTE (PostgreSQL/CockroachDB only)
  .withMaterialized("users_cte", (qb) => qb.select("name"))
  .many();

Copying & Query Output

• copy, toQuery, unWrap

const qb = sql.query("users").where("isActive", true);
const qbCopy = qb.clone();
const sqlString = qb.toQuery();

QueryBuilder Only

• All methods are available on QueryBuilder, but no model/relation helpers.

---

Next: Relations