SQL Database - Runloop AI Docs

The examples on this page use @runloop/api-client in TypeScript. See the Remote Agents SDK repository and full SDK documentation.

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

Use the SQL database when you need querying, transactions, or secondary indexes. Use the event stream or Broker when you need messaging or coordination with external runtimes.

Semantics and Guarantees

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.

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.

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.

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.

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.

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

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

Run Atomic Transactions

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

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:

{
  "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:

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:

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:

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:

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:

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.

Axons Overview — Event streams, publishing, and subscribing
Broker — Bridging Axons to agents running in Devboxes
SDKs — SDK installation and reference docs

​What This Is

​When to Use SQL vs Events

​Semantics and Guarantees

​Getting Started

​Create Schema

​Insert and Query Data

​Use Parameters Safely

​Run Atomic Transactions

​Understand Results

​Query Result

​Batch Result

​Handle Errors

​Common Patterns

​Key-Value Store

​Task Queue

​Schema Versioning

​Limits and Caveats

​Related Documentation

What This Is

When to Use SQL vs Events

Semantics and Guarantees

Getting Started

Create Schema

Insert and Query Data

Use Parameters Safely

Run Atomic Transactions

Understand Results

Query Result

Batch Result

Handle Errors

Common Patterns

Key-Value Store

Task Queue

Schema Versioning

Limits and Caveats

Related Documentation