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 { ActorSystem, ActorSystemOptions,} from 'actor-ts';import { SqliteJournal, SqliteJournalOptions, SqliteSnapshotStore, SqliteSnapshotStoreOptions,} from 'actor-ts/persistence';
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 systemOptions = ActorSystemOptions.create().withPersistence({ journal: new SqliteJournal(sqliteJournalOptions), snapshotStore: new SqliteSnapshotStore(sqliteSnapshotStoreOptions),});const system = ActorSystem.create('my-app', systemOptions);A single file per system; the actor system writes through SQLite to the OS page cache, which flushes to disk on commit. Stores set in the system’s options open as part of its startup, so a file the node cannot open fails the startup rather than the first persisted message — see opening the stores at startup.
Register it as a plugin
Section titled “Register it as a plugin”The options above wire the stores directly, which is the shortest route and
fine for an app that will only ever use SQLite. The other way round is to
register SQLite as a plugin and let configuration select it — which is what
actor-ts.persistence.journal.plugin has always meant, and what
registerSqlitePlugins now makes true for SQLite as well:
import { ActorSystem, ActorSystemOptions } from 'actor-ts';import { registerSqlitePlugins } from 'actor-ts/persistence';
const systemOptions = ActorSystemOptions.create().withPersistence({ plugins: [(persistence) => registerSqlitePlugins(persistence)],});const system = ActorSystem.create('my-app', systemOptions);actor-ts.persistence { journal { plugin = "actor-ts.persistence.journal.sqlite" } snapshot-store { plugin = "actor-ts.persistence.snapshot-store.sqlite" } durable-state { plugin = "actor-ts.persistence.durable-state.sqlite" }
journal.sqlite { path = "/var/lib/my-app/app.db", wal = on } snapshot-store.sqlite { path = "/var/lib/my-app/app.db", keep-n = 5 } durable-state.sqlite { path = "/var/lib/my-app/app.db" }}That is the whole wiring: the plugin id is the config section, so the block
named after the id is the block that plugin reads. Nothing about the database
lives in code any more — the path, the table names, wal, keep-n and the busy
timeout all move with the environment, which is the point of a config file.
Every store a selector names has to name its database. The shipped path = ""
means not set, and there is no in-memory fallback behind it: a selected store
whose block and code options leave the path unset is refused when
registerSqlitePlugins runs, so ActorSystem.create throws with an error that
names the key to set. path = ":memory:" is how to ask for an in-memory
database. A store that is registered but not selected is never built, so it
needs nothing.
Options passed to registerSqlitePlugins still win, field by field, and a field
you leave unset falls through to the block rather than blanking it:
// path and tables from HOCON; the serializer only expressible in code.const sqlitePluginsOptions = RegisterSqlitePluginsOptions.create().withSerializer(myCborSerializer);const systemOptions = ActorSystemOptions.create().withPersistence({ plugins: [(persistence) => registerSqlitePlugins(persistence, sqlitePluginsOptions)],});A path given here is a shared default — a per-store block that names its
own keeps it — while a pre-opened database is an override for the
durable-state store, the one SQLite store that takes a handle. The journal and
the snapshot store open their own file, so a handle passed here does not reach
them, and a selected one still needs a path. There is no config leaf for
driver, database or serializer: those are live objects, and a config file
has no way to express one.
See Configuration reference for the full key table.
Configuration
Section titled “Configuration”type SqliteJournalOptions = { path?: string; // required: a file path, or ":memory:" for an in-memory DB 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};const sqliteJournalOptions = SqliteJournalOptions.create().withPath('/var/lib/my-app/events.db');new SqliteJournal(sqliteJournalOptions)The database file, and it is required. 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).
There is no default to fall back on. A journal constructed without
a path throws an OptionsError, and so does an empty path or a
remote URL — libsql://, https://, wss:// belong to
libSQL / Turso. An in-memory
database is an explicit choice, not what a missing path means: 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
Section titled “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 name is a stem, not just one table: the tag index and the
compaction high-water mark derive from it, so orders_events also
gives you orders_events_tags and orders_events_meta. The
framework creates all of them automatically when the store opens — at
startup, or on first use when the boot-time open is turned off; see
Schema.
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
Section titled “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
Section titled “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-sqlite3when it is installed, otherwise the built-innode: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.
Schema
Section titled “Schema”Each store runs CREATE TABLE IF NOT EXISTS when it opens — at startup,
or on first use when the boot-time open is turned off — so an empty
file is a valid starting point. Five tables, across the three stores:
CREATE TABLE events ( persistence_id TEXT NOT NULL, sequence_nr INTEGER NOT NULL, payload TEXT NOT NULL, -- JSON tags TEXT, -- legacy CSV (also mirrored into events_tags) timestamp INTEGER NOT NULL, PRIMARY KEY (persistence_id, sequence_nr));CREATE INDEX idx_events_pid ON events(persistence_id);
CREATE TABLE events_tags ( -- indexed tag lookups for projections 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);
CREATE TABLE events_meta ( -- compaction high-water mark persistence_id TEXT PRIMARY KEY, deleted_to INTEGER NOT NULL);
CREATE TABLE snapshots ( persistence_id TEXT NOT NULL, sequence_nr INTEGER NOT NULL, payload TEXT NOT NULL, timestamp INTEGER NOT NULL, PRIMARY KEY (persistence_id, sequence_nr));
CREATE TABLE durable_state ( persistence_id TEXT PRIMARY KEY, revision INTEGER NOT NULL, payload TEXT NOT NULL, timestamp INTEGER NOT NULL);The names above are the defaults; eventsTable, snapshotsTable and the
durable-state table option rename them, and events_tags / events_meta
follow the journal’s stem.
Two details worth knowing. The dual-table tag design is why
projections scale: events.tags keeps the legacy CSV column for
back-compat, but tag queries read events_tags, hitting an index instead
of scanning every row. Both are written in the same transaction, so they
cannot drift apart.
And the events primary key is the backstop here, not the concurrency
mechanism. Locally the journal re-reads the head sequence inside a
BEGIN IMMEDIATE transaction and raises JournalConcurrencyError before
attempting any insert. The HTTP-fronted backends have no such
transaction, so they let the write hit (persistence_id, sequence_nr) and
translate the UNIQUE constraint failed instead — same tables, weaker
guarantee.
This is the SQLite dialect’s schema, shared verbatim with libSQL / Turso and Cloudflare D1 — the same DDL runs on all three, which is what lets a database move between a local file and either of those without a migration.
Peer dependency on Node — optional
Section titled “Peer dependency on Node — optional”npm install better-sqlite3On 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 waits until the framework actually opens a SQLite database — at startup for a store the system opens there, or on first use when the boot-time open is turned off. 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
Section titled “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/persistence';
const stateOptions = SqliteDurableStateStoreOptions.create() .withPath('./state.db');const store = new SqliteDurableStateStore(stateOptions);It is the only SQLite store that takes a database handle instead of a path — the journal and the snapshot store open their own file, so give them a path. To hand it one you opened yourself, one your own code also uses, pass it in; the store then leaves closing it to you:
import { getSqliteDriver } from 'actor-ts/persistence';
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 everything else that uses it. Set it
yourself on a handle you own:
database.exec('PRAGMA busy_timeout = 1000;');Its table is the durable_state one in Schema — the SQLite
dialect’s, identical to what
libSQL / Turso and
Cloudflare D1 run, 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, by all three SQLite stores and
not only this one: withPath('libsql://…') throws and points you at the
libSQL stores, because the local driver cannot open one and silently
creating a file with that name is the confusing outcome. An empty path
is refused the same way, and a missing one unless a database handle
stands in for it.
How it performs
Section titled “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
Section titled “Recovery flow”When a PersistentActor starts:
- Load latest snapshot from the snapshot store (if any).
- Run
SELECT payload FROM events WHERE persistence_id = ? AND sequence_nr >= ?to stream events after the snapshot. - 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
Section titled “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-appcp events.db events.db.baksystemctl start my-appOnline backup is preferred — no downtime, consistent snapshot. The standard SQLite tooling applies.
Pitfalls
Section titled “Pitfalls”When SQLite isn’t enough
Section titled “When SQLite isn’t enough”Three signals you’ve outgrown single-file SQLite:
- Multi-node — you need actors on N nodes to share the same event stream. SQLite per-node doesn’t just scale poorly here, it silently corrupts: each node replays its own file, so a moved entity recovers a different history with no error anywhere (the cluster warns — storage locality). Switch to a shared backend: Cassandra, PostgreSQL, or any “shared across nodes” row of the backend matrix.
- Sustained 100K+ events/sec — SQLite can handle it with tuning but you’re at the edges; columnar / distributed engines are designed for it.
- Large events (> 1 MB each) — SQLite stores each event as a
JSON
TEXTpayload; read performance degrades. Consider event compaction (store pointers to external storage) or a journal designed for large payloads.
Where to next
Section titled “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.
