Skip to main content

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.

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

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

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("active_users", (cte) =>
    cte.newCte("users_cte", (b) => b.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, whereIn, whereNotIn, whereNull, whereNotNull, whereBetween, whereNot, 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();

Subqueries & Nested Conditions​

  • whereSubQuery, andWhereSubQuery, orWhereSubQuery, whereBuilder, andWhereBuilder, orWhereBuilder
await sql
  .query("users")
  .whereSubQuery("id", (sub) =>
    sub.select("userId").from("posts").where("published", true)
  )
  .many();
await sql
  .query("users")
  .whereBuilder((qb) => qb.where("age", ">", 18).orWhere("isActive", true))
  .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
const page = await sql.query("users").paginate(1, 10);
await sql.query("users").limit(5).offset(10).many();

Locking​

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

CTEs (Common Table Expressions)​

  • with
await sql
  .query("users")
  .with("normal", (cte) => cte.newCte("users_cte", (b) => b.select("name")))
  .many();

Copying & Query Output​

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

QueryBuilder Only​

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

Next: Relations