# Schema & migrations

SQL is the source of truth. Define your tables in SQL, evolve them with additive migrations, and generate the typed schema your query builder needs — with one CLI: rindle migrate, rindle schema gen.

Rindle's data model is **tables with typed columns** — and where your data lives in
SQLite (the [`rindled` daemon](/docs/daemon), a [Rust/Node replica](/docs/replica-and-views),
or a [synced app](/docs/architecture)), **the SQL schema is the source of truth.** You
define your tables in ordinary SQL and evolve them with migrations; the daemon
introspects the live file and maintains your queries against it.

The TypeScript schema you may have seen — `table("issue").columns({…}).primaryKey("id")` —
is **not** where your data model lives. It is a **generated, typed facade** for the
[query builder](/docs/supported-queries-ts): it gives the builder its column types,
drives the comparator, and parses `json` columns on read. You generate it *from* the SQL
with one command, so the client and the database can never drift. This page is that loop.

> **Writing Rust?** The embedded [`rindle-replica`](/docs/quickstart) path is already
> SQL-first — you run your own `CREATE TABLE` and build queries with the `rindle::table`
> AST builder, no generated TypeScript in sight. The migrate + `schema gen` loop below is
> for the daemon and its JS/TS clients.

## The loop

One toolchain does all of it: the **`rindle` CLI**, shipped beside the daemon. (Rust:
installed with `rindled`. JS/TS: `npm i -D @rindle/cli`, then `npx rindle …`; see
[`@rindle/cli`](/docs/rindle-cli) for the toolchain reference.)

```bash
rindle init          # scaffold rindle.ncl (the colocated pair) + an empty migrations/ directory
rindle up            # render rindle.ncl + supervise the local write-master + follower (Ctrl-C to stop)
```

`rindle init` writes a loopback dev topology and a `migrations/` folder:

```text
# rindle.ncl — the one topology (design 214): a write-master + follower(s)
{
  profile = "replicated",
  app = "my-app",
  followers = 1,   # 1 = the colocated pair, both processes on one box
}
```

`rindle up` renders it to the write-master + follower pair on loopback and supervises
both. There's no table list anywhere — your tables come from migrations, auto-discovered
on the follower as the master's DDL replicates.

### 1 · Author a migration in SQL

```bash
rindle migrate create init     # creates migrations/0001_init.sql
```

A migration is **ordinary, additive SQL DDL** — one statement per `;`. Every table needs
a single declared **primary key** (the engine indexes on it). Declare a column's *kind*
with its type name — including `BOOLEAN` and `JSON` (more below). Two habits pay off:
use `IF NOT EXISTS` so a re-run is safe, and add an index for each direction your joins
and windowed `orderBy`s traverse. (Column *order* matters too — the engine reads it back
with `PRAGMA table_info`, so append new columns rather than reordering.)

```sql
-- migrations/0001_init.sql
CREATE TABLE IF NOT EXISTS issue (
  id        TEXT PRIMARY KEY,
  title     TEXT    NOT NULL,
  closed    BOOLEAN NOT NULL DEFAULT 0,    -- declared BOOLEAN  → boolean()
  labels    JSON    NOT NULL DEFAULT '[]', -- declared JSON     → json()
  priority  INTEGER NOT NULL DEFAULT 0,
  createdAt REAL    NOT NULL
);
CREATE INDEX IF NOT EXISTS issue_created ON issue (createdAt DESC, id);  -- the paginated window

CREATE TABLE IF NOT EXISTS comment (
  id      TEXT PRIMARY KEY,
  issueId TEXT NOT NULL,
  body    TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS comment_issue ON comment (issueId);           -- the issue → comments join
```

### 2 · Apply it

```bash
rindle migrate apply           # POSTs each *.sql to the daemon, in order, idempotently
```

