Query

Run SQL against a board's replica from the engine, or build it with Kysely through the HTTP client.

Reads run against the board’s local DuckDB replica, synced to at least the requested watermark first. A query never touches the truth store directly.

SQL dialect

Queries run as DuckDB SQL, not Postgres’s dialect, though DuckDB’s own SQL is close enough to Postgres’s that most statements read the same. DuckDB’s analytical functions are available too, things like TRY_CAST and list() that Postgres doesn’t have. For the {{table}} placeholder and the $1/$2 params convention every query uses, see the fundamentals guide.

engine.query

const outcome = (
  await engine.query(
    "acme_roadmap",
    "SELECT id, title FROM {{table}} WHERE status = $1 ORDER BY id",
    { params: ["open"] },
  )
).unwrap();

outcome.result.columns; // ["id", "title"]
outcome.result.rows; // [["task-1", "Ship the thing"], ...]

{{table}} is a placeholder, not the board’s real table name: it is bound server-side against a fixed entity allowlist, which is what makes cross-board access unrepresentable in the query language rather than merely checked. params are positional ($1, $2, …) and carried separately from the SQL text, so a value can never become syntax.

QueryOutcome

interface QueryOutcome {
  readonly result: { columns: string[]; rows: Cell[][] };
  readonly sync: SyncResult; // { from, to, skipped, changesApplied, rowsInserted, rowsDeleted, columnsAdded }
  readonly cache: "hit" | "rebuild" | "reattach";
  readonly syncMs: number;
  readonly queryMs: number;
  readonly watermark: number;
}

result is columnar: an array of columns and an array of row-arrays, not row objects, because building one object per row is exactly the cost a JS serving layer burns its latency budget on for a wide read.

cache is one of three outcomes:

Read-your-write: minWatermark and atLeast

Pass minWatermark to refuse a read served by a replica older than a watermark you already hold, typically one a write just returned:

const w = (await engine.update("acme_roadmap", "task-2", { status: "done" })).unwrap();
const fresh = await engine.query("acme_roadmap", "SELECT status FROM {{table}} WHERE id = $1", {
  params: ["task-2"],
  minWatermark: w,
});

The client’s equivalent is atLeast(watermark), which returns a new BoardClient bound to that floor rather than mutating the one you called it on. The full watermark model, including what “at least” costs, cross-board staleness bounds, and why this is the one case that pays a round-trip on an otherwise-current replica, is covered in the consistency model.

Query over HTTP

The client’s query is engine.query over the wire. Same SQL, same {{table}}, same options, same outcome shape. The board handle already knows its board, so there is no first argument:

import { connect } from "lakefront/client";

const db = connect({ url }).board("acme_roadmap");

const outcome = (
  await db.query("SELECT id, title FROM {{table}} WHERE status = $1 ORDER BY id", {
    params: ["open"],
  })
).unwrap();

outcome.result.rows; // columnar, exactly like engine.query
outcome.servedBy; // plus which node answered -- the one extra field

Build queries with Kysely

select and selectAll start a Kysely chain rooted at the board: a board is one table, so there is no table to name. The chain returns row objects rather than columns:

const rows = await db
  .select(["id", "title"])
  .where("status", "=", "open")
  .orderBy("id")
  .execute();

const built = db.select(["id", "title"]).where("status", "=", "open");

built.compile().sql; // Postgres-flavoured SQL with a placeholder, never a real table name
built.compile().parameters; // ["open"]

A plugin rewrites the table reference into a placeholder before the query leaves the client (the same mechanism as {{table}}), so compiled SQL never names a schema or a board. compile() is Kysely’s escape hatch for inspecting what will be sent without executing it.

After any mutation, db.atLeast(watermark) chains the same way:

const w = (await db.insert({ id: "task-4", state: "active", title: "Write docs" })).unwrap();
const openTasks = await db.atLeast(w).select(["id"]).where("status", "=", "open").execute();

db.lastRead() reports what the previous read did: { watermark, servedBy, cache }. It’s useful for demonstrating cache affinity or debugging routing; correctness doesn’t depend on it. For joins or subqueries beyond the rooted chain, the raw Kysely instance is db.db.

withReplica: low-level access

engine.withReplica(boardId, fn) hands fn the raw pieces query() assembles from: a leased DuckDB connection, the attached LocalReplica, the Lake, and the HeadCache. Attaching the replica and tracking it as busy are handled for you; syncing is not. query() calls sync before running SQL, withReplica does not, so a caller that skips it may run against a replica that is behind the lake.