JCC Eloquent ORM

Query Builder

Introduction

The Builder class is the underlying query layer for all model queries and raw DB access. It wraps Knex with a Laravel-style fluent API.

Most methods are also available as static shortcuts on any model class.

Select and projection

TypeScript

// Select specific columns
await User.select("id", "name", "email").get();

// Distinct rows
await User.distinct("country").get();
await User.select("id", "name").distinct().get();

// Alias a subquery
const sub = User.select("id").where("active", true).as("active_users");

// Raw FROM clause
await DB.table("users").fromRaw("users FORCE INDEX (idx_email)").get();

Clearing query clauses

Useful when reusing a query builder instance:

TypeScript

const q = User.where("active", true).orderBy("name");

q.clearWhere();   // remove all WHERE conditions
q.clearSelect();  // remove SELECT overrides (back to *)
q.clearGroup();   // remove GROUP BY
q.clearHaving();  // remove HAVING

Raw results (skip model hydration)

getRaw() and firstRaw() return plain objects even when a model is set:

TypeScript

// Returns Record<string, any>[] — no model wrapping
const rows = await User.where("active", true).getRaw();

// Returns Record<string, any> | null
const row = await User.where("id", 1).firstRaw();

Useful for lightweight projections, exports, or reporting where model overhead is unnecessary.

Clone

Duplicate the current query state so you can branch without mutating the original:

TypeScript

const base = User.where("active", true);

const admins = base.clone().where("role", "admin").get();
const editors = base.clone().where("role", "editor").get();

Existence helpers

TypeScript

const exists = await User.where("email", email).exists();
const missing = await User.where("email", email).doesntExist();

Value helper

Get a single column value from the first matching row:

TypeScript

const email = await User.where("id", 1).value("email");
const count = await User.value("name"); // first name in table

Aggregates

TypeScript

const total   = await User.count("id");
const highest = await User.max("score");
const lowest  = await User.min("score");
const average = await User.avg("score");
const total   = await User.sum("points");

Aggregates also accept aliases via an object:

TypeScript

const result = await User.select().max({ top: "score" }).firstRaw();
// result.top -> highest score

Where variants

LIKE helpers

TypeScript

await User.whereLike("name", "john").get();   // name LIKE '%john%'
await User.orWhereLike("name", "jane").get();
await User.andWhereLike("email", "@example").get(); // AND LIKE

JSON column paths

TypeScript

// WHERE JSON_UNQUOTE(JSON_EXTRACT(settings, '$.theme')) = 'dark'
await User.where("settings.theme", "dark").get();
await User.where("settings.notifications.email", true).get();

Grouped conditions

TypeScript

await User.where((q) => {
  q.where("role", "admin").orWhere("role", "editor");
}).where("active", true).get();

Array of conditions

TypeScript

await User.where([
  ["status", "active"],
  ["role", "=", "admin"],
]).get();

Date helpers

TypeScript

await Post.whereDate("created_at", "2024-01-01").get();
await Post.whereMonth("created_at", 6).get();
await Post.whereDay("created_at", 15).get();
await Post.whereYear("created_at", 2024).get();
await Post.whereTime("created_at", ">", "12:00:00").get();

Column comparison

TypeScript

await Order.whereColumn("updated_at", ">", "created_at").get();
await Order.orWhereColumn("shipped_at", "updated_at").get();

Group By

TypeScript

await Order.groupBy("status").select("status").count("id").get();
await Order.groupByRaw("YEAR(created_at), MONTH(created_at)").get();

Raw helpers

TypeScript

await User.whereRaw("LOWER(email) = ?", ["user@example.com"]).get();
await User.orWhereRaw("status IN (?, ?)", ["active", "pending"]).get();
await User.runQuery("SELECT count(*) as c FROM users WHERE active = ?", [1]);

Joins with callbacks

TypeScript

await User.join("posts", (join) => {
  join.on("users.id", "=", "posts.user_id")
      .orOn("users.id", "=", "posts.editor_id");
}).get();

await User.leftJoin("profiles", (join) => {
  join.on("users.id", "=", "profiles.user_id");
}).select("users.*", "profiles.avatar").get();

Available join types: join, innerJoin, leftJoin, rightJoin, leftOuterJoin, rightOuterJoin, fullOuterJoin.

Subquery joins

TypeScript

await User.joinSub(
  Order.where("total", ">", 100),
  "big_orders",
  (on) => on.on("users.id", "=", "big_orders.user_id")
).get();

await User.leftJoinSub(
  Profile.select("user_id", "avatar"),
  "p",
  (on) => on.on("users.id", "=", "p.user_id")
).get();

Union

TypeScript

const admins  = User.where("role", "admin");
const editors = User.where("role", "editor");

const result = await admins.union([editors]).get();
const all    = await admins.unionAll([editors]).get();

Increment / Decrement

TypeScript

await User.where("id", 1).increment("login_count");
await User.where("id", 1).increment("score", 10);
await User.where("id", 1).decrement("credits", 5);

// Multiple columns at once
await User.where("id", 1).increment({ score: 5, level: 1 });

JSON column updates

TypeScript

// UPDATE users SET settings = JSON_SET(settings, '$.theme', 'dark') WHERE id = 1
await User.where("id", 1).updateJson("settings.theme", "dark");
await User.where("id", 1).updateJson("preferences.notifications.email", false);

Cursor pagination

For large datasets where COUNT(*) is too slow, use cursor-based (keyset) pagination:

TypeScript

const page = await User.orderBy("id").cursorPaginate(20);

Response shape:

JSON

{
  "data": [...],
  "meta": {
    "per_page": 20,
    "has_more": true,
    "next_cursor": "eyJpZCI6MTAwfQ==",
    "prev_cursor": null,
    "cursor_column": "id",
    "direction": "asc"
  },
  "links": {
    "path": "http://localhost/api/users",
    "next": "http://localhost/api/users?cursor=eyJpZCI6MTAwfQ==",
    "prev": null
  }
}

Pass the next page by forwarding ?cursor=<token> from meta.next_cursor.

Simple pagination

Offset pagination without a COUNT(*) query — faster for large tables:

TypeScript

const page = await User.where("active", true).simplePaginate(15);

Response:

JSON

{
  "data": [...],
  "meta": {
    "current_page": 2,
    "per_page": 15,
    "has_more": true,
    "next_page": 3,
    "prev_page": 1
  }
}

Pluck

Get an array of values for a single column:

TypeScript

const emails = await User.where("active", true).pluck("email");
// ["alice@example.com", "bob@example.com", ...]

Transactions

Wrap multiple operations in a transaction — rolls back automatically on error:

TypeScript

await DB.transaction(async () => {
  const user = await User.create({ name: "Alice" });
  await user.posts().create({ title: "First post" });
});

The transaction context propagates to all Builder and Model calls within the callback via AsyncLocalStorage — no need to pass trx manually.

Summary

Use clone() to branch a query without mutation.
Use getRaw() / firstRaw() when you don't need model instances.
Use cursorPaginate() for stable, high-performance pagination on large tables.
Use transaction(cb) for atomic multi-step writes.
Use JSON path helpers (where("col.key", val), updateJson(...)) for JSON columns.