SQLite

For a high-level overview of where to store actor data, including when to use c.state versus SQLite, see State & Storage. For a worked multi-tenant pattern, see the cookbook: Database per Tenant.

What is SQLite?

Database per actor: each actor instance has its own SQLite database, scoped to that actor.
High performance: Rivet Actors keep compute and storage together, so queries avoid network round trips to an external database.
Larger-than-memory storage: SQLite stores data on disk, so you can work with datasets that do not fit in actor memory.
Embedded relational database: use tables, indexes, and SQL queries directly inside actor logic.

SQLite features

Indexes: speed up lookups on frequently queried fields.
Search and filtering: use WHERE, LIKE, and ORDER BY instead of manual in-memory loops.
Relationships: use multiple tables and JOIN queries for connected data.
Constraints: use primary keys, unique constraints, and foreign keys for data integrity.
Transactions: apply multiple writes atomically when changes must stay consistent.

Raw SQL vs ORM (Drizzle)

Rivet supports both raw SQL and Drizzle for actor-local SQLite.

Use raw SQL when you want direct query control and minimal abstraction.

await c.db.execute("INSERT INTO todos (title) VALUES (?)", title);
const rows = await c.db.execute("SELECT id, title FROM todos ORDER BY id DESC");

Use Drizzle when you want typed schema and typed query APIs.

await c.vars.drizzle.insert(todos).values({ title });
const rows = await c.vars.drizzle.select().from(todos).orderBy(desc(todos.id));

You can mix both in the same actor.

For Drizzle setup, see SQLite + Drizzle.

Basic setup

Define db: db({ onMigrate }) on your actor, create your schema in onMigrate, and execute SQL with c.db.execute(...).

RivetKit wraps onMigrate in a SQLite savepoint, so migration steps are atomic. If onMigrate throws, all SQL run by that hook is rolled back before the actor starts.

Queries

c.db.execute(...) returns an array of row objects for SELECT queries.

const rows = await c.db.execute(
  "SELECT id, title FROM todos WHERE title LIKE ?",
  `%${query}%`,
);

Parameterized queries

Use ? placeholders for dynamic values and pass parameters in order after the SQL string.

await c.db.execute("INSERT INTO todos (title) VALUES (?)", title);

You can also use named SQLite bindings by passing a single properties object.

const rows = await c.db.execute(
  "SELECT id, title FROM todos WHERE title = :title",
  { title: "Write SQLite docs" },
);

Transactions

Use transactions when multiple writes must succeed or fail together.

Start with BEGIN.
End with COMMIT if all queries succeed.
On error, run ROLLBACK and rethrow.
A transaction is global for the entire shared c.db connection. Be careful with other interleaved queries.

await c.db.execute("BEGIN");

try {
  await c.db.execute("INSERT INTO todos (title) VALUES (?)", title);
  await c.db.execute(
    "INSERT INTO comments (todo_id, body) VALUES (last_insert_rowid(), ?)",
    body,
  );
  await c.db.execute("COMMIT");
} catch (error) {
  await c.db.execute("ROLLBACK");
  throw error;
}

Queues

It's recommended to use queues for mutations and actions for read-only queries. This is the same code structure as the basic setup, but mutation writes are routed through queues.

Debugging

GET /inspector/summary includes isDatabaseEnabled so you can confirm SQLite is configured.
GET /inspector/database/schema returns the tables and views discovered in the actor's SQLite database.
GET /inspector/database/rows?table=...&limit=100&offset=0 returns paged rows for a specific table or view.
POST /inspector/database/execute lets you run ad-hoc SQL for debugging and data fixes with positional args or named properties.
Keep a small read-only action for quick query verification while debugging.
In non-dev mode, inspector endpoints require authorization.

Recommendations

Keep schema creation and migration steps in onMigrate; RivetKit runs them atomically inside a SQLite savepoint.
Use ? placeholders for dynamic values.
Prefer queue-driven writes for ordered or background work.
Use transactions for related multi-step mutations when atomicity matters.