# Welcome to Rindle

> A simple and fast SQL database with live queries

Think Convex, but simple, open, and cheap. Turso, but with live queries.

Under the hood, Rindle is powered by a Rust port of
[ZVM](https://github.com/rocicorp/rindle/blob/branch/docs/ZVM.md), the incremental query engine behind
[Zero](https://zero.rocicorp.dev/). ZVM blends ideas from traditional and incremental
query engines, making live queries cheap enough to be on by default.

## Features

- **Simple** It's SQL plus `subscribe()`. You already know how to use it: `CREATE TABLE`,
  `INSERT`, `SELECT`. Database, not framework.
- **Live** Agents get woken on changes. Users see what happened without refreshing. Idle
  queries cost no CPU and ~400b of memory.
- **Fast** [First result in milliseconds, updates in microseconds](https://rindle-docs.preview.rocicorp.dev/performance.md). Thousands of live queries and thousands of writes/second on one
  core.
- **Tree queries** One trailing `WITH` clause, and Rindle returns the tree of data your
  application actually wants, instead of a flat table to reassemble.
- **Composable** Layer a sync client like `@rocicorp/zero-client` or TanStack DB on top for instant
  client-first reads and writes. Or run Rindle client-side only, as an embedded relational database
  with live queries.
- **Cheap** Built to run on local NVMe with S3 as the log, so a database costs nothing when idle
  and almost nothing when active. Make one per app, one per agent, or let agents spawn their own.

## Ready to try?

Head to [Get Started](https://rindle-docs.preview.rocicorp.dev/get-started.md). Agents, see [For Agents](https://rindle-docs.preview.rocicorp.dev/agents.md).

---

# Get Started

Rindle is in private beta. You need access to the [rocicorp/rindle](https://github.com/rocicorp/rindle) repo to follow these instructions. Email
[aa@roci.dev](mailto:aa@roci.dev?subject=Rindle%20beta%20access) to get access.

## With Agents

Paste a prompt like this into your agent:

Prompt for your agent (Polls):

```text
Build me a live polling app with Rindle. Use the skill https://rindle-docs.preview.rocicorp.dev/skill.md.

One page creates a poll with a question and 2–5 options
and gives me a share link. Anyone with the link sees the poll, votes once, and watches the
counts update live, without refreshing. Render the pages on the server so the poll shows up on
first load, then stays live.
```

Prompt for your agent (Chess):

```text
Build me multiplayer chess with Rindle. Use the skill https://rindle-docs.preview.rocicorp.dev/skill.md.

I create a game and get a link to send a friend, and we
each see the board update the moment the other moves. The server enforces the rules: it accepts a
move only if it is legal and it is that player's turn, so nobody can cheat by calling the API
directly. I can also choose to play against an AI instead, which moves through the same server
rules. Show check, checkmate, and the move list.
```

Prompt for your agent (Team chat):

```text
Build me a Slack-style team chat with Rindle. Use the skill https://rindle-docs.preview.rocicorp.dev/skill.md.

Channels, messages, threads, and emoji reactions,
all updating live, with an unread count on each channel. Private channels and direct messages are
visible only to their members, and the server checks membership on every read and write.
```

Prompt for your agent (Auction):

```text
Build me a live auction site with Rindle. Use the skill https://rindle-docs.preview.rocicorp.dev/skill.md.

Sellers list an item with a starting price and an end
time, and bidders watch the price and bid history update live. The server decides which bids
count: a bid must beat the current high bid and arrive before the auction closes, so two people
bidding at once can't both win.
```

Prompt for your agent (Trivia):

```text
Build me a live trivia game with Rindle,
like Kahoot. Use the skill https://rindle-docs.preview.rocicorp.dev/skill.md.

A host creates a quiz and shares a join
code, and players join from their phones. The host moves through the questions, everyone answers
against a countdown, and the leaderboard updates live. The server scores the answers, and players
never receive the correct answer before the question closes.
```

Agents can also read these docs as [plain Markdown](https://rindle-docs.preview.rocicorp.dev/agents.md).

## By Hand

Download the `rindle` binary:

```bash
gh release download dev -R rocicorp/rindle -p install.sh -O - | sh
```

Start the database. It writes its `RINDLE_URL` to `.env`:

```bash
./rindle up
```

In another terminal, install the JS client:

```bash
gh release download dev -R rocicorp/rindle -p install.sh -O - | sh -s -- client
```

Then, from any app:

```typescript
import { createReactiveSqlClient } from "@rindle/reactive-sql-client";

const db = await createReactiveSqlClient({ rindle: { url: process.env.RINDLE_URL } });

// Applied once, by name. Re-running is a no-op; the live engine picks the change up.
await db.migrate("0001_init", `
  CREATE TABLE issue (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, closed INTEGER);
  CREATE TABLE comment (id INTEGER PRIMARY KEY AUTOINCREMENT, issue_id INTEGER, body TEXT);
`);

// Queries are trees -- `WITH` creates a subquery.
type Issue = { id: number; title: string; comments: { body: string }[] };
const open = await db.materialize<Issue>(`
  SELECT * FROM issue WHERE closed = 0 ORDER BY id DESC
  WITH comments OF comment ON issue_id = id (ORDER BY id DESC LIMIT 10)
`);

// Fires now, then after every commit that affects it.
// In React, useLiveQuery from @rindle/react does this for you.
open.subscribe((rows) => {
  console.clear();
  console.table(rows.map(({ id, title, comments }) => ({
    id, title, comments: comments.length, newest: comments[0]?.body,
  })));
});

// Plain SQL writes. Every copy writes to the same database, so AUTOINCREMENT ids never collide.
const { result } = await db.sql.execute(`INSERT INTO issue (title, closed) VALUES ('first', 0)`);
const issueId = result.lastInsertRowid;

const comments = Array.from({ length: 1000 }, (_, i) => ({
  sql: `INSERT INTO comment (issue_id, body) VALUES (?, ?)`,
  args: [issueId, `comment ${i}`],
}));
// Twenty to a transaction. Fifty commits land; the view never holds more than ten.
for (let i = 0; i < comments.length; i += 20) await db.sql.batch(comments.slice(i, i + 20));
```

Save that as `app.ts` and run it in two terminals:

```bash
node --env-file=.env app.ts
```

The second copy sees the first one's writes live, and its view never holds more than ten comments.

Next, see the [demos](https://rindle-docs.preview.rocicorp.dev/demos.md).

---

# Why Live Queries

In a normal database, you run a query and get a result. That result is instantly stale. Run a second query and it's stale too, but differently. The two results can disagree with each other, and there's no way to know.

Not a great start for high-quality software, but you accept it — it's just how databases work.

You build something. It has multiple users or agents collaborating. Everyone is changing the same data at the same time, and nobody knows what the others just did. Bad things happen.

Making this work properly is called "real-time collaboration," but it's really just solving that same inconsistency problem.

You could try to patch it:

- Re-run queries on a timer. That's expensive, and it still leaves gaps.
- Build a WebSocket system to tell the UI when important data changes. Now you get to maintain a whole separate live push system.

Most people decide to live with it. It's just how databases work.

## *Why* do databases work this way?

Because live queries are hard to do well.

The obvious approach is to re-run each query when its underlying data changes. But the queries worth watching are usually the ones whose data changes most, so you re-run them constantly. That gets expensive.

There are ways to fight this, but they often push the database toward custom query models and APIs, away from standard web architecture.

So most teams pick a traditional database and live with stale results, rather than opt into a walled garden.

## Live queries, no walls

Instead of re-running queries, what if we updated a query result precisely as the underlying data changed? This is called [Incremental View Maintenance](https://materializedview.io/p/everything-to-know-incremental-view-maintenance).

IVM engines like [Feldera](https://www.feldera.com/) and [Materialize](https://materialize.com/) exist today, but they're built for analytics: a small number of queries known up front over huge datasets. They trade lots of memory and slow startup for optimal incremental updates.

When we were building [Zero](https://zero.rocicorp.dev/), we needed something like this, but for application workloads. We wanted to sync large numbers of small dynamic queries.

So we built [ZVM](https://github.com/rocicorp/rindle/blob/branch/docs/ZVM.md). It can maintain thousands of small queries on a single core at 1,000 writes per second.

Until now, ZVM was an implementation detail of Zero — you couldn't use it on its own. Rindle factors it out into a standalone database. And because of IVM, we don't need a custom query model: it's just plain [SQL](https://rindle-docs.preview.rocicorp.dev/sql.md) with subscriptions. Use it as the basis of a [sync engine](https://rindle-docs.preview.rocicorp.dev/zero.md), or just use it as a database.

**Don't like SQL?**

You can of course use a [typed query builder](https://rindle-docs.preview.rocicorp.dev/query-builder.md) too. It's just syntax. The core idea is that Rindle is a traditional relational database.

No more stale data. No inconsistent results. Your UI updates when the data does, and collaborators see each other's changes live.

## Bigger than it sounds

Live queries sound like a minor feature. They're not. Beyond solving data inconsistency and collaboration, live queries are also the foundation for two of the biggest shifts in software today:

- **Sync.** Efficient live queries are the key to general-purpose sync, because they enable clients to sync only the subset of data they care about. Rindle directly supports [Zero](https://rindle-docs.preview.rocicorp.dev/zero.md), but it can just as easily back [TanStack DB](https://rindle-docs.preview.rocicorp.dev/tanstack-db.md) or any other sync engine.
- **Agents.** Long-lived agents need to react when data changes. Live queries wake them on the change, instead of having them poll or re-read everything.

## Why Rindle

We built ZVM because we needed it for Zero. But live queries shouldn't be a special feature of exotic databases. They should just be how data works.

[Get Started](https://rindle-docs.preview.rocicorp.dev/get-started.md)

---

# Demos

## Scale

Thousands of open queries. 1k writes/second. 1 GB of data. 1 vCPU.

![The Agent Desk demo: 4,100 live queries and 1,000 writes/second on one core](https://rindle-docs.preview.rocicorp.dev/scale.webp)

Running live, with the same page on two hosts in front of one shared backend:

- Vercel: [https://rindle-scale-demo.preview.rocicorp.dev/](https://rindle-scale-demo.preview.rocicorp.dev/)
- Cloudflare: [https://rindle-scale-demo.replicache.workers.dev/](https://rindle-scale-demo.replicache.workers.dev/)

Try it locally:

```bash
cd rust
cargo run -p rindle-scale-demo --profile release-server -- --no-guide
```

## Full-Stack Sync

Pair with TanStack DB or `@rindle/sync-client` for instantaneous client-first reads and writes
throughout your app.

Because the app controls precisely the data to sync with queries, the oversync problem common
with sync is elegantly avoided. The app can tune what it presyncs to balance startup time and
interaction speed.

![zbugs running on Rindle](https://rindle-docs.preview.rocicorp.dev/sync.webp)

Try it locally (needs Docker for Postgres; this config still uses a Postgres upstream):

```bash
pnpm install
pnpm --filter @rindle/zbugs build:runtime   # builds the Rindle binaries
cd apps/zbugs && pnpm dev                   # starts Postgres, the Rindle gateway + follower, and Vite on :5173
ZERO_UPSTREAM_DB='postgres://rindle:rindle@127.0.0.1:<port>/app' pnpm db-seed   # seed it, using the port `pnpm dev` printed
```

## Client-Side Live Queries

The core Rindle database is a 200KB library, gzip-compressed. It works on the web via wasm, or on
iOS and Android natively. It provides a full relational, embedded database with live queries that
answer and update in microseconds. Hook it to the Rindle server for a full, opinionated stack, or to
your own server however you want.

![The wasm canvas demo](https://rindle-docs.preview.rocicorp.dev/canvas.webp)

Try it locally: the demo lives in its own repo,
[rocicorp/wasm-canvas-demo](https://github.com/rocicorp/wasm-canvas-demo).

## Streaming Queries Against Any Change Source

Put Rindle downstream of your existing database, Kafka stream, whatever. Delete your websockets,
webhooks, and API. Agents can directly watch any relational query they want, efficiently. Rindle
becomes shared infra within your org that agents use to watch changing data.

![The Wikipedia firehose demo](https://rindle-docs.preview.rocicorp.dev/wiki.webp)

Rindle sits downstream of Wikimedia's live edit firehose. Every browser that opens attaches to
the same maintained boards, so N readers cost one pipeline, not N.

Try it locally: the data tier in one terminal, the UI in another.

```bash
cd apps/wiki-demo && pnpm dev       # boots the write-master/follower pair + ingests the live Wikimedia feed
cd product-page && pnpm dev         # then open http://localhost:3200/wiki
```

---

# Architecture

Rindle fits into your architecture like any other database. Clients typically don't talk to it
directly. Access goes through your API server, which enforces business logic and authorization.

The only difference is that Rindle has *sessions*. A session is a single streaming connection,
shared by all of a client's open queries. `addQuery` and `removeQuery` change which queries are
synced over it.

*Diagram: The client talks only to your API server. The client calls your API server: one route opens the Rindle stream, and the others are ordinary endpoints, such as one that watches the user's notes and one that shares a note. Your API server calls the Rindle server to connect, to add a query, and to execute writes. Rindle's stream flows back to the API server, which passes it through to the client.*

## How it works

1. **The page opens one stream.** The browser opens a stream to your server, at `/api/rindle-connect`. Your
   server checks the user with your normal auth and returns Rindle's stream unchanged. Platforms
   like Vercel and Cloudflare pipe it through without your code touching each byte.
2. **Each query is an endpoint.** Framework-specific bindings like `useLiveQuery()` call your
   endpoints. The endpoint checks the user, picks the SQL, and calls `rindle.addQuery`. Rindle
   runs the query and sends its rows down the stream the page already has open.
3. **Writes are endpoints too.** They check the user and run ordinary SQL. Every live query the
   write affects updates on its own, for every user who can see it.

*Diagram: One query and one write, step by step. The browser opens a stream through your server, and Rindle's stream comes back through it. The browser then calls a query endpoint, which checks the user, picks the SQL, and attaches it with rindle.addQuery. Rows arrive on the stream. Later the browser calls a write endpoint, which runs an UPDATE with an ownership check, and every affected query updates on the stream.*

## Setup

Your server needs one setting, `RINDLE_URL`, which `rindle up` prints and writes to `.env`.
Locally it's just an address. In production it also carries a password, like a Postgres URL
(`https://:password@db.example.com`), so keep it in your server's environment and never send it to
the browser.

A complete example, for both Next.js and Cloudflare Workers, is in
[`apps/notes`](https://github.com/rocicorp/rindle/tree/branch/apps/notes).

Install Rindle in your app:

```bash
gh release download dev -R rocicorp/rindle -p install.sh -O - | sh -s -- server react
```

Create the server's clients once, and import them from your routes:

```ts
// lib/rindle.ts — server only
import { createRindleSessions } from "@rindle/daemon-client";
import { createReactiveSqlClient } from "@rindle/reactive-sql-client";

export const rindle = createRindleSessions();                      // reads RINDLE_URL
export const db = await createReactiveSqlClient({ rindle: {} });   // for writes
```

### The stream route

One route per app. It authenticates the request and passes Rindle's stream through.

- `clientID` names one live client (each `createReactiveSqlClient` has its own), and `queryID` names
  one of its queries. The page sends them in the `Rindle-Client` and `Rindle-Query` headers. Each
  client has its own stream and its own queries.
- `userID` is optional. Rindle stores it with the client and rejects any later call for that
  client with a different `userID`. Without it, knowing the `clientID` is enough to change its
  queries, so pass it whenever there is a signed-in user.

**Next.js on Vercel** (`app/api/rindle-connect/route.ts`):

```ts
import { rindle } from "@/lib/rindle";
import { getUser } from "@/lib/auth"; // your auth

export const maxDuration = 800; // the stream reconnects when Vercel ends the function

export async function GET(request: Request) {
  const user = await getUser(request);
  if (!user) return new Response(null, { status: 401 });
  const clientID = request.headers.get("Rindle-Client");
  return rindle.connect({ clientID, userID: user.id });
}

export async function DELETE(request: Request) {
  const user = await getUser(request);
  if (!user) return new Response(null, { status: 401 });
  // the page stopped using a query
  const clientID = request.headers.get("Rindle-Client");
  const queryID = request.headers.get("Rindle-Query");
  return rindle.removeQuery({ clientID, queryID, userID: user.id });
}
```

**Cloudflare Workers** (`worker/index.ts`):

```ts
import { rindle } from "../lib/rindle";
import { getUser } from "../lib/auth"; // your auth

export default {
  async fetch(request: Request): Promise<Response> {
    const url = new URL(request.url);
    const user = await getUser(request);

    if (url.pathname === "/api/rindle-connect") {
      if (!user) return new Response(null, { status: 401 });
      const clientID = request.headers.get("Rindle-Client");
      const queryID = request.headers.get("Rindle-Query");
      if (request.method === "DELETE") {
        return rindle.removeQuery({ clientID, queryID, userID: user.id });
      }
      return rindle.connect({ clientID, userID: user.id });
    }
    // …your query and write routes, below
    return new Response("Not found", { status: 404 });
  },
};
```

### A query endpoint

A query endpoint looks like any other endpoint: authenticate, then decide what this user may see.
The difference is that it returns `rindle.addQuery(...)` instead of rows. The rows go down the stream.

This one returns the notes a user owns, plus the notes shared with them:

```ts
// app/api/notes/route.ts
import { rindle } from "@/lib/rindle";
import { getUser } from "@/lib/auth";

export async function GET(request: Request) {
  const user = await getUser(request);
  if (!user) return new Response(null, { status: 401 });

  const clientID = request.headers.get("Rindle-Client");
  const queryID = request.headers.get("Rindle-Query");
  return rindle.addQuery({ clientID, queryID, userID: user.id }, `
    SELECT * FROM note
     WHERE owner = ?
        OR EXISTS (SELECT 1 FROM share WHERE share.note_id = note.id AND share.user_id = ?)
     ORDER BY id DESC
     LIMIT 50
  `, [user.id, user.id]);
}
```

Read arguments from the request the way you normally would, and pass them as parameters:

```ts
// app/api/notes/[id]/comments/route.ts
export async function GET(request: Request, { params }: { params: Promise<{ id: string }> }) {
  const user = await getUser(request);
  if (!user) return new Response(null, { status: 401 });
  const { id } = await params;

  const clientID = request.headers.get("Rindle-Client");
  const queryID = request.headers.get("Rindle-Query");
  return rindle.addQuery({ clientID, queryID, userID: user.id }, `
    SELECT comment.* FROM comment
     WHERE note_id = ?
       AND EXISTS (SELECT 1 FROM note WHERE note.id = comment.note_id AND note.owner = ?)
     ORDER BY id
  `, [Number(id), user.id]);
}
```

If the SQL is not something Rindle can keep live, `rindle.addQuery` returns a `400` with the
compiler's message, and the page sees it as the query's `error`.

### Use it in the page

Create one live client per signed-in page, and close it on sign-out:

```tsx
"use client";
import { useEffect, useState } from "react";
import { createReactiveSqlClient, type ReactiveStreamClient } from "@rindle/reactive-sql-client";
import { useLiveQuery } from "@rindle/react";

function useStreamClient(): ReactiveStreamClient | null {
  const [db, setDb] = useState<ReactiveStreamClient | null>(null);
  useEffect(() => {
    let client: ReactiveStreamClient | undefined;
    let closed = false;
    void createReactiveSqlClient({ stream: "/api/rindle-connect" }).then((c) => {
      if (closed) return c.close();
      client = c;
      setDb(c);
    });
    return () => {
      closed = true;
      client?.close();
    };
  }, []);
  return db;
}

type Note = { id: number; title: string; owner: string };

export function Notes() {
  const db = useStreamClient();
  const { rows, status, error } = useLiveQuery<Note>(db, "/api/notes");
  if (error) return <p>Couldn't load notes: {error.message}</p>;
  if (status === "loading") return <p>Loading…</p>;
  return <ul>{rows.map((n) => <li key={n.id}>{n.title}</li>)}</ul>;
}
```

The client opens the stream the first time a component asks for a query, and one stream carries
every query on the page. Render the signed-in part of the page with `key={user.id}`, so a
different user gets a fresh client. If your endpoint answers `401` or `403`, that status shows up as `error`.
Unmounting the component removes the query.

### A write endpoint

Writes are ordinary endpoints that run SQL with your server's client. Put the ownership check in
the SQL, so a user can never change a row they don't own, then check how many rows changed:

```ts
// app/api/notes/[id]/share/route.ts
import { db } from "@/lib/rindle";
import { getUser } from "@/lib/auth";

export async function POST(request: Request, { params }: { params: Promise<{ id: string }> }) {
  const user = await getUser(request);
  if (!user) return new Response(null, { status: 401 });
  const { id } = await params;
  const { userId } = await request.json();

  const { result } = await db.sql.execute({
    sql: `INSERT INTO share (note_id, user_id)
          SELECT id, ? FROM note WHERE id = ? AND owner = ?`,
    args: [userId, Number(id), user.id],
  });
  if (result.rowsAffected === 0) return new Response(null, { status: 403 });
  return new Response(null, { status: 204 });
}
```

The moment that row lands, the note appears in the other user's `/api/notes` results, on any page
they have open. You don't notify anyone; the query does it.

## Authorization / Permissions

You already know how to implement authorization – it's just an API endpoint that adds a query.
There is no separate permission system, and no need for one. Your endpoints use ordinary business
logic to decide which query to run:

```ts
// app/api/notes/route.ts
export async function GET(request: Request) {
  const user = await getUser(request);
  if (!user) return new Response(null, { status: 401 });

  const ids = {
    clientID: request.headers.get("Rindle-Client"),
    queryID: request.headers.get("Rindle-Query"),
    userID: user.id,
  };
  if (user.isAdmin) {
    return rindle.addQuery(ids, `SELECT * FROM note ORDER BY id DESC`, []);
  }
  return rindle.addQuery(ids, `SELECT * FROM note WHERE owner = ? ORDER BY id DESC`, [user.id]);
}
```

## More authorization examples

| Rule | SQL |
|---|---|
| Only your own rows | `WHERE owner = ?` |
| Rows shared with you | `OR EXISTS (SELECT 1 FROM share WHERE share.note_id = note.id AND share.user_id = ?)` |
| Rows in your teams | `WHERE EXISTS (SELECT 1 FROM member WHERE member.team_id = doc.team_id AND member.user_id = ?)` |
| Hide a column | Select only the columns this user may see: `SELECT id, title FROM …`, not `SELECT *`. Only the selected columns reach the browser. |
| Different rules per role | Pick a different query in the endpoint: `if (user.isAdmin) …` |

Always pass the user's id as a parameter from your server. Never take it from the request body or
URL.

## Authorization is Live

Note that because authorization in Rindle is based on live queries, the resulting authorization
logic is also live. When a note is unshared, it disappears from the other user's open page right away, because their query no longer matches it.
The same holds for anything the rule can see: team membership, a role column, an `archived` flag.

Some rules live outside your data: a user logs out, gets banned, or has a role your auth provider
holds. For those, end the user's streams:

```ts
await rindle.disconnect({ userID: user.id });
```

That stops every query for that user, in every tab. Their pages reconnect, your stream route checks
them again, and anything they are no longer allowed to see stays gone. `rindle.disconnect({ userID, clientID })`
disconnects one client.

## Deploying

- **Keep Rindle private.** Only your servers need its address. Keep `RINDLE_URL` in your
  server's environment.
- **Vercel:** set `maxDuration` on the stream route. When Vercel ends the function, the stream
  reconnects and the page's queries resume without calling your endpoints again.
- **Cloudflare:** the stream route returns Rindle's response as is, so the Worker uses almost no CPU
  while a page is open. Add `"compatibility_flags": ["nodejs_compat"]` to your wrangler config so the
  Worker can read `RINDLE_URL`.

---

# Server-Side Rendering

Components use the same `useLiveQuery` hook on the server and in the browser. The server calls
your query endpoints, renders the rows, and puts them in the page. The browser shows the page,
then keeps the same queries live. It doesn't call your endpoints again.

Server-rendered pages hold one user's data. Serve them with `Cache-Control: private, no-store`
so a CDN never shares them.

## How it works

1. **The server render is a client.** It opens a stream to Rindle and calls your query endpoints
   as the signed-in user, forwarding their cookies. Your endpoints check the user and call
   `rindle.addQuery`, exactly as they do for the browser.
2. **Hooks wait for their rows.** `useLiveQuery` suspends on the server until its query's rows
   arrive, then renders them.
3. **The page carries the queries.** Along with the HTML, the page includes each query's data and
   the ID of the client that holds them.
4. **The browser takes over.** It builds its views from the page, so hydration matches the server's
   HTML, then opens the stream as the same client. Changes continue from where the server stopped.

If the browser takes too long to take over, Rindle drops the server's queries. The browser then
calls your endpoints as usual, and keeps showing the server's rows until fresh ones arrive.

## Setup

SSR builds on the stream route and query endpoints from [Architecture](https://rindle-docs.preview.rocicorp.dev/architecture.md). They
don't change.

### Components

Get `db` from a provider instead of importing it:

```tsx
import { useLiveQuery } from "@rindle/react";

type Note = { id: number; title: string };

export function Notes() {
  const { rows, status, error } = useLiveQuery<Note>("/api/notes");
  if (error) return <p>Couldn't load notes: {error.message}</p>;
  if (status === "loading") return <p>Loading…</p>;
  return <ul>{rows.map((n) => <li key={n.id}>{n.title}</li>)}</ul>;
}
```

The server renders many users' pages at once, so each request gets its own `db`. The provider is
how a component finds it. A browser-only app can keep passing `db` directly.

### The server entry

Create an SSR client for the request, render, then add its script to the page:

```tsx
import { createRindleSessions, createSSRClient } from "@rindle/reactive-sql-client";
import { RindleProvider } from "@rindle/react";
import { renderToReadableStream } from "react-dom/server";

const rindle = createRindleSessions(); // reads RINDLE_URL

export async function renderPage(request: Request): Promise<Response> {
  const user = await getUser(request); // your auth
  const db = createSSRClient({ rindle, request, userID: user?.id });

  const stream = await renderToReadableStream(
    <RindleProvider client={db}>
      <App />
    </RindleProvider>,
  );
  await stream.allReady;
  const app = await new Response(stream).text();

  return new Response(
    `<!doctype html>
     <div id="root">${app}</div>
     <script>${db.dehydrateScript()}</script>
     <script type="module" src="/client.js"></script>`,
    {
      headers: {
        "Content-Type": "text/html",
        "Cache-Control": "private, no-store",
      },
    },
  );
}
```

Call `dehydrateScript()` once, after the render is done.

| Option | |
|---|---|
| `rindle` | Your `createRindleSessions()` object |
| `request` | The page request. Endpoints resolve against its origin, and its `cookie` and `authorization` headers are forwarded to them |
| `userID` | Optional. The same `userID` your endpoints pass to `rindle.addQuery` |
| `fetch` | How endpoints are called. Defaults to the global `fetch`. Pass your app's own handler (a Worker's `fetch`, Hono's `app.fetch`) to call them in-process |
| `timeout` | How long a query may take, in ms. Default 2,000 |

### The browser entry

Create `db` from the page, then hydrate:

```tsx
import { createReactiveSqlClient } from "@rindle/reactive-sql-client";
import { RindleProvider } from "@rindle/react";
import { hydrateRoot } from "react-dom/client";

const db = await createReactiveSqlClient({ stream: "/api/rindle-connect", hydrate: true });

hydrateRoot(
  document.getElementById("root")!,
  <RindleProvider client={db}>
    <App />
  </RindleProvider>,
);
```

On a page rendered without SSR, `hydrate: true` does nothing, so the same entry works everywhere.

## Loading and errors

- **Slow queries.** A query with no rows by `timeout` renders `status: "loading"`. It stays live,
  and its rows arrive in the browser.
- **Refused queries.** If an endpoint answers `401` or `403`, the hook renders `error` on the
  server, and the browser renders the same error.
- **`renderToString`** doesn't wait for queries. Components render their `<Suspense>` fallback, and
  the browser fills them in. Use a streaming renderer that waits, like `renderToReadableStream`
  with `allReady`.

SSR needs React 19. Browser-only rendering works on React 18.

## Frameworks

- **Hono, Cloudflare Workers, Express, or your own server:** as above. Pass your app's handler as
  `fetch` so endpoint calls stay in the same process.
- **React Router 7, Remix, TanStack Start:** create the SSR client and wrap the app in
  `RindleProvider` in the server entry (`entry.server.tsx`), which receives the request.
- **Next.js (App Router):** see below.

### Next.js

Next.js renders client components on the server without the request, and a server component
can't hand a client to them as a prop. So the root layout, which has the request, creates the SSR
client and parks it under an id; a client component picks it up by that id during the server
render, and writes the page's script with `useServerInsertedHTML`.

```tsx
// lib/render.ts — server only
import { headers } from "next/headers";
import { after, connection } from "next/server";
import { createSSRClient, type SSRClient } from "@rindle/reactive-sql-client";
import { rindle } from "./rindle";

const g = globalThis as { __rindleRenders?: Map<string, SSRClient> };
const renders = (g.__rindleRenders ??= new Map());

// `userID` is the same id the stream route passes to `rindle.connect` (undefined for a public page).
export async function beginRender(userID?: string): Promise<string> {
  await connection(); // render per request, never at build time
  const incoming = await headers();
  const proto = incoming.get("x-forwarded-proto") ?? "http";
  const request = new Request(`${proto}://${incoming.get("host")}/`, { headers: incoming });
  const db = createSSRClient({ rindle, request, userID });
  const id = crypto.randomUUID();
  renders.set(id, db);
  // Once the response is sent, wait for the browser to take the queries over, then forget them.
  after(async () => {
    db.dehydrateScript();
    await Promise.race([db.handedOff, new Promise((r) => setTimeout(r, 10_000))]);
    renders.delete(id);
  });
  return id;
}
```

```tsx
// app/layout.tsx
import { beginRender } from "@/lib/render";
import { currentUser } from "@/lib/auth"; // however your endpoints read the signed-in user
import { RindleRoot } from "./RindleRoot";

export default async function RootLayout({ children }: { children: React.ReactNode }) {
  const ssrID = await beginRender((await currentUser())?.id);
  return (
    <html lang="en">
      <body>
        <RindleRoot ssrID={ssrID}>{children}</RindleRoot>
      </body>
    </html>
  );
}
```

```tsx
// app/RindleRoot.tsx
"use client";
import { useRef, useState, type ReactNode } from "react";
import { useServerInsertedHTML } from "next/navigation";
import { RindleProvider } from "@rindle/react";
import { createReactiveStreamClient, type SSRClient } from "@rindle/reactive-sql-client";

// One browser client per page load. Kept outside React state, because React's development Strict
// Mode runs state initializers twice, and a second hydrating client would take over the queries.
let browserClient: ReturnType<typeof createReactiveStreamClient> | undefined;

export function RindleRoot({ ssrID, children }: { ssrID: string; children: ReactNode }) {
  const [state] = useState(() => {
    if (typeof window !== "undefined") {
      browserClient ??= createReactiveStreamClient({ stream: "/api/rindle-connect", hydrate: true });
      return { browser: browserClient };
    }
    const db = (globalThis as { __rindleRenders?: Map<string, SSRClient> }).__rindleRenders!.get(ssrID)!;
    // Count queries still loading, so the script is written once they have all arrived.
    const loads = new Map<string, Promise<void>>();
    let pending = 0;
    const client = {
      peek: db.peek,
      load(endpoint: string) {
        let p = loads.get(endpoint);
        if (!p) {
          pending++;
          p = db.load(endpoint).finally(() => pending--);
          loads.set(endpoint, p);
        }
        return p;
      },
    };
    return { server: { db, client, pending: () => pending } };
  });

  const written = useRef(false);
  useServerInsertedHTML(() => {
    const server = state.server;
    if (!server || written.current || server.pending() > 0) return null;
    written.current = true;
    return <script dangerouslySetInnerHTML={{ __html: server.db.dehydrateScript() }} />;
  });

  return <RindleProvider client={state.browser ?? state.server!.client}>{children}</RindleProvider>;
}
```

Pages then call `useLiveQuery("/api/notes")` in client components, with no `<Suspense>` around
the live queries. A complete example, with a Playwright test, is
[`apps/ssr`](https://github.com/rocicorp/rindle/tree/branch/apps/ssr).

## Costs

- **Each server-rendered page view holds its queries briefly**, until the browser takes over. That
  includes crawlers and link previews. Skip the provider for known bots if that matters.
- **The page grows by the data it shows.** About the size of the rows themselves.

---

# Full-stack sync, with Zero

> **Coming soon.** This is not available yet. The page is a sketch, here for review.

Rindle directly supports the [Zero](https://zero.rocicorp.dev/) sync framework. Your schemas, queries, mutators and
`useQuery` components stay the same. The only thing that changes is performance – Rindle is [much faster](https://rindle-docs.preview.rocicorp.dev/performance.md#vs-zero).

## Architecture

With Zero, the browser talks to zero-cache, which calls your server and follows Postgres. With
Rindle, the browser talks to your server, and your server talks to Rindle, the same shape as
[Architecture](https://rindle-docs.preview.rocicorp.dev/architecture.md). Postgres is optional.

*Diagram: A Zero app on Rindle. The Zero client opens a stream through your API server, which connects to the Rindle server; Rindle's stream flows back through your API server to the client. The client's /query calls become rindle.addQuery on the Rindle server. Its /mutate calls go to your API server, which writes either to Rindle or, optionally, to Postgres. When Postgres is used, Rindle follows its changes.*

## Client

Change one import. `cacheURL` points at your server instead of zero-cache.

```diff
-import { Zero } from "@rocicorp/zero";
+import { Zero } from "@rindle/zero";

 const zero = new Zero({ schema, mutators, cacheURL: "/api/zero", auth, userID });
```

React components keep `useQuery` from `@rindle/zero/react`.

## Server

Your `/query` and `/mutate` endpoints keep their auth, their routes, and the bodies of every query
and mutator. Only the glue changes:

```diff
-import { handleMutateRequest, handleQueryRequest } from "@rocicorp/zero/server";
+import { createZeroApiServer } from "@rindle/zero/server";
+
+const api = createZeroApiServer({ schema, queries, mutators }); // reads RINDLE_URL

 export async function POST(request: Request) {
   const user = await getUser(request); // your auth
-  return handleQueryRequest(/* … */);
+  return api.handleQuery(request, { user });
 }
```

## Your data

- **Rindle is the database.** Run `rindle up` and write migrations in SQL. Mutators write to
  Rindle.
- **Postgres stays the database.** Mutators keep writing to Postgres through your existing
  connection (drizzle, kysely, prisma, postgres.js). Rindle follows it with logical replication.

---

# TanStack DB

> **Sketch.** Nothing on this page is built yet. It is here for review.

[TanStack DB](https://tanstack.com/db) keeps collections of rows in the browser and runs live
queries over them. Rindle can fill those collections: each collection is one of your query
endpoints, kept live by Rindle.

Install:

```sh
npm i @rindle/tanstack-db @tanstack/react-db
```

## A collection

Point a collection at a query endpoint, the same endpoints `useLiveQuery` uses
(see [Architecture](https://rindle-docs.preview.rocicorp.dev/architecture.md)):

```ts
import { createCollection } from "@tanstack/react-db";
import { createReactiveSqlClient } from "@rindle/reactive-sql-client";
import { rindleCollectionOptions } from "@rindle/tanstack-db";

const db = await createReactiveSqlClient({ stream: "/api/rindle-connect" });

type Note = { id: number; title: string; owner: string };

export const notes = createCollection(
  rindleCollectionOptions<Note>({
    db,
    query: "/api/notes",
    getKey: (note) => note.id,
  }),
);
```

The collection is ready when the query's first rows arrive. After that, every write that changes
the result updates the collection.

Query it with TanStack DB as usual:

```tsx
import { useLiveQuery } from "@tanstack/react-db";

export function Notes() {
  const { data } = useLiveQuery((q) => q.from({ note: notes }).orderBy(({ note }) => note.id, "desc"));
  return <ul>{data.map((n) => <li key={n.id}>{n.title}</li>)}</ul>;
}
```

## Writes

Writes go to your own endpoints. Return Rindle's write receipt from the endpoint, and pass it to
`awaitWrite`. TanStack DB shows the change immediately and keeps it until Rindle's stream
includes it:

```ts
export const notes = createCollection(
  rindleCollectionOptions<Note>({
    db,
    query: "/api/notes",
    getKey: (note) => note.id,
    onUpdate: async ({ transaction, collection }) => {
      for (const { modified } of transaction.mutations) {
        const res = await fetch(`/api/notes/${modified.id}`, {
          method: "POST",
          body: JSON.stringify({ title: modified.title }),
        });
        await collection.utils.awaitWrite(await res.json());
      }
    },
  }),
);
```

## How loading works

TanStack DB implements joins and `LIMIT` in a way that causes waterfalls. Rindle can do better,
but the APIs in TanStack DB need to be changed to take advantage of this capability.

Rindle keeps a whole query live on the server: joins, `EXISTS`, and `ORDER BY … LIMIT`, including
refilling the window when rows leave it. The browser receives the result on one stream.

TanStack DB works per collection. When it loads data on demand:

- **Joins are a waterfall.** It loads one side of a join, computes the join keys in the browser,
  then requests the matching rows of the other side. Each level of join is another round trip.
- **`LIMIT` refills are requests.** When rows leave a limited window, it asks for the next page
  with a cursor or offset.

So a TanStack DB query that joins or limits across collections makes several requests, even
though Rindle could have answered it in one.

You can avoid this by doing the join or limit in the endpoint's SQL instead. A Rindle collection
loads its whole query up front, so TanStack DB never asks it for more rows: Rindle keeps the
joined, limited result live on the server and refills the window itself. For example, an endpoint
that returns the newest 50 notes, each with its comments as an array:

```sql
SELECT * FROM note ORDER BY id DESC LIMIT 50
  WITH comments OF comment ON note_id = id (ORDER BY id)
```

The cost is flexibility: the join and the limit are fixed by the endpoint. A TanStack DB query
over that collection can still filter and sort the rows it holds, but a join or limit written in
TanStack DB across two collections goes back to loading per collection.

## Full queries on the server

It should be possible to change TanStack DB to work more like
[Zero](https://zero.rocicorp.dev): send the whole query to the server and receive its rows for
every collection it touches, with no waterfall. Rindle already streams results this way. This
hasn't been investigated deeply.

---

# Bring Your Own Database

> Keep your existing database as the source of truth

> **Preview.** This page describes an API that is not in a release yet. It is here for review.

By default, Rindle is your database. In **follow mode** it sits in front of one you already have.
Your app keeps writing to Postgres, Rindle follows its changes, and live queries update as those
writes land. Zero works the same way.

Use follow mode when you can't move off your database, or when other systems write to it too:
cron jobs, admin tools, other services. If you're starting fresh, let Rindle be the database.
There are fewer moving parts, and no replication lag.

## How it works

1. **Rindle copies your tables.** On first start it takes a snapshot of the tables you publish.
2. **Rindle follows the replication stream.** Each committed transaction in your database becomes
   one transaction in Rindle, in the same order.
3. **Queries work as usual.** Your endpoints call `rindle.addQuery`, exactly as in
   [Architecture](https://rindle-docs.preview.rocicorp.dev/architecture.md). Rows stream to the client and update when your database
   changes.
4. **Writes go to your database.** Your write endpoints keep using your existing database client.
   Rindle never writes to your database.

## Postgres

Requires Postgres 15 or newer with `wal_level = logical`, a direct connection (not a pooler), and
a role with `REPLICATION`.

Start Rindle in follow mode:

```sh
./rindle up --follow postgres://rindle@localhost/app
```

Rindle creates a publication and a replication slot, copies the tables, then follows. It prints
`RINDLE_URL` and `RINDLE_DATABASE_TOKEN` as usual.

To follow only some tables:

```sh
./rindle up --follow postgres://rindle@localhost/app --tables issue,comment
```

### What gets copied

Every published table, its primary key, and its plain b-tree indexes. Each table needs a primary
key, or a unique index on `NOT NULL` columns.

| Postgres | Rindle |
| --- | --- |
| `bool` | boolean |
| `smallint`, `integer`, `bigint` | integer |
| `real`, `double precision` | float |
| `numeric` | string (exact decimal text) |
| `text`, `varchar`, `uuid`, enums | string |
| `json`, `jsonb`, arrays | json |
| `date`, `timestamp`, `timestamptz` | string (Postgres text form) |

### Schema changes

Migrate Postgres with whatever tool you use today. Rindle picks up the change from the stream.

### Managed providers

Neon, Supabase, RDS, Aurora, Cloud SQL and Azure work. Two things trip people up:

- **Poolers.** Connect to the primary directly. Neon's `-pooler` host and Supabase's port 6543 are
  refused.
- **Row-level security.** The role Rindle connects as needs `BYPASSRLS`, or it copies only the rows
  its policies allow. Supabase turns RLS on by default.

### Reading your own writes

Rindle lags your database by replication delay. A write your endpoint just
made can be missing from a query result for that long. Rindle tells the client when it has
caught up to a write, so optimistic updates don't flicker.

## Other databases

**Future work.** Postgres is the only upstream today. This section describes where we're going.

Rindle doesn't need to know about your database. Anything that can produce an ordered stream of
committed changes can be an upstream. You write a small **adapter** that speaks Rindle's change
protocol, and Rindle handles the copy, the live queries, and recovery.

Rindle connects to your adapter, over WebSocket, and asks for changes after the last position it
has. Your adapter provides:

1. **A snapshot**: every row of every table, as of some position.
2. **Committed transactions** after that position, in commit order.
3. **Resume**: given a position Rindle has acknowledged, continue from there.

```ts
type Position = string; // opaque to Rindle, ordered by you

interface Adapter {
  // First start, or when Rindle's position is too old to resume from.
  snapshot(): AsyncIterable<SnapshotMessage>;
  // Every later connect, from the last position Rindle acknowledged.
  changes(from: Position): AsyncIterable<ChangeMessage>;
  // Rindle has applied everything up to here. You can release it.
  ack(position: Position): void;
}

type SnapshotMessage =
  | { type: "schema"; tables: Table[] }
  | { type: "rows"; table: string; rows: Row[] }
  | { type: "done"; position: Position };

type ChangeMessage =
  | { type: "begin" }
  | { type: "insert"; table: string; row: Row }
  | { type: "update"; table: string; key: Key; row: Row }
  | { type: "delete"; table: string; key: Key }
  | { type: "truncate"; table: string }
  | { type: "schema"; tables: Table[] }
  | { type: "commit"; position: Position };

type Table = {
  name: string;
  columns: { name: string; type: "integer" | "float" | "boolean" | "string" | "json" }[];
  primaryKey: string[];
};
```

The rules:

- Send only committed changes. Nothing between `begin` and `commit` is visible until `commit`.
- Positions only go up.
- Every table has a primary key.
- If you can't resume from a position, close the stream. Rindle takes a fresh snapshot.

---

# Embedded

> Use Rindle as an embedded database

> **Coming soon.** This page describes an API that is not in a release yet. It is here for review.

Rindle can be used as a standalone embedded database, for example as part of a client-side
application.

The API is the same everywhere: `migrate` to change the schema, `materialize` a `SELECT` and
subscribe to it, and write with plain SQL.

## Browser

```bash
npm i @rindle/wasm
```

```typescript
import { openDatabase } from "@rindle/wasm";

// Stored in OPFS. It survives reloads.
const db = await openDatabase("app.db");

await db.migrate("0001_init", `
  CREATE TABLE issue (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, closed INTEGER);
`);

type Issue = { id: number; title: string };
const open = db.materialize<Issue>(
  `SELECT id, title FROM issue WHERE closed = ? ORDER BY id DESC`, [0],
);

// Fires now, then after every commit that affects it.
open.subscribe((rows) => console.table(rows));

await db.sql.execute({ sql: `INSERT INTO issue (title, closed) VALUES (?, ?)`, args: ["first", 0] });
```

In React, `useLiveQuery(db, sql, params)` from `@rindle/react` does the subscribing for you.

## Node

```bash
npm i @rindle/replica
```

```typescript
import { openDatabase } from "@rindle/replica";

// A SQLite file on disk.
const db = await openDatabase("app.db");

await db.migrate("0001_init", `
  CREATE TABLE issue (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, closed INTEGER);
`);

const open = db.materialize<{ id: number; title: string }>(
  `SELECT id, title FROM issue WHERE closed = ? ORDER BY id DESC`, [0],
);
open.subscribe((rows) => console.table(rows));

await db.sql.execute({ sql: `INSERT INTO issue (title, closed) VALUES (?, ?)`, args: ["first", 0] });
```

## React Native

```bash
npm i @rindle/react-native @rindle/react
```

```tsx
import { openDatabase } from "@rindle/react-native";
import { useLiveQuery } from "@rindle/react";

// A SQLite file in the app's documents directory.
const db = await openDatabase("app.db");

await db.migrate("0001_init", `
  CREATE TABLE issue (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, closed INTEGER);
`);

function Issues() {
  const { rows } = useLiveQuery<{ id: number; title: string }>(
    db, `SELECT id, title FROM issue WHERE closed = ? ORDER BY id DESC`, [0],
  );
  return rows.map((i) => <Text key={i.id}>{i.title}</Text>);
}

await db.sql.execute({ sql: `INSERT INTO issue (title, closed) VALUES (?, ?)`, args: ["first", 0] });
```

## iOS

Add `https://github.com/rocicorp/rindle` as a Swift package.

```swift
import Rindle

let db = try Database.open(appSupport.appendingPathComponent("app.db"))

try db.migrate("0001_init", """
  CREATE TABLE issue (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, closed INTEGER);
""")

struct Issue: Decodable { let id: Int; let title: String }
let open = try db.materialize(Issue.self,
  "SELECT id, title FROM issue WHERE closed = ? ORDER BY id DESC", [0])

let sub = open.subscribe { rows in print(rows) }

try db.sql.execute("INSERT INTO issue (title, closed) VALUES (?, ?)", ["first", 0])
```

In SwiftUI, a `LiveQuery` is `Observable`: read `open.rows` in a view and it re-renders on commit.

## Android

```kotlin
// build.gradle.kts
implementation("sh.rindle:rindle-android:0.1.0")
```

```kotlin
import sh.rindle.Database

val db = Database.open(File(context.filesDir, "app.db"))

db.migrate("0001_init", """
  CREATE TABLE issue (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, closed INTEGER);
""")

data class Issue(val id: Long, val title: String)
val open = db.materialize<Issue>(
  "SELECT id, title FROM issue WHERE closed = ? ORDER BY id DESC", listOf(0))

open.subscribe { rows -> println(rows) }

db.sql.execute("INSERT INTO issue (title, closed) VALUES (?, ?)", listOf("first", 0))
```

In Compose, `open.rows` is a `StateFlow`: `val rows by open.rows.collectAsState()`.

## Rust

```bash
cargo add rindle
```

```rust
use rindle::Database;

let db = Database::open("app.db")?;

db.migrate("0001_init", "
  CREATE TABLE issue (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, closed INTEGER);
")?;

#[derive(serde::Deserialize, Debug)]
struct Issue { id: i64, title: String }

let open = db.materialize::<Issue>(
    "SELECT id, title FROM issue WHERE closed = ? ORDER BY id DESC", (0,))?;

open.subscribe(|rows| println!("{rows:?}"));

db.sql().execute("INSERT INTO issue (title, closed) VALUES (?, ?)", ("first", 0))?;
```

## Any SQLite

Rindle is also a SQLite extension. Load it into any SQLite host and subscribe with SQL:

```sql
.load ./rindle

SELECT id, snapshot FROM rindle_subscribe(
  'SELECT id, title FROM issue WHERE closed = 0 ORDER BY id DESC');

INSERT INTO issue (title, closed) VALUES ('first', 0);

-- The changes since the last read, one row per commit.
SELECT seq, delta FROM rindle_changes(1);
```

## Adding a Server

The same SQL runs against the Rindle server. When you need many clients on one database, start
with [Get Started](https://rindle-docs.preview.rocicorp.dev/get-started.md).

---

# Cloud Rindle

The easiest way to deploy Rindle is with Cloud Rindle.

## Deploy

Sign in. This opens a browser to confirm a code:

```bash
./rindle login
```

Then, from your project directory:

```bash
./rindle deploy
```

It creates the database, waits until it's ready, and prints how to reach it:

```bash
RINDLE_URL=https://app-7f3k2q.rindle.cloud
RINDLE_DATABASE_TOKEN=rdt_…
```

Set both in your server's environment, wherever it runs. Keep the token server-side.

Running `deploy` again updates the same database. The project is bound to it in
`.rindle/cloud.json`, which holds no secrets and is safe to commit.

## Pricing

See [rindle.sh/pricing](https://rindle.sh/pricing).

## Regions

Databases run in Gravelines, France. More regions are coming.

## Other commands

| Command | Does |
|---|---|
| `./rindle whoami` | Shows the signed-in account |
| `./rindle logout` | Signs out and deletes the saved credential |
| `./rindle link <app-id>` | Binds the project to a database created in the dashboard |
| `./rindle migrate apply --cloud` | Applies `migrations/*.sql` |

Logs, metrics, resizing, and deleting are in the dashboard at
[cloud.rindle.sh](https://cloud.rindle.sh).

---

# Self-Hosted

> How Rindle runs server-side, and how it scales

Rindle is one binary, `rindle`. On a single machine it is one server process and one SQLite file.
To scale, it splits into a write master and any number of read followers, the way Postgres
scales reads with replicas, except that followers also run the live queries.

This is the same architecture Cloud Rindle runs.

## Single server

The simplest deployment, and what `./rindle up` runs locally. Write a config:

```json
{
  "db": "/var/lib/rindle/data.db",
  "standalone": true,
  "httpPort": 7600,
  "bindHost": "0.0.0.0",
  "authToken": "…",
  "sqlAuthToken": "…"
}
```

And start it:

```bash
rindle serve --config rindle.json
```

One process serves reads, writes, migrations, and live queries on one port. The data is a
single SQLite file in wal2 mode, with one
writer and a pool of query workers, one per CPU by default.

A single server has no built-in replication. Back it up with volume or filesystem snapshots. To
move to the replicated setup later, export and import.

## Replicated

*Diagram: The replicated deployment. Your API server talks to the edge. The edge sends writes to the write master and assigns each session to one of several followers. The master streams its journal to every follower and ships it to object storage. New followers restore from object storage, then catch up from the master.*

| Process | Command | Does |
|---|---|---|
| Write master | `rindle replicator` | Commits every write and keeps the journal of changes |
| Follower | `rindle serve` | Holds a full replica, serves reads, and runs live queries |
| Edge | `rindle edge` | The one public endpoint. Routes writes to the master and sessions to followers |
| Backup producer | `rindle backup producer` | Turns the archived journal into periodic snapshots |

Your app talks only to the edge, with the same `RINDLE_URL` and client code as a single server.

### Writes

All writes go to the master. Its storage is SQLite's HCTree backend, which runs many `BEGIN CONCURRENT` transactions in parallel with snapshot isolation, checking for conflicts
at commit. Each commit gets a sequence number, and its journal entry is written in the same
transaction as the data, so the journal can never disagree with the database.

The master streams the journal to every follower and ships it to object storage. A fencing
token stops a master that has been replaced from accepting writes.

### Reads and live queries

Each follower holds a full copy of the database. It tails the master's journal, applies each
commit, and pushes the change through its live queries, so subscribers get only the rows that
changed. A query's state is shared by every subscriber to it, and queries are spread across a
pool of worker threads.

A follower that restarts resumes from the last commit it applied. A new follower, or one that
fell too far behind, restores the latest snapshot from object storage and catches up from there.
It reports not-ready, and takes no traffic, until it has caught up.

Followers are the unit of scale. Add one to add capacity for reads and live queries; the master
and the other followers don't change.

### Sessions

A session lives on one follower. When a client connects, the edge assigns it a follower,
balancing load across them, and signs a short-lived ticket so that every later request for that
session reaches the same follower.

## Durability

The master ships its journal to object storage (S3, R2, MinIO, or a filesystem) continuously.
The backup producer turns it into daily snapshots and keeps the last seven. Together they give
point-in-time restore to any commit in that window:

```bash
rindle backup ls
rindle backup restore-master --source main --out master.db --at 1791500000000
rindle backup restore-follower --source main --out follower.db --cid 4812
```

Every snapshot the producer writes is itself a full restore of the archive, so restores are
tested continuously. See [Status](https://rindle-docs.preview.rocicorp.dev/status.md) for the one durability gap that remains.

## Running it

Master:

```json
{
  "db": "/var/lib/rindle/master.db",
  "name": "main",
  "httpPort": 7611,
  "wsPort": 7610,
  "bindHost": "0.0.0.0",
  "authToken": "…",
  "sqlAuthToken": "…",
  "backup": { "type": "s3", "generation": "prod-1" }
}
```

```bash
rindle replicator --config master.json
```

Each follower:

```json
{
  "db": "/var/lib/rindle/follower.db",
  "httpPort": 7600,
  "bindHost": "0.0.0.0",
  "authToken": "…",
  "sources": [{
    "kind": "replicator",
    "name": "main",
    "url": "ws://master.internal:7610/subscribe",
    "backup": { "type": "s3" }
  }]
}
```

```bash
rindle serve --config follower.json
```

The edge lists the master and the followers:

```json
{
  "app": "main",
  "port": 7650,
  "bindHost": "0.0.0.0",
  "key": "…",
  "masterUrl": "http://master.internal:7611",
  "followers": [
    { "mid": "f1", "wsUrl": "ws://f1.internal:7600", "controlUrl": "http://f1.internal:7600" },
    { "mid": "f2", "wsUrl": "ws://f2.internal:7600", "controlUrl": "http://f2.internal:7600" }
  ]
}
```

```bash
rindle edge --config edge.json
```

Object-storage credentials come from the standard `AWS_*` environment variables. Every server
signals readiness to systemd (`Type=notify`).

## Operations

- **Health.** Followers serve `/readyz`, `/version`, and `/stats`. The master serves Prometheus
  `/metrics` on `RINDLE_METRICS_ADDR`.
- **Auth.** A server bound to anything but loopback refuses to start without tokens.
  `authToken` covers the internal routes; `sqlAuthToken` is the one your app uses.
- **TLS.** Terminate it at your load balancer or proxy, in front of the edge.
- **Migrations.** `rindle migrate apply --url <edge>` applies them on the master. Followers
  receive the schema change through the journal.
- **Upgrades.** Upgrade the master first, then the followers one at a time.

## Limits

- **One write master.** Writes scale up, across concurrent transactions on one machine, not out.
- **Manual failover.** To replace a lost master, restore it from object storage with
  `restore-master`, which promotes the copy to a new master.

---

# HTTP API

> The routes rindle serve accepts

`rindle serve` (and `rindle up`, which runs it) serves everything on one port: HTTP requests,
the live-query stream, and WebSocket upgrades. `rindle up` uses port 7600, or the next free port
up to 7699, and writes the address to `.env` as `RINDLE_URL`.

The TypeScript clients wrap all of this. You need this page only to call Rindle from another
language or to debug.

## Auth

Send the token as a bearer:

```http
Authorization: Bearer <token>
```

- **Locally**, there is no token unless you start with `rindle up --token <token>`. A server bound
  to `127.0.0.1` with no token accepts every request.
- **On any other address**, a token is required. Without one, `/v1/sql/*` answers `403`.
- A token in `RINDLE_URL` (`https://:token@db.example.com`) is a client convention. The clients
  send it as the bearer; the server only reads the header.

A wrong or missing token is `401`.

## Responses

Success bodies are JSON. Write responses carry a `commit_cursor`, the position of that write in
the log, and a `routing` object:

```json
{
  "result": { … },
  "commit_cursor": "w:00000000000000a3",
  "routing": { "served_by": "standalone", "applied_lag_ms": null, "fence_fallback": false }
}
```

Errors under `/v1/sql/` have a code:

```json
{ "code": "STATEMENT_INVALID", "message": "write request requires a non-empty idempotency_key", "retry_scope": "never" }
```

| Status | Meaning |
|---|---|
| `400` | Bad request, or SQL that doesn't compile. `code` says which. |
| `401` / `403` | Wrong token, or no token on a public address. |
| `409` | A conflict, such as `MIGRATION_CHECKSUM_MISMATCH`. |
| `413` | Body over 80 MiB, or a result over the row limit (`RESULT_LIMIT`). |
| `503` | Busy or starting up. Retry after the `Retry-After` header. |

Every response has a `Rindle-Boot-Id` header that changes when the server restarts.

## SQL

All `POST`, all JSON.

### Statements

A statement is one SQL statement and its arguments:

```json
{ "sql": "INSERT INTO issue (title, closed) VALUES (?, ?)", "args": ["first", 0] }
```

- `args` is an array for `?` placeholders, or an object for named ones (`:title`).
- Values are JSON strings, numbers, booleans and `null`. Send 64-bit integers that don't fit in a
  double as `{"$rindle":"i64","value":"9007199254740993"}`.
- `want_rows: false` skips returning rows.

A statement result:

```json
{
  "columns": [{ "name": "id", "decltype": "INTEGER" }],
  "rows": [[{ "$rindle": "i64", "value": "1" }]],
  "rows_affected": 0,
  "last_insert_rowid": null,
  "rows_read": null,
  "rows_written": null
}
```

`rows` are arrays in column order. Integers come back as `{"$rindle":"i64","value":"…"}`, so
none lose precision. `last_insert_rowid` is a string.

### Idempotency keys

Every write needs an `idempotency_key`. Sending the same request with the same key again returns
the first result instead of writing twice, so retries are safe. The format is `sql1.` + the
current time in milliseconds (13 digits) + `.` + 32 random lowercase hex digits:

```text
sql1.1760029200000.9f86d081884c7d659a2feaa0c55ad015
```

### `/v1/sql/execute`

Runs one statement.

```json
{ "statement": { "sql": "SELECT * FROM issue WHERE id = ?", "args": [1] } }
```

Returns `{ "result": … }`. Reads need no key. Writes need `idempotency_key`. Schema changes go
through `/v1/sql/migrate`.

Reads take an optional `consistency`: `session` (default), `strong`, or `eventual`.

### `/v1/sql/batch`

Runs statements in one transaction. All commit or none do.

```json
{
  "statements": [
    { "sql": "INSERT INTO comment (issue_id, body) VALUES (?, ?)", "args": [1, "a"] },
    { "sql": "INSERT INTO comment (issue_id, body) VALUES (?, ?)", "args": [1, "b"] }
  ],
  "idempotency_key": "sql1.1760029200000.9f86d081884c7d659a2feaa0c55ad015"
}
```

Returns `{ "results": [ … ] }`, one per statement.

### `/v1/sql/execute-multiple`

Runs a script of `;`-separated statements, each committed on its own. No arguments.

```json
{ "sql": "DELETE FROM comment; DELETE FROM issue;", "idempotency_key": "sql1.…" }
```

Returns `{ "commit_cursor": … }`.

### `/v1/sql/migrate`

Applies a schema change once, by name. Sending the same `id` again is a no-op.

```json
{ "id": "0001_init", "sql": "CREATE TABLE issue (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT);" }
```

Returns `{ "applied": true, "commit_cursor": … }`, or `"applied": false` if it was already
applied. Sending a different script under an applied `id` is `409 MIGRATION_CHECKSUM_MISMATCH`.

### `/v1/sql/transactions`

Interactive transactions, for when a statement depends on an earlier one's result.

| Route | Body | Returns |
|---|---|---|
| `/v1/sql/transactions` | `{ "read_only"?: bool }` | `{ "transaction_id": … }` |
| `/v1/sql/transactions/{id}/execute` | `{ "operation_id": "1", "statements": [ … ] }` | `{ "results": [ … ] }` |
| `/v1/sql/transactions/{id}/commit` | `{ "operation_id": "2" }` | `{ "commit_cursor": … }` |
| `/v1/sql/transactions/{id}/rollback` | `{ "operation_id": "2" }` | |

`operation_id` counts up from `1` within the transaction; a repeated id is a retry.

## Live queries

A client is one browser tab (or other consumer). It has one stream and any number of queries.
Your API server makes these calls on the client's behalf; see [Architecture](https://rindle-docs.preview.rocicorp.dev/architecture.md).

Each call names the client in headers:

| Header | |
|---|---|
| `Rindle-Client` | Required. The client's id. |
| `Rindle-Query` | The query's id, a number. |
| `Rindle-User` | Optional. Once set, calls for this client with a different user are refused. |

| Method and route | Body | Returns |
|---|---|---|
| `GET /v1/sql/client/stream` | | The stream, below. |
| `POST /v1/sql/client/queries` | `{ "sql": "…", "params": [ … ] }` | `200`. Rows arrive on the stream. `400 {"error": …}` with the compiler's message if the SQL can't be kept live. |
| `DELETE /v1/sql/client/queries/{id}` | | `204` |
| `DELETE /v1/sql/client` | | `{ "ended": n }`. Ends every stream for `Rindle-Client`, or for `Rindle-User` if no client is given. |

A client may hold 256 queries, and a user 32 clients. A client whose stream drops is kept for 5
seconds, so a reconnect resumes its queries.

### The stream

`GET /v1/sql/client/stream` returns [server-sent events](https://developer.mozilla.org/en-US/docs/Web/API/Server-sent_events/Using_server-sent_events).
The first event lists the client's queries. Each later event carries an `id` and one JSON frame:

```text
data: {"t":"client","server":"b-3f2a…","queries":[1,2]}

id: 1
data: {"t":"hello","queryId":1,…}

id: 2
data: {"t":"batch","queryId":1,"batch":{"events":[…]}}
```

| Frame | |
|---|---|
| `hello` | A query started. Carries its schema. |
| `batch` | Row changes for a query: adds, removes and edits. |
| `progress` | Every query is current as of this point. |
| `queryError` | A query failed. |

A `: keepalive` comment arrives every 15 seconds.

## Health

| Route | Auth | Returns |
|---|---|---|
| `GET /readyz` | none | `200`, or `503 {"ready": false, "reason": …}` while starting |
| `GET /version` | none | `{"service": "rindled", "sha": …}` |

## Internal routes

The server also has routes the clients use internally (`/materialize`, `/schema`,
`/execute-sql-txn`, the WebSocket protocol, and others). They can change between releases; use
the routes on this page.

---

# TypeScript API

> @rindle/reactive-sql-client

Everything is in `@rindle/reactive-sql-client`:

| | Runs on | For |
|---|---|---|
| [`createReactiveSqlClient({ rindle })`](#server-client) | Your server, scripts | SQL, migrations, live queries |
| [`createReactiveSqlClient({ stream })`](#browser-client) | The browser | Live queries through your endpoints |
| [`createRindleSessions()`](#createrindlesessions) | Your server | The stream route and query endpoints |
| [`createSSRClient()`](#createssrclient) | Your server | [Server-side rendering](https://rindle-docs.preview.rocicorp.dev/ssr.md) |

## Server client

```ts
import { createReactiveSqlClient } from "@rindle/reactive-sql-client";

const db = await createReactiveSqlClient({ rindle: {} });
```

Has full access to the database. Keep it on the server.

| Option | |
|---|---|
| `rindle.url` | The server's address. Defaults to `RINDLE_URL`. A password in the URL is the token. |
| `rindle.token` | Defaults to `RINDLE_DATABASE_TOKEN`. |
| `rindle.fetch` | A custom `fetch`. |
| `reveal` | `"committed"` (default) shows a write once the server commits it. `"immediate"` shows it as soon as it arrives. |
| `onQueryError` | `(query, error) => void`. Called when a live query fails after it started. |

Nothing is fetched until the first call.

### `db.materialize(sql, params?)`

```ts
const issues = await db.materialize<Issue>(
  `SELECT * FROM issue WHERE closed = ? ORDER BY id DESC LIMIT 50`,
  [0],
);
```

Starts a live query. `sql` is [Rindle SQL](https://rindle-docs.preview.rocicorp.dev/sql.md);
`params` are strings, numbers, booleans and `null`. Rejects with `SqlCompileError` if the query
can't be kept live.

Returns a `LiveQuery<Row>`:

| | |
|---|---|
| `data` | The current rows. |
| `resultType` | `"unknown"` until the first rows arrive, then `"complete"`. Tells "no rows yet" from "no rows". |
| `subscribe(listener)` | Calls `listener(rows)` now and after every change. Returns an unsubscribe function. |
| `destroy()` | Stops the query. |

The same SQL and params, materialized twice, share one query on the server.

### `db.readOnce(sql, params?)`

Runs a query once and returns its rows. Same SQL as `materialize`.

### `db.migrate(name, sql)`

```ts
await db.migrate("0001_init", `
  CREATE TABLE issue (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, closed INTEGER);
`);
```

Applies a schema change once, by name. Calling it again with the same name does nothing and
returns `{ applied: false }`. A different script under a name that's already applied throws.

Takes `CREATE`/`DROP TABLE`, `ADD`/`DROP COLUMN` and `CREATE`/`DROP INDEX`. Every table needs a
primary key.

### `db.sql.execute(statement)`

```ts
const { result } = await db.sql.execute({
  sql: `INSERT INTO issue (title, closed) VALUES (?, ?)`,
  args: ["first", 0],
});
result.lastInsertRowid; // "1"
```

Runs one statement. `statement` is a string, or `{ sql, args }`, where `args` is an array for `?`
or an object for `:name`. Schema changes throw; use `migrate`.

`result` has:

| | |
|---|---|
| `columns` | `{ name, decltype }[]` |
| `rows` | Arrays in column order. |
| `rowsAffected` | |
| `lastInsertRowid` | A string, or `null`. |

Retries are safe: each write carries a key, and the server applies it once.

### `db.sql.batch(statements)`

Runs statements in one transaction. Returns `{ results }`, one per statement.

### `db.sql.withTransaction(fn)`

```ts
await db.sql.withTransaction(async (tx) => {
  const { rows } = await tx.execute({ sql: `SELECT stock FROM item WHERE id = ?`, args: [id] });
  if (rows[0][0] === 0) throw new Error("sold out");
  await tx.execute({ sql: `UPDATE item SET stock = stock - 1 WHERE id = ?`, args: [id] });
});
```

Commits when `fn` resolves and rolls back when it throws. Inside, `tx.execute` and `tx.batch`
return results directly, without the `{ result }` wrapper. `withTransactionRetry` takes the same
`fn` and reruns it when another write conflicts.

### `db.close()`

Closes the client and its live queries.

## Browser client

```ts
const db = await createReactiveSqlClient({ stream: "/api/rindle-connect" });
```

Talks only to your API server. It has no SQL; your [query endpoints](https://rindle-docs.preview.rocicorp.dev/architecture.md#a-query-endpoint)
pick the SQL.

| Option | |
|---|---|
| `stream` | Your stream route. |
| `headers` | Sent with every request. An object, or a function that returns one. |
| `credentials` | Default `"same-origin"`. |
| `fetch` | The `fetch` this client calls for every request, including the stream. By default the client uses the global `fetch`, except that on Safari and iOS the stream uses `XMLHttpRequest`, to work around [WebKit bug 322545](https://bugs.webkit.org/show_bug.cgi?id=322545). If you pass your own `fetch`, be aware of that WebKit bug. |
| `hydrate` | `true` to pick up queries from a server-rendered page. See [SSR](https://rindle-docs.preview.rocicorp.dev/ssr.md). |

### `db.watch(endpoint)`

```ts
const notes = await db.watch<Note>("/api/notes");
```

Calls your endpoint with the `Rindle-Client` and `Rindle-Query` headers. Rows arrive on the
stream. If the endpoint answers with an error, rejects with a `LiveQueryError` whose `status` is
that code.

Returns the same `data`, `resultType`, `subscribe` and `destroy` as `materialize`, plus `error`. `destroy()`
tells your stream route to remove the query.

In React, use [`useLiveQuery`](https://rindle-docs.preview.rocicorp.dev/react-api.md) instead.

### `db.close()`

Closes the stream.

## `createRindleSessions()`

```ts
import { createRindleSessions } from "@rindle/reactive-sql-client";

const rindle = createRindleSessions(); // reads RINDLE_URL
```

What your [stream route and query endpoints](https://rindle-docs.preview.rocicorp.dev/architecture.md#setup) call. Each method returns a
`Response` to return from your route unchanged.

| | |
|---|---|
| `connect(ids)` | Opens the client's stream. |
| `addQuery(ids, sql, params?)` | Starts a query on the client's stream. `400` with the compiler's message if the SQL can't be kept live. |
| `removeQuery(ids)` | Stops a query. |
| `disconnect({ userID?, clientID? })` | Ends streams for a user or a client. Returns how many. |

`ids` is `{ clientID, queryID, userID? }`, read from the request's `Rindle-Client` and
`Rindle-Query` headers and your auth. `connect` needs no `queryID`.

Options: `url` (default `RINDLE_URL`), `token`, `fetch`.

## `createSSRClient()`

```ts
const db = createSSRClient({ rindle, request, userID: user?.id });
```

A client for one server render. See [Server-Side Rendering](https://rindle-docs.preview.rocicorp.dev/ssr.md).

| | |
|---|---|
| `load(endpoint)` | Calls the endpoint and waits for its rows. Never rejects. |
| `peek(endpoint)` | The rows loaded so far, as `{ rows, status, error }`. |
| `dehydrateScript()` | A script for the page that hands the queries to the browser. |

---

# React API

> @rindle/react

## `useLiveQuery`

```tsx
import { useLiveQuery } from "@rindle/react";

const { rows, status, error } = useLiveQuery<Note>(db, "/api/notes");
```

Keeps a component rendering a live query. Re-renders when the rows change, and stops the query
on unmount.

It takes three forms:

| Call | `db` is | Second argument |
|---|---|---|
| `useLiveQuery(db, endpoint)` | A [browser client](https://rindle-docs.preview.rocicorp.dev/typescript-api.md#browser-client) | Your query endpoint |
| `useLiveQuery(db, sql, params?)` | A [server client](https://rindle-docs.preview.rocicorp.dev/typescript-api.md#server-client) | [Rindle SQL](https://rindle-docs.preview.rocicorp.dev/sql.md) and its parameters |
| `useLiveQuery(endpoint)` | From [`RindleProvider`](#rindleprovider) | Your query endpoint |

Returns:

| | |
|---|---|
| `rows` | The current rows. Empty until they arrive. |
| `status` | `"loading"`, `"complete"` or `"error"`. |
| `error` | An `Error`, when `status` is `"error"`. If your endpoint answered with an error status, a `LiveQueryError` with that `status`. |

Passing `null` for `db` or the query runs nothing and returns `status: "loading"`. Use it while a
client is being created, or to wait on an argument.

Changing the endpoint, SQL or params stops the old query and starts the new one. Until the new
one answers, `rows` is empty and `status` is `"loading"`. Params are compared by value, so an
inline array is fine.

On the server, inside a provider given an [SSR client](https://rindle-docs.preview.rocicorp.dev/typescript-api.md#createssrclient), the hook
suspends until the rows arrive. That needs React 19.

## `RindleProvider`

```tsx
import { RindleProvider } from "@rindle/react";

<RindleProvider client={db}>
  <App />
</RindleProvider>
```

Makes `db` available to `useLiveQuery(endpoint)` below it. `client` is a browser client or an SSR
client. A server-rendered app needs it, because each request has its own client; see
[Server-Side Rendering](https://rindle-docs.preview.rocicorp.dev/ssr.md).

---

# SQL

> What a live query can say

Writes are ordinary SQLite. Live queries are a subset of SQLite `SELECT`, plus a `WITH` clause
for nested results. If a query isn't in the subset, Rindle refuses it with a message that says
what to change.

## Live queries

```sql
SELECT * FROM issue
 WHERE closed = 0 AND title LIKE ?
 ORDER BY id DESC
 LIMIT 50
WITH comments OF comment ON issue_id = id (ORDER BY id DESC LIMIT 10)
```

One `SELECT` from one table. Optionally `ORDER BY`, `LIMIT`, and any number of `WITH` clauses.

### SELECT

- `*`, `table.*`, or a list of columns.
- A column list must include the primary key and the `ORDER BY` columns.
- No renaming (`AS`), no `DISTINCT`, no expressions, except [counts](#counts).

### FROM

One table, optionally with an alias. No `JOIN`: use [`WITH`](#with) for related rows and
[`EXISTS`](#exists) to filter by them.

### WHERE

| | |
|---|---|
| Comparisons | `=` `!=` `<>` `<` `<=` `>` `>=`, between a column and a value |
| Null | `IS NULL`, `IS NOT NULL` |
| Patterns | `LIKE`, `NOT LIKE`, `ILIKE`, `NOT ILIKE` |
| Lists | `IN (…)`, `NOT IN (…)` |
| Ranges | `BETWEEN a AND b` |
| Booleans | `IS TRUE`, `IS FALSE` |
| Logic | `AND`, `OR`, to any depth |
| Subqueries | `EXISTS (…)`, `NOT EXISTS (…)` |

A comparison is always a column and a value. `a = b` between two columns, arithmetic and function
calls are refused. There is no bare `NOT`; use `!=`, `NOT LIKE`, `NOT IN`, `IS NOT` or
`NOT EXISTS`.

### EXISTS

Filters by related rows:

```sql
SELECT * FROM note
 WHERE owner = ?
    OR EXISTS (SELECT 1 FROM share WHERE share.note_id = note.id AND share.user_id = ?)
```

The subquery selects `1` or `*` from one table, and its `WHERE` ties it to the outer row with
`=`. Qualify the outer column (`note.id`); an unqualified name means the inner table.

To require a number of related rows, add `GROUP BY` and `HAVING count(*)`:

```sql
SELECT * FROM issue
 WHERE EXISTS (SELECT 1 FROM comment WHERE comment.issue_id = issue.id
               GROUP BY comment.issue_id HAVING count(*) >= 3)
```

### ORDER BY and LIMIT

`ORDER BY` any columns, `ASC` or `DESC`. Rindle adds the primary key to the end so the order is
always total. `NULL` sorts lowest.

`LIMIT n` keeps the first `n` rows, and stays at `n` as rows come and go.

There is no `OFFSET`. Page from the last row you have instead:

```sql
SELECT * FROM issue
 WHERE (created, id) > (?, ?)
 ORDER BY created, id
 LIMIT 50
```

The columns in the tuple must be the start of an ascending `ORDER BY`. `>=` includes the row.

### WITH

Adds related rows to each result row, as a nested array:

```sql
SELECT * FROM issue
WITH comments OF comment ON issue_id = id (
  WHERE deleted = 0
  ORDER BY id DESC
  LIMIT 10
  WITH author OF user ON id = author_id
)
WITH ONE assignee OF user ON id = assignee_id
```

```ts
type Issue = {
  id: number;
  title: string;
  comments: { id: number; body: string; author: User[] }[];
  assignee: User | null;
};
```

`WITH name OF table ON child_column = parent_column (…)`:

- `name` is the field on each row. `OF table` defaults to `name`.
- `ON` matches the related table's column, on the left, to this row's column, on the right.
  Join on several columns with `AND`.
- The parentheses are optional. Inside them goes the related rows' `WHERE`, `ORDER BY`, `LIMIT`,
  and more `WITH` clauses, nested to any depth.
- `WITH ONE` gives a single row, or `null`, instead of an array.

`WITH` clauses come last. Each `LIMIT` applies per parent row: above, each issue gets its own 10
newest comments.

Inside the parentheses, the related table's columns are in scope, not the parent's.

### Counts

A live count of related rows goes in the `SELECT` list:

```sql
SELECT *, (SELECT count(*) FROM comment WHERE comment.issue_id = issue.id) AS comment_count
  FROM issue
```

`sum(column)` and `avg(column)` work the same way. Name it with `AS`.

### Parameters

`?`, `?1`, or `$1`, numbered from 1. Values are strings, numbers, booleans and `null`. Write a
list as one placeholder per item: `IN (?, ?, ?)`. Named parameters are not supported in live
queries.

### Not supported

`JOIN`, `UNION`, `DISTINCT`, `OFFSET`, window functions, `GROUP BY`, and a top-level aggregate
such as `SELECT count(*) FROM issue`. For a count of related rows, see [Counts](#counts). Run those with
[`db.sql.execute`](https://rindle-docs.preview.rocicorp.dev/typescript-api.md#dbsqlexecutestatement) or
[`readOnce`](https://rindle-docs.preview.rocicorp.dev/typescript-api.md#dbreadoncesql-params); they just aren't live.

## Writes

[`db.sql.execute`](https://rindle-docs.preview.rocicorp.dev/typescript-api.md#dbsqlexecutestatement) and `batch` take any SQLite
`SELECT`, `INSERT`, `UPDATE`, `DELETE` or `REPLACE`, including `RETURNING` and `WITH` (a common
table expression, here). Parameters are `?` or `:name`.

Not accepted: `BEGIN`/`COMMIT` (use [`withTransaction`](https://rindle-docs.preview.rocicorp.dev/typescript-api.md#dbsqlwithtransactionfn)),
`PRAGMA`, `ATTACH` and `VACUUM`.

## Schema changes

Only through [`migrate`](https://rindle-docs.preview.rocicorp.dev/typescript-api.md#dbmigratename-sql):

- `CREATE TABLE`, `DROP TABLE`
- `ALTER TABLE … ADD COLUMN`, `ALTER TABLE … DROP COLUMN`
- `CREATE INDEX`, `DROP INDEX`

Every table needs a primary key. Renaming and changing a column's type are not supported yet.

---

# Query Builder

> Typed queries in TypeScript, instead of SQL

The builder runs on Rindle's in-browser engine (`@rindle/wasm`). `createReactiveSqlClient` and
`rindle.addQuery` take [SQL](https://rindle-docs.preview.rocicorp.dev/sql.md) only for now.

The builder writes the same queries as [Rindle SQL](https://rindle-docs.preview.rocicorp.dev/sql.md), as typed TypeScript. Results are
typed from your schema, and a misspelled table or column is a type error.

```ts
import { createSchema, createWasmStore, gt, number, string, boolean, table } from "@rindle/wasm";

const issue = table("issue")
  .columns({ id: number(), title: string(), priority: number(), closed: boolean() })
  .primaryKey("id");
const comment = table("comment")
  .columns({ id: number(), issueId: number(), body: string() })
  .primaryKey("id");

const schema = createSchema({ tables: [issue, comment] });
const store = await createWasmStore(schema);

const view = store.query.issue
  .where.closed(false)
  .where.priority(gt(3))
  .orderBy("id", "desc")
  .limit(50)
  .sub("comments", comment, { parent: ["id"], child: ["issueId"] }, (c) =>
    c.orderBy("id", "desc").limit(10),
  )
  .materialize();

view.subscribe((rows) => console.log(rows));
```

The same query in SQL:

```sql
SELECT * FROM issue WHERE closed = 0 AND priority > 3 ORDER BY id DESC LIMIT 50
WITH comments OF comment ON issueId = id (ORDER BY id DESC LIMIT 10)
```

## Schema

| | |
|---|---|
| `table(name).columns({ … }).primaryKey(…cols)` | A table. |
| `string()`, `number()`, `boolean()`, `int64()`, `json<T>()` | Column types. Add `.nullable()` for a column that can be `NULL`. |
| `createSchema({ tables })` | The schema. |
| `rel(parent, child, { parentCol: "childCol" })` | A relationship, to reuse in `sub`, `countAs` and `exists`. Group them with `defineRelationships({ … })`. |

## Building a query

`store.query.<table>` starts a query. Every method returns a new query.

| Method | SQL |
|---|---|
| `.where(condition)` | `WHERE`. Calls are combined with `AND`. |
| `.where.<column>(value)` | `WHERE column = value`, or pass an operator: `.where.priority(gt(3))` |
| `.orderBy(column, "asc" \| "desc")` | `ORDER BY` |
| `.limit(n)` | `LIMIT` |
| `.start(row, { exclusive })` | `WHERE (cols) > (…)`: page from a row |
| `.select(...columns)` | A column list |
| `.one()` | `LIMIT 1`. The view's `data` is a row or `null`. |
| `.sub(name, table, correlation, build?)` | `WITH name OF table ON …` |
| `.countAs(name, table, correlation, build?)` | A live count of related rows on each row |
| `.count()`, `.groupBy(column)`, `.having(…)` | Aggregates |
| `.materialize()` | Starts the live query. |
| `.ast()` | The query as JSON. |

A correlation is `{ parent: [...], child: [...] }`: the parent columns, and the child columns
they match. A relationship from `rel` can stand in for the table and correlation.

## Conditions

| Function | SQL |
|---|---|
| `eq`, `ne`, `gt`, `ge`, `lt`, `le` | `=`, `!=`, `>`, `>=`, `<`, `<=` |
| `is`, `isNot`, `isNull`, `isNotNull` | `IS`, `IS NOT`, `IS NULL`, `IS NOT NULL` |
| `like`, `notLike`, `ilike`, `notIlike` | `LIKE`, `NOT LIKE`, `ILIKE`, `NOT ILIKE` |
| `inList`, `notInList` | `IN`, `NOT IN` |
| `and(...)`, `or(...)` | `AND`, `OR` |
| `exists(table, correlation, build?)`, `notExists(…)` | `EXISTS`, `NOT EXISTS` |

```ts
import { exists, or } from "@rindle/wasm";

store.query.issue.where(
  or(issue.priority(gt(8)), exists(comment, { parent: ["id"], child: ["issueId"] })),
);
```

A table doubles as a condition factory: `issue.priority(gt(8))` is the condition
`priority > 8`.

## Views

`materialize()` returns a view:

| | |
|---|---|
| `data` | The current rows. |
| `subscribe(listener)` | Calls `listener(rows)` now and after every change. Returns an unsubscribe function. |
| `destroy()` | Stops the query. |

---

# Performance

Rindle is designed to provide both fast hydration (comparable to OLTP databases) and fast updates
(like differential dataflow). It achieves this with a
[new IVM approach](https://github.com/rocicorp/rindle/blob/branch/docs/ZVM.md).

*Chinook ×30 (105k tracks), in-process, same SQLite file and indexes. Rindle in sink mode over
SQLite vs. SQLite re-running the query. MacBook M5 Max, measured 2026-09-16.*

## Hydrate

**Time to first complete result from a query.**

Rindle is already close to SQLite for most queries with limits, which are common in OLTP use cases.
For queries without limits we are currently slower, and there are a few known open issues where we
are much slower than SQLite.

We expect to reach parity with SQLite for most query forms.

| Query | Rindle | SQLite | Rindle vs. SQLite |
| --- | ---: | ---: | ---: |
| top 50 tracks by length<br>(`ORDER BY … LIMIT 50`) | 23.9 µs | 22.4 µs | 0.94× |
| one genre's 100 longest tracks<br>(`WHERE … ORDER BY … LIMIT 100`) | 48.7 µs | 49.8 µs | 1.0× |
| 10 albums, each with its 10 newest tracks<br>(nested, `LIMIT 10` both levels) | 49.8 µs | 33.3 µs | 0.67× |
| 100 newest albums with a track in a genre<br>(`EXISTS … LIMIT 100`) | 1.3 ms | 44.4 µs | 0.03×\* |
| all albums with all their tracks<br>(10,410 albums, 105,090 tracks, nested) | 53.8 ms | 21.9 ms | 0.41× |

<small>\* A known issue with `EXISTS` hydration that we're working on.</small>

## Update

**CPU spent keeping a query live.**

An app that polls SQLite re-runs its query on a timer, whether or not anything changed. Rindle
does nothing until a write happens, and then does work proportional to the size of the edit, not
the size of the result. Set how often you would poll and how long the task you're waiting on takes:

| Query | CPU/s SQLite | CPU/s Rindle | Rindle advantage | Latency SQLite | Latency Rindle |
| --- | --- | --- | --- | --- | --- |
| top 50 tracks by length | 5.26 µs | 0.06 µs | 88× | 2.5 s | 3.6 µs |
| one genre’s 100 longest tracks | 10.7 µs | 0.063 µs | 170× | 2.5 s | 3.8 µs |
| 10 albums, each with its 10 newest tracks | 7.42 µs | 0.062 µs | 120× | 2.5 s | 3.7 µs |
| 100 newest albums with a track in a genre | 9.54 µs | 0.06 µs | 160× | 2.5 s | 3.6 µs |
| all albums with all their tracks | 4.32 ms | 0.087 µs | 50,000× | 2.52 s | 5.2 µs |

<small>Polling CPU is the re-run time divided by the poll interval. Rindle CPU is the update time
divided by the task's duration. Latency is the average time from a write to the new result: half the poll
interval plus a re-run for polling, one update for Rindle. Polling faster cuts latency but
multiplies its CPU.</small>

## Fan-out

**Time to update many queries affected by a single write.**

Rindle queries are deduplicated, but Rindle also shares work among closely related *query
families*. As a result, the advantage increases with the number of outstanding live queries.

| Live queries | Rindle | SQLite (re-run all N) | Rindle vs. SQLite |
| ---: | ---: | ---: | ---: |
| 1 | 3.6 µs | 53.4 µs | 15× |
| 8 | 3.8 µs | 435 µs | 115× |
| 64 | 6.3 µs | 3.7 ms | 588× |
| 512 | 24.0 µs | 29.7 ms | 1,240× |

## vs. Zero

**Rindle against the Zero code it ports.**

We expect the delta to grow as Rindle continues to be optimized.

| Operation | Rindle (Rust) | Zero (TypeScript) | Speedup |
| --- | ---: | ---: | ---: |
| **In-memory storage** | | | |
| scan 1,000 rows | 3.6 µs | 110 µs | **31×** |
| filtered fetch (~100 rows) | 0.8 µs | 12.8 µs | **16×** |
| start from middle (~500 rows) | 2.4 µs | 58 µs | **24×** |
| insert + delete a row | 0.11 µs | 3.1 µs | **28×** |
| edit a row | 0.28 µs | 3.9 µs | **14×** |
| **SQLite storage** | | | |
| scan 1,000 rows | 99 µs | 673 µs | **6.8×** |
| filtered fetch (~100 rows) | 17 µs | 80 µs | **4.7×** |
| start from middle (~500 rows) | 72 µs | 369 µs | **5.2×** |
| insert + delete a row | 2.9 µs | 7.6 µs | **2.6×** |
| edit a row | 2.7 µs | 8.1 µs | **3.0×** |

---

# Status

> Beta, but getting pretty good

Rindle is an LLM-assisted port of Zero's query engine from TypeScript to Rust. Zero has been in
production for two years, and it was already a database in all but name, with storage, indexes, a
query optimizer, and replicas. What it lacked was authority: Zero always follows some other
upstream database. Rindle adds that, so it can be the source of truth itself.

Correctness is tested in depth. Rindle runs its own unit and integration suite and Zero's original
one, but the main defense is a single contract: the changes a subscriber receives, applied in
order, always equal running the query from scratch. A SQLite-based oracle and a differential
fuzzer check that contract across generated queries, and regular runs add deeper fuzz sweeps and
TLA+ model checks of the replication protocols.

## What's left: the last sixty seconds of durability

Most of the authority work is built: a write-master with multi-writer transactions and snapshot
isolation, a journal that read followers replicate from, fencing so a stale master can't write,
and backup and point-in-time restore to S3.

The main thing that's left is the last sixty seconds of durability. As a replica, Zero never
needed it: it has a single writer, the replication stream, and if it exits before a write is
durable, it picks the write up again from the stream on reboot. Rindle's master has no stream to
fall back on. It ships its journal to S3 every 60 seconds, so a machine lost inside that window
loses acknowledged writes. A process crash loses nothing, because the writes are on local disk.
But a lost machine can, and that is not acceptable for a database.

Fixing this is a well-understood problem with at least two well-known solutions: group-commit the
log tail to the object store before acknowledging, like Turso, or hold the tail in a small
replicated hot store, like Neon.

---

# For Agents

> These docs, in plain Markdown

Every page on this site is also plain Markdown, for coding agents and other tools.

- [`/llms.txt`](https://rindle-docs.preview.rocicorp.dev/llms.txt) lists every page, with a link to its Markdown.
- [`/llms-full.txt`](https://rindle-docs.preview.rocicorp.dev/llms-full.txt) is every page in one file.
- Add `.md` to any page's URL for its Markdown, like
  [`/get-started.md`](https://rindle-docs.preview.rocicorp.dev/get-started.md). The home page is
  [`/index.md`](https://rindle-docs.preview.rocicorp.dev/index.md).

## The skill

To build an app on Rindle, point your agent at the skill:

```text
https://rindle-docs.preview.rocicorp.dev/skill.md
```

See [Get Started](https://rindle-docs.preview.rocicorp.dev/get-started.md) for prompts that use it.