Database

Query Builder

Introduction

JCC Express MVC provides a fluent query builder through DB.table(...) and DB.query().

Use it for composable SQL queries without writing full raw SQL for every operation.

Starting queries

TypeScript

const q1 = DB.table("users");
const q2 = DB.query().from("users");

Select columns:

TypeScript

const rows = await DB.table("users").select("id", "name", "email").get();

Read methods

TypeScript

const all = await DB.table("users").get();
const first = await DB.table("users").where("id", 1).first();
const found = await DB.table("users").find(1);

const exists = await DB.table("users").where("email", email).exists();
const notExists = await DB.table("users").where("email", email).doesntExist();
const oneValue = await DB.table("users").where("id", 1).value("email");

Where clauses

TypeScript

const rows = await DB.table("users")
  .where("active", true)
  .where("age", ">", 18)
  .orWhere("role", "admin")
  .whereNull("deleted_at")
  .get();

Other common filters:

whereNot(...), orWhereNot(...)
whereNotNull(...)
whereIn(...)
whereBetween(...), whereNotBetween(...)
whereDate(...), whereMonth(...), whereDay(...), whereYear(...), whereTime(...)
whereLike(...), orWhereLike(...)
whereRaw(...), orWhereRaw(...)
whereColumn(...), orWhereColumn(...), whereNotColumn(...)
whereJson(...), orWhereJson(...)
whereExists(...), orWhereExists(...)

Ordering, grouping, and having

TypeScript

const report = await DB.table("orders")
  .select("status")
  .select(DB.raw("count(*) as total"))
  .groupBy("status")
  .having("status", "!=", "cancelled")
  .orderBy("total", "desc")
  .get();

Also available:

latest(column?), older(column?)
orderByRaw(...)
groupByRaw(...)
havingIn(...), havingNotIn(...)
havingNull(...), havingNotNull(...)
havingBetween(...), havingNotBetween(...)
havingRaw(...)

Joins

TypeScript

const rows = await DB.table("posts")
  .join("users", "posts.user_id", "=", "users.id")
  .leftJoin("comments", "comments.post_id", "=", "posts.id")
  .select("posts.*", "users.name as author")
  .get();

Join variants:

innerJoin(...)
leftJoin(...)
rightJoin(...)
leftOuterJoin(...)
rightOuterJoin(...)
fullOuterJoin(...)
joinRaw(...)
subquery joins: joinSub(...), leftJoinSub(...), rightJoinSub(...), crossJoinSub(...)

Write methods

TypeScript

await DB.table("users").insert({ name: "Abdou", email: "abdou@example.com" });

await DB.table("users").where("id", 1).update({ name: "Abdou J" });
await DB.table("users").where("id", 1).updateQuietly({ name: "No Events" });

await DB.table("users").where("id", 1).delete();
await DB.table("users").where("id", 1).deleteQuietly();

Counters and JSON updates:

TypeScript

await DB.table("users").where("id", 1).increment("login_count", 1);
await DB.table("users").where("id", 1).decrement("credits", 2);
await DB.table("users").where("id", 1).updateJson("profile->theme", "dark");

Aggregates and pagination

TypeScript

const total = await DB.table("users").count("id");
const maxScore = await DB.table("users").max("score");
const minScore = await DB.table("users").min("score");
const sumScore = await DB.table("users").sum("score");
const avgScore = await DB.table("users").avg("score");

const page = await DB.table("users").orderBy("id", "desc").paginate(1, 15);

Pagination returns a page payload (data + metadata) from builder internals.

Raw SQL and transactions

TypeScript

const rows = await DB.runQuery("select * from users where active = ?", [1]);
const rawExpr = DB.raw("count(*) as total");

await DB.transaction(async () => {
  await DB.table("wallets").where("id", 1).decrement("balance", 100);
  await DB.table("wallets").where("id", 2).increment("balance", 100);
});

Utility methods

clone() -> duplicate a query
toSQL() / toSqlCode() -> inspect generated SQL
offset(...), limit(...)
distinct(...)
pluck(...)
union(...), unionAll(...)
truncate()

Summary

Use DB.table(...) for most query-builder workflows.
Chain filters/joins/groups, then finalize with get, first, insert, update, delete, etc.
Use runQuery / raw for advanced SQL and DB.transaction(...) for atomic operations.