> ## Documentation Index
> Fetch the complete documentation index at: https://docs.runloop.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL Database

> Private embedded SQLite database for structured state within an Axon

<Note>
  The examples on this page use `@runloop/api-client` in TypeScript. See the [Remote Agents SDK repository](https://github.com/runloopai/remote-agents-sdk) and [full SDK documentation](https://runloopai.github.io/remote-agents-sdk/).
</Note>

## What This Is

Every Axon includes its own private, embedded SQLite database for structured state. Use it to store configuration, queues, indexes, checkpoints, and relational data that belongs to a single Axon.

The database is accessed through the SDK:

* `axon.sql.query()` — execute a single SQL statement
* `axon.sql.batch()` — execute multiple statements atomically in one transaction

### When to Use SQL vs Events

| Use case                                               | Prefer                           |
| ------------------------------------------------------ | -------------------------------- |
| Structured local state, indexes, queues, configuration | **Axon SQL**                     |
| Append-only messaging, fan-out, external coordination  | **Axon event stream**            |
| Bridging events to an agent process in a Devbox        | **[Broker](/docs/axons/broker)** |

Use the SQL database when you need querying, transactions, or secondary indexes. Use the [event stream](/docs/axons/overview) or [Broker](/docs/axons/broker) when you need messaging or coordination with external runtimes.

### Semantics and Guarantees

<Tip>
  **Axon SQL semantics**

  * **One database per Axon** — each Axon has its own isolated SQLite instance.
  * **Private** — the database is not visible from other Axons or external connections.
  * **Durable** — data persists for the lifetime of the Axon.
  * **`query()`** — executes exactly one SQL statement.
  * **`batch()`** — runs all statements atomically in a single transaction. If any statement fails, all writes are rolled back.
  * **Serialized** — concurrent queries against the same Axon are serialized server-side.
</Tip>

## Getting Started

This walkthrough creates an Axon, initializes a schema, writes rows, reads them back, and runs a transaction — everything you need to start using Axon SQL.

```typescript theme={null}
import { RunloopSDK } from "@runloop/api-client";

const runloop = new RunloopSDK();

// 1. Create an Axon
const axon = await runloop.axon.create({ name: "task-tracker" });

// 2. Initialize schema
await axon.sql.batch({
  statements: [
    { sql: "CREATE TABLE IF NOT EXISTS tasks (id INTEGER PRIMARY KEY, title TEXT NOT NULL, done INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT (datetime('now')))" },
    { sql: "CREATE INDEX IF NOT EXISTS idx_tasks_done ON tasks(done)" },
  ],
});

// 3. Insert rows
await axon.sql.query({
  sql: "INSERT INTO tasks (title) VALUES (?)",
  params: ["Review the latest PR"],
});
await axon.sql.query({
  sql: "INSERT INTO tasks (title) VALUES (?)",
  params: ["Update CI pipeline"],
});

// 4. Query rows
const result = await axon.sql.query({
  sql: "SELECT * FROM tasks WHERE done = 0",
});
console.log("Columns:", result.columns.map((c) => c.name));
for (const row of result.rows) {
  console.log(row);
}
console.log(`Query took ${result.meta.duration_ms}ms`);

// 5. Run an atomic transaction
await axon.sql.batch({
  statements: [
    { sql: "UPDATE tasks SET done = 1 WHERE id = ?", params: [1] },
    { sql: "INSERT INTO tasks (title) VALUES (?)", params: ["Deploy to staging"] },
  ],
});
```

## Create Schema

Use `batch()` to set up tables and indexes together. `IF NOT EXISTS` makes schema creation idempotent so it is safe to run on every startup.

```typescript theme={null}
await axon.sql.batch({
  statements: [
    { sql: "CREATE TABLE IF NOT EXISTS tasks (id INTEGER PRIMARY KEY, title TEXT NOT NULL, done INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT (datetime('now')))" },
    { sql: "CREATE INDEX IF NOT EXISTS idx_tasks_done ON tasks(done)" },
    { sql: "CREATE INDEX IF NOT EXISTS idx_tasks_created ON tasks(created_at)" },
  ],
});
```

**Schema tips:**

* Use `INTEGER PRIMARY KEY` for auto-incrementing IDs (SQLite aliases this to `rowid`).
* Store timestamps as ISO 8601 text via `datetime('now')` or as milliseconds in an `INTEGER` column.
* Add indexes for columns you filter or sort on frequently.
* Wrap schema setup in `batch()` so all tables and indexes are created atomically.

## Insert and Query Data

`query()` executes a single SQL statement and returns a `SqlQueryResultView`.

```typescript theme={null}
await axon.sql.query({
  sql: "INSERT INTO tasks (title) VALUES (?)",
  params: ["Review the latest PR"],
});

const result = await axon.sql.query({
  sql: "SELECT * FROM tasks WHERE done = 0",
});
console.log("Columns:", result.columns.map((c) => c.name));
console.log("Rows:", result.rows);
console.log(`Query took ${result.meta.duration_ms}ms`);
```

## Use Parameters Safely

Use `?`-style positional placeholders and pass values via the `params` array. This prevents SQL injection and lets the database optimize repeated queries.

```typescript theme={null}
const result = await axon.sql.query({
  sql: "SELECT * FROM tasks WHERE done = ? AND title LIKE ?",
  params: [0, "%PR%"],
});
```

<Warning>
  Always use parameterized queries for user-provided values. Never interpolate
  strings directly into SQL.
</Warning>

## Run Atomic Transactions

`batch()` runs multiple statements in a single transaction. If any statement fails, the entire transaction is rolled back — no partial writes.

```typescript theme={null}
const result = await axon.sql.batch({
  statements: [
    { sql: "UPDATE tasks SET done = 1 WHERE id = ?", params: [1] },
    { sql: "INSERT INTO tasks (title) VALUES (?)", params: ["Follow-up review"] },
    { sql: "SELECT * FROM tasks" },
  ],
});

for (const [i, step] of result.results.entries()) {
  if (step.error) {
    console.log(`Statement ${i} failed: ${step.error.message}`);
  } else if (step.success) {
    console.log(`Statement ${i}: ${step.success.meta.changes} changes, ${step.success.rows.length} rows`);
  }
}
```

## Understand Results

### Query Result

`query()` returns a `SqlQueryResultView`:

| Field     | Type                  | Description                                             |
| --------- | --------------------- | ------------------------------------------------------- |
| `columns` | `SqlColumnMetaView[]` | Column names and declared types                         |
| `rows`    | `array`               | Result rows as arrays (empty for non-SELECT statements) |
| `meta`    | `SqlResultMetaView`   | Execution metadata                                      |

Each `SqlColumnMetaView` has `name` (column name or alias) and `type` (declared type: `TEXT`, `INTEGER`, `REAL`, `BLOB`, or empty).

`SqlResultMetaView` contains:

| Field                     | Type      | Description                                                 |
| ------------------------- | --------- | ----------------------------------------------------------- |
| `changes`                 | `number`  | Rows modified by INSERT, UPDATE, or DELETE                  |
| `duration_ms`             | `number`  | Execution time in milliseconds                              |
| `rows_read_limit_reached` | `boolean` | `true` when the result was truncated at the 1,000-row limit |

**Example response** for `SELECT id, title, done FROM tasks WHERE done = 0`:

```json theme={null}
{
  "columns": [
    { "name": "id", "type": "INTEGER" },
    { "name": "title", "type": "TEXT" },
    { "name": "done", "type": "INTEGER" }
  ],
  "rows": [
    [1, "Review the latest PR", 0],
    [2, "Update CI pipeline", 0]
  ],
  "meta": {
    "changes": 0,
    "duration_ms": 1,
    "rows_read_limit_reached": false
  }
}
```

### Batch Result

`batch()` returns a `SqlBatchResultView` containing a `results` array with one entry per statement. Each entry is a `SqlStepResultView` with either:

* `success` — a `SqlQueryResultView` (same structure as a single query result)
* `error` — a `SqlStepErrorView` with a `message` string

## Handle Errors

`query()` raises an exception for invalid SQL or execution errors. Catch it in your application code:

```typescript theme={null}
try {
  await axon.sql.query({ sql: "SELECT * FROM nonexistent_table" });
} catch (e) {
  console.error("Query failed:", e);
}
```

`batch()` executes atomically — if any statement fails, all writes are rolled back. Each step result includes either `success` or `error`, so you can inspect individual failures:

```typescript theme={null}
const result = await axon.sql.batch({
  statements: [
    { sql: "INSERT INTO tasks (title) VALUES (?)", params: ["Valid insert"] },
    { sql: "INSERT INTO nonexistent (col) VALUES (?)", params: ["This fails"] },
  ],
});

for (const [i, step] of result.results.entries()) {
  if (step.error) {
    console.log(`Statement ${i} failed: ${step.error.message}`);
  }
}
```

Constraint violations (e.g. `UNIQUE`, `NOT NULL`, `FOREIGN KEY`) behave the same way — the failing statement produces an `error` in the step result and the entire batch is rolled back.

## Common Patterns

### Key-Value Store

A simple key-value table for agent state, configuration, or checkpoints:

```typescript theme={null}
await axon.sql.batch({
  statements: [
    { sql: "CREATE TABLE IF NOT EXISTS kv (key TEXT PRIMARY KEY, value TEXT NOT NULL)" },
    { sql: "INSERT OR REPLACE INTO kv (key, value) VALUES (?, ?)", params: ["cursor", "evt_42"] },
    { sql: "INSERT OR REPLACE INTO kv (key, value) VALUES (?, ?)", params: ["status", "running"] },
  ],
});

const result = await axon.sql.query({
  sql: "SELECT value FROM kv WHERE key = ?",
  params: ["cursor"],
});
```

### Task Queue

A FIFO queue with claim semantics using `UPDATE ... LIMIT 1`:

```typescript theme={null}
await axon.sql.query({
  sql: "CREATE TABLE IF NOT EXISTS queue (id INTEGER PRIMARY KEY, payload TEXT NOT NULL, claimed INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT (datetime('now')))",
});

await axon.sql.query({
  sql: "INSERT INTO queue (payload) VALUES (?)",
  params: ['{"task": "run-tests", "repo": "runloop"}'],
});

const claimed = await axon.sql.batch({
  statements: [
    { sql: "UPDATE queue SET claimed = 1 WHERE id = (SELECT id FROM queue WHERE claimed = 0 ORDER BY id LIMIT 1) RETURNING *" },
  ],
});
```

### Schema Versioning

Track schema migrations with a version table so schema setup is safe to re-run:

```typescript theme={null}
await axon.sql.query({
  sql: "CREATE TABLE IF NOT EXISTS schema_version (version INTEGER PRIMARY KEY)",
});

const vResult = await axon.sql.query({
  sql: "SELECT MAX(version) as v FROM schema_version",
});
const currentVersion = (vResult.rows[0]?.[0] as number | null) ?? 0;

if (currentVersion < 1) {
  await axon.sql.batch({
    statements: [
      { sql: "CREATE TABLE tasks (id INTEGER PRIMARY KEY, title TEXT NOT NULL, done INTEGER NOT NULL DEFAULT 0)" },
      { sql: "INSERT INTO schema_version (version) VALUES (1)" },
    ],
  });
}

if (currentVersion < 2) {
  await axon.sql.batch({
    statements: [
      { sql: "ALTER TABLE tasks ADD COLUMN created_at TEXT NOT NULL DEFAULT (datetime('now'))" },
      { sql: "CREATE INDEX idx_tasks_created ON tasks(created_at)" },
      { sql: "INSERT INTO schema_version (version) VALUES (2)" },
    ],
  });
}
```

## Limits and Caveats

* The Axon SQL database uses standard SQLite semantics. Supported types are `TEXT`, `INTEGER`, `REAL`, `BLOB`, and `NULL`.
* Booleans are represented as integers (`0` / `1`).
* `query()` executes exactly one SQL statement. Multi-statement strings are not supported in a single `query()` call — use `batch()` instead.

## Related Documentation

* [Axons Overview](/docs/axons/overview) — Event streams, publishing, and subscribing
* [Broker](/docs/axons/broker) — Bridging Axons to agents running in Devboxes
* [SDKs](/docs/tools/sdks) — SDK installation and reference docs