---
title: Query
description: 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](/guides/fundamentals).

## engine.query

```ts title="query.ts"
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

```ts
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.

| Field                | What it tells you                                                                           |
| -------------------- | ------------------------------------------------------------------------------------------- |
| `sync`               | What the pre-query sync did, or that it was skipped because the replica was already current |
| `cache`              | Whether the replica was already attached (see below)                                        |
| `syncMs` / `queryMs` | Freshness cost split from execution cost                                                    |
| `watermark`          | This read's own watermark, usable as a later `minWatermark`                                 |

`cache` is one of three outcomes:

| `cache`    | Meaning                               |
| ---------- | ------------------------------------- |
| `hit`      | The replica was already attached      |
| `rebuild`  | The replica was rebuilt from the lake |
| `reattach` | The replica was reattached from disk  |

## 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:

```ts
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](/concepts/consistency).

## 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:

```ts title="query.ts"
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:

**Query**

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

**Inspect the compiled SQL**

```ts
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:

```ts
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.

:::warning
`withReplica` bypasses the read path's freshness guarantee. Use it only for maintenance or inspection code that has its own reason not to want a sync. It isn't a shortcut around `query()` for ordinary reads.
:::