SQLite

Store relational data inside each Rivet Actor with embedded SQLite, raw SQL queries, transactions, and durable local persistence.

For a high-level overview of where to store actor data, including when to use c.vars versus SQLite, see State & Storage.

What is SQLite?

• Database per actor: each actor instance has its own SQLite database, scoped to that actor.
• High performance: queries run inside the actor’s own process, against pages cached next to it, with no external database server in between.
• Durable and larger than memory: the database lives in the actor’s durable volume, replicated on every commit, so it survives restarts and moves and can hold 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.

import { actor, setup } from "rivetkit";
import { db } from "rivetkit/db";

export const todoList = actor({
  db: db({
    onMigrate: async (db) => {
      await db.execute(`
        CREATE TABLE IF NOT EXISTS todos (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          title TEXT NOT NULL
        );
      `);

      await db.execute(`
        CREATE TABLE IF NOT EXISTS comments (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          todo_id INTEGER NOT NULL,
          body TEXT NOT NULL,
          FOREIGN KEY(todo_id) REFERENCES todos(id)
        );
      `);
    },
  }),
  actions: {
    addTodo: async (c, title: string) => {
      await c.db.execute("INSERT INTO todos (title) VALUES (?)", title);
    },
    addComment: async (c, todoId: number, body: string) => {
      await c.db.execute(
        "INSERT INTO comments (todo_id, body) VALUES (?, ?)",
        todoId,
        body,
      );
    },
    getTodos: async (c) => {
      return (await c.db.execute(
        "SELECT id, title FROM todos ORDER BY id DESC",
      )) as {
        id: number;
        title: string;
      }[];
    },
  },
});

export const registry = setup({ use: { todoList } });
registry.start();

import { createClient } from "rivetkit/client";
import type { registry } from "./index";

const client = createClient<typeof registry>("http://localhost:6420");
const handle = client.todoList.getOrCreate(["main"]);

await handle.addTodo("Write SQLite docs");
await handle.addTodo("Ship docs update");

const todos = await handle.getTodos();
console.log(todos);

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" },
);

Concurrent writes

Actions run concurrently and interleave at every await. Reading a value, awaiting, and writing it back can lose an update made by another action in between, so change data with a single statement instead.

// counters (name TEXT PRIMARY KEY, value INTEGER NOT NULL)
const [counter] = await c.db.execute<{ value: number }>(
  "UPDATE counters SET value = value + 1 WHERE name = ? RETURNING value",
  name,
);

Use a transaction when a change needs several statements.

Transactions

Use transactions when multiple writes must succeed or fail together.

await c.db.transaction(
  async (tx) => {
    await tx.execute("INSERT INTO todos (title) VALUES (?)", title);
    await tx.execute(
      "INSERT INTO comments (todo_id, body) VALUES (last_insert_rowid(), ?)",
      body,
    );
  },
  { name: "create-todo" },
);

RivetKit commits when the callback resolves and rolls back when it throws. Other transactions and ordinary actor SQL queue in FIFO order until the callback finishes. Transactions have a 60-second safety timeout by default; increase it for legitimately long work with { timeout: 120_000 }.

Always use the callback’s tx value inside the transaction. Starting another transaction or using the outer c.db from the callback waits behind the active transaction and eventually reaches the safety timeout; the resulting error points to this possible deadlock.

Do not await other actors, fetch, or other network work inside the callback, because all of the actor’s other SQL waits until the transaction finishes. Make those calls after it commits.

Manual BEGIN/COMMIT calls remain supported for compatibility, but cannot protect against interleaving callers. RivetKit logs a warning recommending db.transaction(). Set warnOnManualTransactions: false in db(...) to disable the warning; the warning itself mentions this flag.

Use a static transaction name for profiling, never a request ID or other dynamic value.

Profiling

See SQLite Profiling to understand query fingerprints, transaction names, metrics, and diagnostics.

Recommendations

• Keep schema creation and migration steps in onMigrate; RivetKit runs them atomically inside a SQLite savepoint.
• Use ? placeholders for dynamic values.
• Make each write a single statement, such as UPDATE ... RETURNING, because actions run concurrently.
• Use transactions for related multi-step mutations when atomicity matters.

Read more

• For raw SQL schemas, use migrations from rivetkit/unstable/migrations to apply numbered migrations and track them in a dedicated schema-version table. See upgrading actor state.
• SQLite + Drizzle in Rivet Actors
• SQLite docs
• SQLite SQL language reference