SQLite 3.38+

SQLite is the framework's single-process dialect — Dev environments, embedded apps, CLIs, edge-deployed single-tenant tools. The architectural trade-offs are different from the server dialects: no cluster scale-out, no read replicas, sub-millisecond CDC. The framework documents these explicitly because users frequently underestimate the inherent single-process constraint.

#Target version

SQLite 3.38 or later. Two reasons:

1. json_object + json_group_array (the framework's eager-load idiom on sqlite) stabilized in 3.38. 3.37.x supports them but with subtle ordering quirks.
2. RETURNING * on DML was added in 3.35. The framework relies on it.

better-sqlite3 (the bundled driver) ships modern SQLite versions — version targeting is more about your build environment than the runtime.

#Configuration

DB_DIALECT=sqlite

# File backend:
DB_URL=file:./db.sqlite       # relative path (resolved against cwd)
DB_URL=file:/abs/path/db.sqlite

# Ephemeral in-memory backend:
DB_URL=:memory:

No host, no port, no credentials. SQLite is process-local.

#Driver: @effect/sql-sqlite-node (better-sqlite3)

Synchronous driver under the hood — the framework wraps every call in Effect.tryPromise so the API surface stays Promise-based. Performance is excellent for single-process workloads (no inter-process IPC, no network round-trips).

The driver returns INTEGER for BOOLEAN columns (sqlite stores them as 0/1) and TEXT for everything else (no native DATE / TIMESTAMP / JSON types). The framework's decodeRowsFromSchema post-processor handles all three:

• boolean() columns: 0/1 → false/true
• json() columns: TEXT → object (JSON.parse)
• timestamp() / date() columns: ISO string → Date

On write, coerceForSqlite converts Date → ISO string and boolean → 0/1 before binding.

#No cluster — runnerStorage: 'memory'

SQLite is single-process by definition. @effect/cluster's SqlRunnerStorage requires a coordinatable lock primitive (advisory locks, GET_LOCK, sp_getapplock) — SQLite has none.

The framework boots the workflow engine with runnerStorage: 'memory' when the dialect resolves to sqlite. What this means:

• Workflows run durably within ONE process. Crash + restart resumes from the persisted journal.

• Multi-replica deployments are not possible. The cluster's getRunners() returns only the local instance; there's no shard re-assignment because there's no second runner to assign to.

• Boot log surfaces this:

  [voltro:dev] workflow engine: cluster-memory, dialect=sqlite — single-process durable replay, no horizontal scaling

For SQLite use cases this constraint is usually intentional — a CLI tool that runs workflows during a single command execution, a desktop app where the whole framework lives in the same process. If you're considering multi-replica scale-out you've outgrown SQLite; switch to postgres / mysql / mariadb / mssql.

#No read replicas

SQLite is single-writer by definition. Setting DB_REPLICA_URLS=… is a no-op with a warning:

[voltro:dev] read replicas: not applicable (sqlite is single-process); ignoring DB_REPLICA_URLS

#CDC — in-process EventEmitter

Sqlite has no LISTEN/NOTIFY equivalent and no trigger-based fan-out is needed (everything runs in one process). The framework's SqliteStore uses an in-process Node EventEmitter (composed as a private field, not subclassed):

• Insert/update/delete emit 'change' events synchronously to the dispatcher.
• The dispatcher's onChange callback is registered against the emitter — no polling, no triggers, no log table.

Latency: sub-millisecond. Bounded by Node's event-loop tick.

This is the FASTEST CDC path the framework offers. The tradeoff is the inherent single-process constraint — there's no cross-process or cross-machine fan-out to worry about.

#JSON columns — TEXT with auto-coerce

json() columns map to TEXT in DDL. SQLite's optional json extension validates content via the JSON1 functions but enforces no type — TEXT is what you get on the wire.

The framework's decodeRowsFromSchema JSON.parses any column declared as json() in the schema registry. Writes go through JSON.stringify in the mutation middleware. Application code sees objects on both sides.

#Identifier quoting

"name" — double quotes. Same as postgres.

#Migration emitter

voltro migrate against sqlite emits:

• CREATE TABLE IF NOT EXISTS …
• CREATE INDEX IF NOT EXISTS …
• FK constraints via REFERENCES … ON DELETE CASCADE/RESTRICT/SET NULL (sqlite supports these since PRAGMA foreign_keys = ON, which the framework sets at connect)
• INTEGER PRIMARY KEY AUTOINCREMENT for numeric ids
• TEXT for ids, text, timestamp, date, json, references
• INTEGER for booleans
• BLOB for vectors

The _voltro_migrations ledger is a regular table keyed by migration id — re-running the same migration is a no-op. WAL mode is enabled at connect (PRAGMA journal_mode = WAL) for better concurrency and crash safety.

#File vs :memory:

• :memory: — ephemeral, lives in the process's address space, dies on exit. Use for tests + smoke fixtures.
• file:./db.sqlite — durable, lives at the filesystem path. The framework auto-creates the file on first write. WAL mode means you'll see db.sqlite-wal + db.sqlite-shm sidecars; that's expected.

When you copy or back up a sqlite file, capture all THREE files together (the WAL contains uncommitted-to-main writes). Or run PRAGMA wal_checkpoint(FULL) first to fold the WAL back into the main file.

#Known caveats

• PRAGMA foreign_keys is OFF by default. The framework turns it on at every connect; if you open the database via another tool (sqlite3 CLI, DBeaver) and run mutations, you bypass FK enforcement.
• Single-writer. SQLite serializes writes — a long-running write blocks every other write on the same database file. WAL mode helps readers (they don't block) but doesn't help writers.
• Date arithmetic is string-based. timestamp() columns store ISO-8601 text; comparing two timestamps is lexical (which works because ISO-8601 sorts correctly) but date math requires the framework's higher-level API, not raw SQL.
• No native DECIMAL. The framework doesn't ship a decimal type yet — integer() and number() (floating-point) are it. Money values: integer cents.

#Where it lives

• voltro/packages/sql-sqlite/src/store.ts — SqliteStore with EventEmitter CDC + coerceForSqlite on write
• voltro/packages/sql-sqlite/src/retry.ts — isRetryableSqliteFailure (SQLITE_BUSY / SQLITE_LOCKED)
• voltro/packages/database/src/migrate.ts — sqlite DDL branch (line 46)
• voltro/packages/database/src/jsonEagerCompiler.ts — compileSqliteEntry using json_object / json_group_array
• voltro/packages/database/src/rowDecoder.ts — read-path JSON / boolean / Date coercion against schema
• voltro/packages/workflow/src/clusterLayer.ts — runnerStorage: 'memory' branch for sqlite