Schema & migrations

Rindle queries use tables with typed columns. For a Rindle database or synced app, SQL migrations define those tables. The CLI applies the migrations and generates a TypeScript schema for the query builder.

The generated schema describes column types, primary keys, comparison rules, and JSON parsing. Regenerate it after SQL schema changes so the application matches the database. Keep relationships, mutators, and other handwritten definitions in separate files.

Choose the schema workflow for your data source:

Local-only UI tables can extend a generated browser schema without a server migration. See local-only tables.

# Migrations

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 for the toolchain reference.)

rindle init
rindle dev --migrate --gen src/schema.gen.ts -- vite dev

rindle init writes the default loopback replicated topology and a migrations/ folder. rindle dev renders that topology, supervises its processes, applies and watches migrations, regenerates the TypeScript schema, and runs your app with RINDLE_URL plus RINDLE_DATABASE_TOKEN. Set profile = "standalone" when one local rindled should own both reads and serialized writes; the migration and schema-generation loop is unchanged.

The topology itself remains a small input record:

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

Use rindle up only when you want the data tier without an app process. There’s no table list anywhere — tables come from migrations. Standalone discovers them locally; in the replicated profile, followers discover them as the master’s DDL replicates.

Every non-empty migration file must be one of two pure kinds:

• DDL — schema statements such as CREATE, ALTER, and DROP.
• DML — data writes such as INSERT, UPDATE, and DELETE.

DDL and DML cannot appear in the same file. If a backfill depends on an earlier schema change, keep their zero-padded filenames ordered.

# 1 · Author a schema migration

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

A schema migration is ordinary SQL DDL — one statement per ;. Every table needs a declared primary key, which can span several columns. Key columns must not contain null. 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 orderBys traverse. Regenerate the TypeScript schema after a schema change so its column positions match the database.

