Reusable and conditional queries

Kysoql builders are immutable, which makes it safe to share a base query and derive different versions from it.

Reuse a base queryCopy Anchor Link

import { db } from "./db";

const base = db.selectFrom("Account").select(["Id", "Name"]);

const firstPage = base.orderBy("Name").limit(25);
const search = base.where("Name", "like", "Acme%").limit(10);

firstPage and search do not change base or each other. Always keep the return value from a builder method.

Use $call for reusable query helpersCopy Anchor Link

import { db } from "./db";

const soql = db
  .selectFrom("Account")
  .select(["Id", "Name"])
  .$call((query) => query.where("Name", "like", "Acme%"))
  .$call((query) => query.compile().soql);

$call passes the current builder to your callback and returns whatever the callback returns. That can be another builder, a compiled query, a promise, or a plain value.

It is especially useful for shared query functions because the callback receives the specialised builder type you already have.

Add filters or selections conditionallyCopy Anchor Link

import { db } from "./db";

export function accountSearch(prefix: string, includeRevenue: boolean) {
  return db
    .selectFrom("Account")
    .select(["Id", "Name"])
    .$if(prefix.length > 0, (query) =>
      query.where("Name", "like", `${prefix}%`),
    )
    .$if(includeRevenue, (query) => query.select("AnnualRevenue"))
    .limit(25);
}

$if(condition, callback) runs the callback only when the condition is true.

A field selected inside $if becomes optional in the inferred result because TypeScript cannot know which branch ran. Check for undefined before using a conditionally selected field.

Use $if for changes within the same query shape. If one branch needs a record query and another needs an aggregate or a different Apex mode, branch outside the builder instead.

Be careful when the condition controls an authorization or tenant filter. Skipping an optional search condition is very different from skipping an access-control boundary. See security and production use.

Clear a clause when that is clearer than rebuildingCopy Anchor Link

import { db } from "./db";

const query = db
  .selectFrom("Account")
  .select(["Id", "Name"])
  .where("Name", "like", "Acme%")
  .orderBy("Name")
  .limit(25);

const idsOnly = query.clearSelect().select("Id").clearOrderBy();
const unfiltered = query.clearWhere();

Clearing a clause leaves the earlier builder unchanged.

Use the helpers deliberately. clearWhere() can remove a required object predicate or an application security boundary. clearGroupBy() is only available when removing grouping would leave a valid query.

Not every specialised builder exposes every clear method. The Core API reference shows the methods available on each builder type.