Skip to content
Lakefront
Esc
↑↓navigate↵open⌘Jpreview
On this page

Tutorial

One continuous scenario across schema evolution, a declared board, transactions, watch, and the HTTP client.

This walks a single scenario, a roadmap board for a team called “acme”, from its first write through to being served over HTTP. It mirrors examples/tour.ts in the repository, which runs the same steps against a live lake. Run bun run tour to follow along with real output.

Each step assumes the engine from the quickstart is open:

await using engine = await open({
  postgres: "postgres://lakefront:lakefront@127.0.0.1:5432/lakefront",
  data: "./data/lake/",
  topology: "single-node",
});

Write to a board that does not exist

There is no createBoard call here. A write to an unknown board id creates it:

await engine.insertMany("acme_roadmap", [
  { id: "task-1", state: "active", title: "Ship the thing", status: "open", priority: 3 },
  { id: "task-2", state: "active", title: "Fix the bug", status: "stuck", priority: 1 },
]);

autoCreate and columns are the two knobs that change this behavior. Both are covered in the schema guide.

Add columns by writing them

Write a row with a column the board has never seen and the column is added for you. Rows that predate it read back as NULL:

await engine.insertMany("acme_roadmap", [
  {
    id: "task-3",
    state: "active",
    title: "Add dark mode",
    status: "open",
    priority: 2,
    assignee: "dana",
    due: "2026-09-01",
  },
]);

const evolved = (
  await engine.query("acme_roadmap", "SELECT id, assignee, due FROM {{table}} ORDER BY id")
).unwrap("read");
console.log(evolved.sync.columnsAdded); // ["assignee", "due"]

The inference rules, including what decides a column’s type, are in the schema guide.

Type conflicts

A column’s type is fixed the first time data implies it. A later value that does not fit is refused, not coerced:

const conflict = await engine.insertMany("acme_roadmap", [
  { id: "task-9", state: "active", title: "Triage", status: "open", priority: "N/A" },
]);
// conflict.isErr() && conflict.error._tag === "ColumnTypeConflict"

The fix is widenColumn. It only ever widens, so a request that would narrow a column is refused:

await engine.widenColumn("acme_roadmap", "priority", "text");
await engine.insertMany("acme_roadmap", [
  { id: "task-9", state: "active", title: "Triage", status: "open", priority: "N/A" },
]);

Every error tag, including ColumnTypeConflict, is cataloged in the errors reference.

Declare a strict schema

Inference is the default, not the only option. createBoard takes a schema up front, and a board created with strict: true refuses any write naming a column it was not told about:

await engine.createBoard("acme_billing", {
  columns: { amount: "double", currency: "text", paid: "boolean" },
  strict: true,
});

await engine.insertMany("acme_billing", [
  { id: "inv-1", state: "active", amount: 120.5, currency: "EUR", paid: false },
]);

const typo = await engine.insertMany("acme_billing", [
  { id: "inv-2", state: "active", amount: 80, curency: "EUR" }, // note: curency
]);
// typo.isErr() && typo.error._tag === "UnknownColumn"

strict is stored in the catalog alongside the board, so the policy holds on every node, not just the one that created it. The column-type vocabulary and how strict interacts with the engine-wide columns option are in the schema guide.

Run an analytics query

Reads are plain SQL against the board’s replica. {{table}} is bound server-side, so the SQL never names a board or a schema:

const grouped = (
  await engine.query(
    "acme_roadmap",
    "SELECT status, count(*) AS n, avg(TRY_CAST(priority AS DOUBLE)) AS avg_priority " +
      "FROM {{table}} GROUP BY status ORDER BY n DESC",
  )
).unwrap("read");
console.log(grouped.result.rows, grouped.syncMs, grouped.queryMs);

TRY_CAST matters here because priority now holds both numbers and the string "N/A". It turns each numeric value into a DOUBLE and every non-numeric value into NULL instead of failing the aggregate. syncMs and queryMs split freshness cost from execution cost; the queries guide covers the rest of a query outcome.

Read your own write