The CLI lints each statement (additive only — no `DROP`/`RENAME`; see
[the DDL subset](#evolving-your-schema)), then applies it through the **write-master's**
ordered migration path, which mints a schema-version bump and replicates it to every follower.
The new tables are **auto-discovered** — you don't list them anywhere. Under `rindle up`,
the pair reshapes to serve the new schema; a self-hosted deployment is bounced once
(`rindle restart`, a process restart, or `fly machine restart`):

```text
[migrate] applying 1 migration(s) from migrations/ → http://127.0.0.1:7611
  [applied] 0001_init  schemaVersion=0001_init
[migrate] done — 1 newly applied, 0 already present
```

`rindle migrate apply` is safe to re-run: the write-master dedups by migration id, so
already-applied migrations report `present` and nothing re-runs. `rindle migrate status`
diffs your local folder against what the write-master has applied.

### 3 · Generate the typed schema

```bash
rindle schema gen --out src/schema.gen.ts
```

This reads the follower's introspected schema (`GET /schema`) and emits the
`@rindle/client` definition — one `const` per table, sorted by name, plus the
`createSchema` aggregate:

```ts
// Generated by `rindle schema gen` from the daemon's introspected schema (GET /schema).
// Do not edit by hand — re-run the generator after each migration.
import { boolean, createSchema, json, number, string, table } from "@rindle/client";

export const comment = table("comment")
  .columns({
    id: string(),
    issueId: string(),
    body: string(),
  })
  .primaryKey("id");

export const issue = table("issue")
  .columns({
    id: string(),
    title: string(),
    closed: boolean(),   // ← from the declared BOOLEAN
    labels: json(),      // ← from the declared JSON
    priority: number(),
    createdAt: number(),
  })
  .primaryKey("id");

export const schema = createSchema({ tables: [comment, issue] });
```

That's the whole loop: **edit SQL → `migrate apply` → `schema gen`.** Re-run the last two
after every schema change.

### Adding local-only client tables to a generated schema

Do **not** hand-edit the generated file for browser-only tables such as drafts, selections,
or view preferences. Define those tables in a separate module and extend the generated schema:

```ts
// src/schema.local.ts
import { extendSchema, string, table } from "@rindle/client";
import { schema as generatedSchema } from "./schema.gen.ts";

export const selection = table("selection", { local: true })
  .columns({ id: string(), issueId: string() })
  .primaryKey("id");

export const clientSchema = extendSchema(generatedSchema, { tables: [selection] });
```

Use `clientSchema` in the browser. Keep using the generated `schema` for your API server and
any daemon-facing named-query registry. `extendSchema` accepts only `{ local: true }` tables,
which keeps real synced tables SQL-first and generated from daemon introspection.

## Column types: arbitrary type names

SQLite has only five storage classes, but it stores the **full declared type name verbatim**
and never restricts what you write. Rindle reads that declared name back, so you get the
column *kind* you meant — not just a coarse affinity:

| You declare | Generates | Notes |
| --- | --- | --- |
| `TEXT` · `VARCHAR(n)` · `CHAR` · `CLOB` | `string()` | TEXT affinity |
| `INTEGER` · `REAL` · `NUMERIC` · `DECIMAL` · … | `number()` | numbers are `f64` |
| `BOOLEAN` · `BOOL` | `boolean()` | recovered from the declared name |
| `JSON` · `JSONB` | `json()` | recovered from the declared name; stored as TEXT |
| `BLOB` | `string()` | no blob type yet — store bytes as base64 `TEXT` |

So the SQLite "type limitation" is a non-issue: **declare `BOOLEAN` or `JSON` and the
generated schema is `boolean()` / `json()`.** This matches what the engine already does
internally — a `BOOLEAN` column compares as a boolean, a `JSON` column is parsed on read —
so the generated types agree with runtime behavior.

The one thing a declared name *can't* carry is a refinement **within** a kind — the element
type of `json<T>()`, or a string/number literal union. Those you layer on by hand after
generating (the generated file is yours to re-annotate, then re-apply after each regen):

```ts
import { json, type Col } from "@rindle/client";

labels: json<string[]>(),                          // refine the JSON shape
status: string() as Col<"todo" | "doing" | "done">, // refine a string to a literal union
```

A bare `INTEGER` you *intend* as a boolean stays `number()` — the name carried no intent.
Declare it `BOOLEAN` to recover it. (`bigint` and raw `blob` are refused at apply time:
numbers are `f64`, and there is no blob column type yet.)

## What the generated schema is for

The schema is **purely for the typed query builder** — it is never consulted for
correctness. Concretely it gives you:

- **Typed queries and rows.** `schema` types `store.query.<table>` and the rows you read
  back, so `where`/`orderBy`/`select` are checked against real columns and a result is
  `{ id: string; closed: boolean; labels: string[] }`, not `any`.
- **The comparator.** Each column's kind drives ordering (strings bytewise, numbers by
  total order, booleans as 0/1) so a client sorts a view exactly as the engine does.
- **`json` parsing.** `json` columns arrive as text on the wire and are parsed to objects
  once, on read.

What it is **not**: it carries **no relationships**. Query correlations
(`issue.id → comment.issueId`) live in your [named queries and fragments](/docs/fragments),
not in the schema — which is why plain SQL introspection (columns + PK) is enough to
generate it. And because the daemon validates a client's schema fingerprint on subscribe,
a stale generated schema is **rejected and re-fetched**, never silently wrong: regenerating
after a migration is a convenience, not a correctness burden.

Import it wherever you build queries — the [synced client](/docs/client) and
[API server](/docs/api-server) share the one value:

```ts
import { createRindleClient } from "@rindle/optimistic";
import { schema } from "./schema.gen.ts";          // generated
import { mutators } from "./mutators.ts";          // hand-written (your app logic)

export const app = await createRindleClient({ schema, mutators, /* … */ });
```

## Evolving your schema

v1 migrations are **additive**: `CREATE TABLE`, `ADD COLUMN`, `CREATE INDEX`. (Destructive
or rewriting DDL — `DROP` / `RENAME` / type changes — is rejected for now; fix forward with
a new additive migration.) Each `migrate apply` advances the write-master's `schemaVersion`,
which namespaces live query results: an old-schema client can't attach to a new-shape view,
so on the bounce it simply re-leases against the new version. After any change, **re-run
`rindle schema gen`** and ship the regenerated schema with your client.

Migrations are the one way to shape the schema: `rindle migrate apply` sends your DDL to the
write-master, which replicates it to every follower. There's no inline table list to
maintain.

## The one hand-authored case: standalone wasm

[`@rindle/wasm`](/docs/wasm-client) run **with no server** has no SQLite underneath — it
maintains queries over in-memory rows you push with `tx.add(…)`. There is no database to
introspect, so for that standalone playground you write the same `table(…).columns({…})`
schema **by hand**. The moment a daemon backs it (a [synced app](/docs/architecture)), the
schema becomes a generated artifact again — the source of truth moves back to SQL.

## Next steps

- [Run the daemon](/docs/daemon) — the read-follower that serves `/schema` (the
  write-master serves `/migrate`).
- [The browser client](/docs/client) — imports the generated `schema` to run live, optimistic
  queries.
- [Supported query shapes](/docs/supported-queries-ts) — what the typed builder can lower.
- [Reactive queries in the browser](/docs/wasm-client) — the standalone engine, where you
  author the schema by hand.

---

[View this page on Rindle](https://rindle.sh/docs/schema)