SQLite journal

SqliteJournal stores events in a SQLite database — a single file on disk, durable, no separate server. It’s the right default for single-node production: on Bun the driver is built in (bun:sqlite, no install), on Node it’s a single peer-dep (better-sqlite3). Survives restarts, fast enough for most workloads.

import {
  SqliteJournal,
  SqliteJournalOptions,
  SqliteSnapshotStore,
  SqliteSnapshotStoreOptions,
  PersistenceExtensionId,
  ActorSystem,
} from 'actor-ts';

const system = ActorSystem.create('my-app');
const sqliteJournalOptions = SqliteJournalOptions.create()
  .withPath('/var/lib/my-app/events.db')
  .withWal(true);
const sqliteSnapshotStoreOptions = SqliteSnapshotStoreOptions.create().withPath('/var/lib/my-app/snapshots.db');
const persistence = system.extension(PersistenceExtensionId);
persistence.setJournal(new SqliteJournal(sqliteJournalOptions));
persistence.setSnapshotStore(new SqliteSnapshotStore(sqliteSnapshotStoreOptions));

A single file per system; the actor system writes through SQLite to the OS page cache, which flushes to disk on commit.

Configuration

type SqliteJournalOptions = {
  path?:           string;     // file path, or ":memory:" for ephemeral
  eventsTable?:    string;     // default "events"
  wal?:            boolean;    // enable WAL mode (recommended)
  busyTimeoutMs?:  number;     // lock-wait budget, default 1000; 0 = fail fast
  driver?:         SqliteDriver;   // explicit driver override
};

path

const sqliteJournalOptions = SqliteJournalOptions.create().withPath('/var/lib/my-app/events.db');
new SqliteJournal(sqliteJournalOptions)

The database file. Absolute paths are best — relative paths are resolved from process.cwd(), which can surprise you. The file is created if it doesn’t exist; existing files are reused (events append in place).

For tests, use ':memory:' — a SQLite-backed in-memory DB that behaves exactly like the file version but goes away with the process:

const sqliteJournalOptions = SqliteJournalOptions.create().withPath(':memory:');
new SqliteJournal(sqliteJournalOptions)

eventsTable

Default 'events'. Override if you want multiple systems sharing one DB file (e.g. dev rig):

const sqliteJournalOptions = SqliteJournalOptions.create()
  .withPath('shared.db')
  .withEventsTable('orders_events');
new SqliteJournal(sqliteJournalOptions)

The framework creates the table automatically on first use, with the schema:

CREATE TABLE events (
  persistence_id TEXT NOT NULL,
  sequence_nr    INTEGER NOT NULL,
  payload        TEXT NOT NULL,
  tags           TEXT,                -- legacy CSV tags (back-compat)
  timestamp      INTEGER NOT NULL,
  PRIMARY KEY (persistence_id, sequence_nr)
);

CREATE INDEX idx_events_pid ON events (persistence_id);

CREATE TABLE events_tags (
  persistence_id TEXT NOT NULL,
  sequence_nr    INTEGER NOT NULL,
  tag            TEXT NOT NULL,
  timestamp      INTEGER NOT NULL,
  PRIMARY KEY (tag, timestamp, persistence_id, sequence_nr)
);

CREATE INDEX idx_events_tags_pid_seq ON events_tags (persistence_id, sequence_nr);

The dual-table design (events + events_tags) lets tag queries hit an index instead of scanning CSV.

wal

const sqliteJournalOptions = SqliteJournalOptions.create()
  .withPath('...')
  .withWal(true);
new SqliteJournal(sqliteJournalOptions)

Enables Write-Ahead Logging mode. Recommended for production. WAL gives:

• Better concurrency — readers don’t block the writer.
• Faster commits — WAL writes are sequential, then checkpoint is batched.
• Safer crashes — recovery is simpler than rollback-journal mode.

The default is off to match SQLite’s defaults; enable it explicitly when you go to production.

busyTimeoutMs

const sqliteJournalOptions = SqliteJournalOptions.create()
  .withPath('...')
  .withBusyTimeoutMs(1000);
new SqliteJournal(sqliteJournalOptions)

How long a write waits for the database lock before giving up with SQLITE_BUSY. Default 1000 ms. 0 disables the wait — a contended write fails on the first attempt.

This one is set for you on purpose, because the drivers do not agree on it. Their built-in defaults are 0 ms on bun:sqlite and node:sqlite but 5000 ms on better-sqlite3, so without an explicit value the same journal would fail instantly on Bun and Deno and block for five seconds on Node. The framework stamps its own value on every connection it opens, which is what makes the three runtimes behave alike.

Pick your own value with one thing in mind: the SQLite drivers are synchronous, so the wait is not idle time — it blocks the event loop, and nothing else in the process runs while it lasts, cluster heartbeats included. That is why the default is 1000 ms rather than better-sqlite3’s 5000: the cluster failure detector calls a node unreachable after 2000 ms, so a five-second stall would be long enough for a node’s own peers to give up on it. Raise it only if you know your contention is short-lived and your node is not in a cluster.

A negative value is rejected — SQLite reads it as “retry forever”, which on a synchronous driver means an unbounded freeze.

driver

The framework auto-detects the right driver based on the runtime, so you normally leave driver unset:

• Bun → bun:sqlite (built-in).
• Node → better-sqlite3 when it is installed, otherwise the built-in node:sqlite.
• Deno → node:sqlite (built-in, Deno >= 2.2).

Auto-detection covers every supported runtime, and every one of them has a driver that needs no install. The concrete driver classes aren’t part of the public API — there’s no import path to construct one by hand — so the driver slot is an internal seam the framework’s own tests use, not something you wire up in application code.

Peer dependency on Node — optional

