@capsuleer/sqlite

SQLite module for Capsuleer agents. Gives agents the ability to open local .sqlite files and query them with native SQL — with every operation emitting a structured trace event so Axon has a full audit trail of what was read or modified.

capsuleer install sqlite

Powered by bun:sqlite — zero native dependencies, no build step.

API

Connecting

// Open a file (creates it if it doesn't exist)
await sqlite.open("/data/app.sqlite")

// Open an in-memory database
await sqlite.open(":memory:")

// Close and release the file handle
await sqlite.close()

Querying

// SELECT — returns rows as objects
const users = await sqlite.query("SELECT * FROM users WHERE active = ?", [1])
// → [{ id: 1, name: "Alice", email: "[email protected]" }, ...]

// INSERT / UPDATE / DELETE / DDL — returns affected row count
const result = await sqlite.execute(
  "INSERT INTO events (type, payload) VALUES (?, ?)",
  ["click", JSON.stringify({ x: 100, y: 200 })]
)
// → { changes: 1, lastInsertRowid: 42 }

Always use ? placeholders and pass values via params. Never interpolate values directly into the SQL string.

Introspection

// List all tables
const tables = await sqlite.tables()
// → ["events", "users", "sessions"]

// Column definitions for a table
const cols = await sqlite.schema("users")
// → [{ cid: 0, name: "id", type: "INTEGER", notnull: 1, dflt_value: null, pk: 1 }, ...]

// Indexes — for a specific table or the whole database
const indexes = await sqlite.indexes("users")
// → [{ name: "idx_users_email", unique: 1, origin: "c", partial: 0 }]

const allIndexes = await sqlite.indexes()

// Database-level metadata
const meta = await sqlite.info()
// → { path: "/data/app.sqlite", sizeBytes: 32768, pageSize: 4096, pageCount: 8, walMode: false, encoding: "UTF-8" }

Observability

Every operation emits a structured JSON event to stdout:

{ "ok": true, "op": "sqlite.open", "data": { "path": "/data/app.sqlite" } }
{ "ok": true, "op": "sqlite.query", "data": { "sql": "SELECT * FROM users WHERE active = ?", "params": [1], "rows": 3 } }
{ "ok": true, "op": "sqlite.execute", "data": { "sql": "INSERT INTO events ...", "changes": 1, "lastInsertRowid": 42 } }
{ "ok": true, "op": "sqlite.tables", "data": { "count": 3, "tables": ["events", "users", "sessions"] } }

Axon collects these as trace events — giving you a complete record of every query the agent ran during a session.

Errors emit ok: false with the error message, then re-throw so the agent can handle them.

Policy

SQLite operations are subject to Capsuleer's policy engine. The query and execute split makes it natural to grant read-only access:

const capsule = await Capsule({
  policy: {
    sqlite: {
      open: true,
      query: true,
      execute: false,       // read-only — no writes
      tables: true,
      schema: true,
      indexes: true,
      info: true,
    }
  }
})

Or escalate writes for human approval:

policy: {
  sqlite: {
    query: true,
    execute: "escalate",  // pause and ask before any write
  }
}

See the policy docs for the full rule syntax.

Notes

• Only one database can be open at a time — calling open() while a database is already open closes the previous one first
• execute() handles all write statements: INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE, ALTER TABLE, etc.
• bun:sqlite is bundled with Bun — no npm install or native compilation required
• For large result sets, prefer adding LIMIT clauses rather than fetching everything into memory