-- migrations/0001_init.sql
CREATE TABLE IF NOT EXISTS issue (
  id        TEXT NOT NULL 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 NOT NULL 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

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

The CLI classifies and checksums every file before sending the ordered batch. DDL still rejects RENAME, column type changes, and raw blob. Destructive statements print a loud notice first — see evolving your schema. The write authority commits each schema migration in order. Standalone reshapes its own live-query state; the replicated master forwards the DDL so every follower reshapes. New tables are auto-discovered — you don’t list them anywhere — and no manual restart is needed:

[migrate] applying 1 migration(s) from migrations/ → <write authority>
  [applied] 0001_init  schemaVersion=0001_init
[migrate] done — 1 newly applied, 0 already present
[migrate] schema committed on the write authority; followers, when present, apply DDL over replication — no manual restart needed.

rindle migrate apply is safe to re-run. The write authority binds each id to its kind and content checksum. An exact match reports present, while edited content or reusing a DDL id for DML fails instead of silently adopting it. rindle migrate status validates the local kinds and checksums against the applied journals.

If an applied file was changed cosmetically and reverting it is no longer practical, the first line of the file can explicitly accept the checksum that actually ran:

-- OVERRIDE_HASH: 0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef
CREATE TABLE issue (...);

Copy the applied hash shown by rindle migrate status, then review the change before adding the directive. The directive is excluded from the SQL and the current checksum. The write authority accepts it only when it exactly names that migration id’s stored hash. It never re-executes the edited SQL, changes the stored history, or permits a DDL/DML kind mismatch. A fresh database applies the current file normally, so use this escape hatch only when the old and current SQL are operationally equivalent.

# Data migrations

Put seeds and bounded backfills in pure-DML files after any DDL they depend on:

-- migrations/0002_add_color.sql (DDL)
ALTER TABLE issue ADD COLUMN color TEXT;

-- migrations/0003_backfill_color.sql (DML)
UPDATE issue SET color = 'red' WHERE priority >= 8;
UPDATE issue SET color = 'blue' WHERE color IS NULL;

The write authority evaluates a data migration exactly once in one transaction. In the replicated profile it captures the concrete inserted, updated, deleted, cascaded, and conflict-resolved rows and ships those deltas to followers; followers never execute the migration SQL. This also makes values from random() or time functions converge exactly. An apply-once marker commits with the row changes, including when the DML affects zero rows.

Data migrations accept statements classified as writes: INSERT, UPDATE, DELETE, and write CTEs. Reads, PRAGMAs, and explicit transaction control are rejected. A DML file does not change schemaVersion or reshape live-query state.

One data migration can capture at most 8,191 user-row changes (including cascades) and 64 MiB of encoded row data. The marker consumes the final change in HCTree’s 8,192-change transaction budget. If a file crosses either limit, the write authority rolls it back. Split a large backfill into explicitly key-ranged, separately numbered files.

Mixed DDL+DML table rebuilds are not atomic in v1. Express an additive change and its backfill as two ordered files. The public SqlClient.migrate() surface remains DDL-only. Deploy data migrations with rindle migrate apply, locally or with --cloud.

# 3 · Generate the typed schema

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

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

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

// 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’s declared type names carry more information than its storage classes. Rindle uses those names to choose column kinds and generate TypeScript definitions. Its database admission and write paths enforce additional supported-value rules.

A bare INTEGER intended as a boolean still generates number(). Declare it BOOLEAN to express that intent. A non-exact spelling such as UNSIGNED BIGINT does not opt into the BIGINT/INT8 behavior.

Exact int64 values can round-trip through SQL and replication. Maintained queries currently reject an int64 column in their required data, including a primary key. Selecting only other columns can work when the query does not need the int64 column for identity, filtering, ordering, or another operation.

The generator cannot infer a JSON interface or a string literal union. Use refineTable and refineSchema in a handwritten module. Do not add casts to the generated file. Refinements change types, not stored-value validation.

# What the generated schema is for

The schema supplies both TypeScript types and runtime metadata:

• 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, not in the schema. That is why plain SQL introspection (columns + PK) is enough to generate it. The normalized client also checks advertised table names, column compatibility, and primary keys. It accepts supported projections and additive expansions. These checks do not regenerate your application code or validate every type refinement. Regenerate and ship the schema with application changes.

Import the schema wherever you build queries and configure a backend. The manual quickstart defines these imports for the browser and API server, while local table extensions belong only in the browser schema.

# Evolving your schema

Migrations cover both directions of schema change:

• Additive — CREATE TABLE, ADD COLUMN, CREATE INDEX.
• Destructive — DROP TABLE, ALTER TABLE … DROP COLUMN, DROP INDEX. A drop deletes the schema (and its data) on the write authority and every follower, when present. rindle migrate apply prints a [destructive] notice per statement before sending anything. There is no flag to set — the reviewed migration file is the consent. A replicated backup or an operator-created standalone snapshot is the undo.

Still rejected: RENAME (expand instead: add the new column/table, move writes in your app, then drop the old one), column type changes, and raw blob columns.

Each applied DDL file advances the write authority’s schemaVersion, which namespaces live-query results. An old-schema client can’t attach to a new-shape view, so after the daemon reshapes it re-leases against the new version. A DML file advances the ordered write cursor but leaves schemaVersion unchanged. After any schema 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 authority, which replicates it to every follower when present. There’s no inline table list to maintain.

# Dropping safely: contract like you expand

Order a removal the same way you order an addition, just reversed:

1. Ship the app without the doomed table/column first — remove it from named queries, fragments, mutators, and room declarations. A query that still names it after the drop fails cleanly (that one query errors, and nothing else is affected) — visible, not corrupt — but there’s no reason to ship that.
2. Apply the drop migration. The daemon reshapes and clients re-lease + re-hydrate automatically.
3. Regenerate (rindle schema gen) so the typed schema no longer mentions it.

Two SQLite rules worth knowing: you can’t drop a primary-key column, and you can’t drop an indexed column directly. Drop the index first, in the same migration:

-- migrations/0007_remove_priority.sql
DROP INDEX IF EXISTS issue_priority;
ALTER TABLE issue DROP COLUMN priority;

If you declared foreign keys, drop in dependency order. The write authority enforces foreign_keys = ON, and dropping a table that other tables still reference is refused — whether or not either table holds rows. The error names the cause and the fix. Drop the referencing tables first — the order composes in one migration:

-- migrations/0008_remove_comments.sql
DROP TABLE comment;   -- references issue(id)
DROP TABLE issue;

Dropping a referenced table while keeping a table that points at it is not supported. SQLite cannot drop a foreign-key constraint in place, and the dangling reference breaks the surviving table’s writes. This holds even if you recreate the referenced table under the same name with a different key. A DROP TABLE parent; CREATE TABLE parent (…) that no longer carries the referenced column is refused too (a same-shape rebuild is fine). To keep that data, expand-contract it: create a replacement table without the foreign key in a DDL migration. Move the rows with a bounded DML migration and move application writes. Then drop both old tables.

A replicator host that configures startup table definitions (TableSpec) has an additional constraint: those declared tables are pinned. The declaration re-creates them at every boot, so a migration that drops one is refused. Remove the table from the declaration (redeploy), then apply the drop. Apps built on the migration-first flow above declare nothing and never see this.

A direct embedded Db has a different migration lifecycle. Its exec_ddl rejects schema changes to registered tables. Close and reopen the runtime, apply DDL before table registration, then recreate its queries. The embedded guide describes this contract; it does not use the daemon’s automatic reshape path.

# Handwritten schemas for in-memory stores

A standalone WASM store has no SQLite database to introspect. Define its tables with table(...).columns(...) and push rows through store.write.

Handwritten schemas also describe local-only tables and application-owned source integrations. For a standard daemon-backed app, generate the synced table definitions from SQL so their column order, keys, and kinds match the database.

# Next steps

• Run the daemon — standalone owns reads and writes; a replicated follower serves /schema while its master serves /migrate.
• The browser client — imports the generated schema to run live, optimistic queries.
• Supported query shapes — what the typed builder can lower.
• Reactive queries in the browser — the standalone engine, where you author the schema by hand.