update returns the watermark it committed at. Pass it back as minWatermark and the read refuses to be served by a replica that has not caught up:

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

The watermark model behind this is on the consistency page.

Move rows across boards in a transaction

Every board lives in one catalog, so a transaction can touch several. Declare which boards it touches and write against the tx handle:

const moved = (
  await engine.transaction(["acme_roadmap", "acme_archive"], async (tx) => {
    const row = await tx.scalar("acme_roadmap", "SELECT title FROM {{table}} WHERE id = 'task-2'");
    await tx.insertMany("acme_archive", [{ id: "task-2", state: "archived", title: String(row) }]);
    await tx.deleteMany("acme_roadmap", ["task-2"]);
    return row;
  })
).unwrap("transaction");

console.log(moved.value, moved.watermark);

tx.scalar, tx.insert, and tx.delete are TransactionOps methods, and they throw rather than returning Result. The enclosing engine.transaction call returns the Result, and a throw inside the body aborts the whole block. Both boards commit at the one watermark in moved.watermark. The full semantics, including what a read inside the block can see, are in the transactions guide.

Watch for changes

watch follows a board as an async iterable: catch-up from a watermark, then live. Start listening before the write that should appear in it. The loop is released by its own abort signal, not by the write:

const stopWatching = new AbortController();
const feed = engine.watch("acme_roadmap", {
  after: engine.head(),
  signal: stopWatching.signal,
});
const firstEvent = feed.next(); // start listening BEFORE the write
await engine.update("acme_roadmap", "task-1", { status: "done" });
const seen = await firstEvent;
// seen.value.records.map((r) => r.kind) === ["update"]
stopWatching.abort();
await feed.return();

after defaults to the current head, so the call above only watches the future. Pass an older watermark to catch up first. A write on this node wakes the loop immediately; a write landing on another node arrives on the next poll. Catch-up, retention, and resync are covered in the watch guide.

Handle typed errors

A placeholder bound to a table outside the entity allowlist is refused rather than executed:

const refused = await engine.query("acme_roadmap", "SELECT * FROM {{t0}}", {
  bindings: new Map([["t0", "_lakefront_meta"]]),
});
// refused.error._tag === "EntityNotAllowed"

A board id is validated, not escaped. Anything that could not name a table is rejected before it reaches SQL:

const badBoard = await engine.query("'; DROP TABLE items; --", "SELECT 1");
// badBoard.error._tag === "InvalidBoardId"

Every tag EngineError can carry, and what triggers each one, is in the errors reference.

Serve the board over HTTP

Start a server node and talk to it through the client instead of the engine directly:

import { serve } from "lakefront/server";
import { connect } from "lakefront/client";

const node = await serve({
  postgres: "postgres://lakefront:lakefront@127.0.0.1:5432/lakefront",
  data: "./data/lake/",
  cache: "./data/node",
  nodeId: "tour",
  topology: "single-node",
});
const db = connect({ url: node.url }).board("acme_roadmap");

// Raw SQL, same surface as the engine:
const counted = (await db.query("SELECT count(*) AS n FROM {{table}}")).unwrap("query");

// Or compose it. The builder is rooted at the board, with no inner table to name:
const rows = await db.select(["id", "title"]).where("status", "=", "open").orderBy("id").execute();

db.query(sql) is exactly engine.query over the wire: same SQL, same {{table}}, same outcome shape. db.select(...) is the Kysely builder on top, and .compile() shows the SQL and bound parameters it produces. Mutations go through the same client and return a watermark exactly like the engine does:

const writeWatermark = (
  await db.insertMany([{ id: "task-4", state: "active", title: "Write docs", status: "open" }])
).unwrap("insert");

const openNow = await db
  .atLeast(writeWatermark)
  .select(["id"])
  .where("status", "=", "open")
  .execute();

await node.stop();

atLeast(watermark) is the client’s read-your-write, the same contract as minWatermark on the engine. What serve sets up, including auth and the SSE stream, is in the HTTP guide, and the exact BoardClient surface is in the client reference.

Last updated on September 10, 2026

Was this page helpful?