npm install better-sqlite3

On Node, better-sqlite3 is an optional peer dependency: install it and the framework prefers it, skip it and the built-in node:sqlite takes over. It is worth installing if you want the extra throughput or if your deployment already runs it; otherwise the zero-dependency path is one less native build in your image.

Either way the import is lazy — only when the framework actually needs to open a SQLite database. If you never use SQLite, a missing peer doesn’t matter.

Bun and Deno need nothing: bun:sqlite and node:sqlite are built in.

For a remote SQLite database, see libSQL / Turso — the same schema, reached over HTTP.

Durable state

Alongside the journal and snapshot store, SQLite has a DurableStateStore — the “keep only the current value” model, with no event log and no replay:

import { SqliteDurableStateStore, SqliteDurableStateStoreOptions } from 'actor-ts';

const stateOptions = SqliteDurableStateStoreOptions.create()
  .withPath('./state.db');
const store = new SqliteDurableStateStore(stateOptions);

To share one database handle across the journal, the snapshot store and the durable-state store, open it yourself and pass it in — the store then leaves closing it to you:

import { getSqliteDriver } from 'actor-ts';

const driver = await getSqliteDriver();
const database = driver.open('./app.db');

const sharedOptions = SqliteDurableStateStoreOptions.create()
  .withDatabase(database);

A handle you opened yourself is one the store leaves alone, and that includes busyTimeoutMs: the store applies it only to connections it opens itself, because the pragma is per connection and re-tuning a shared handle would reach into every other store on it. Set it yourself on a handle you own:

database.exec('PRAGMA busy_timeout = 1000;');

The schema is the SQLite dialect’s, identical to libSQL / Turso and Cloudflare D1, so a database can move between a local file and either of those without a migration.

One difference worth knowing in the other direction: because this talks to a local file rather than over HTTP, its transactions are a real BEGIN IMMEDIATE … COMMIT. The HTTP-fronted SQLite backends can only offer an atomic batch, which is why SqlPool specifies isolation as adapter-defined — this backend gives more than the contract asks for.

A remote URL is rejected at construction: withPath('libsql://…') throws and points you at LibSqlDurableStateStore, because the local driver cannot open one and silently creating a file with that name is the confusing outcome.

How it performs

Rough numbers (NVMe disk, default settings):

• Append throughput — 10 000-50 000 events/sec for small events. WAL mode helps significantly.
• Read throughput — 100 000+ events/sec for recovery (sequential scan).
• Concurrent readers — many parallel readers don’t block writers in WAL mode.

For a single-node app with a few thousand actors emitting events per second, SQLite is plenty fast. For tens of thousands of events per second sustained, consider:

• Tuning SQLite pragmas (synchronous = NORMAL, journal_mode = WAL, larger cache).
• Sharding across multiple journals.
• Switching to Cassandra for multi-node distribution.

Recovery flow

When a PersistentActor starts:

1. Load latest snapshot from the snapshot store (if any).
2. Run SELECT payload FROM events WHERE persistence_id = ? AND sequence_nr >= ? to stream events after the snapshot.
3. Apply each event via onEvent.

For a 100 000-event journal with no snapshot, recovery reads all 100 000 rows. Sequential scan with a prepared statement — sub-second on modern hardware, but set up snapshots for any actor that accumulates events.

See Snapshots.

Backup + restore

Since the journal is a single SQLite file:

# Backup (with WAL — use SQLite's online backup):
sqlite3 events.db ".backup events-$(date +%F).db"

# Or stop the app + cp:
systemctl stop my-app
cp events.db events.db.bak
systemctl start my-app

Online backup is preferred — no downtime, consistent snapshot. The standard SQLite tooling applies.

Pitfalls

Don't share a SQLite file across processes

# Two processes writing the same events.db file:
bun run app.ts &
bun run app.ts &

SQLite does support concurrent processes (file locking), but the actor framework’s prepared statements and in-memory bus don’t know about other processes’ writes. One process’s PersistentActor wouldn’t see another process’s appends until the next read — and the in-memory JournalEventBus is local to the process, so push-based queries miss cross-process events.

For multi-process setups, use Cassandra (built for shared state) or one SQLite per process with strict pid partitioning.

If you share a file anyway, busyTimeoutMs is what decides whether a write blocked by the other process waits or fails — and because the driver is synchronous, that wait freezes your event loop for its whole duration.

Filesystem matters

# ✗ network filesystem
/mnt/nfs/events.db

# ✓ local disk
/var/lib/my-app/events.db

SQLite over NFS / S3FS / similar is famously broken (file locking semantics). Local disk only. For shared storage, use a SQL server or Cassandra.

Bun container images need write access

USER nobody
COPY events.db /data/events.db
# → nobody can't write /data/events.db on bind-mount

Make sure the user running the app has write access to the database file and the directory containing it (SQLite creates WAL + shm files alongside the main DB). chown -R appuser /data in the entrypoint.

When SQLite isn’t enough

Three signals you’ve outgrown single-file SQLite:

1. Multi-node — you need actors on N nodes to share the same event stream. SQLite per-node doesn’t work; switch to Cassandra.
2. Sustained 100K+ events/sec — SQLite can handle it with tuning but you’re at the edges; columnar / distributed engines are designed for it.
3. Large events (> 1 MB each) — SQLite stores each event as a JSON TEXT payload; read performance degrades. Consider event compaction (store pointers to external storage) or a journal designed for large payloads.

Where to next

• Persistence overview — the bigger picture.
• In-memory journal — for tests and dev.
• Cassandra journal — for multi-node production.
• Snapshots — to bound the recovery scan.
• Snapshot stores — SQLite — the companion snapshot store.
• Migration recipes — schema evolution on a long-running journal.