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):

Every table is reactive by default — nothing to opt into. .nonReactive() turns it off for one table on every dialect: no subscriber fires, locally or across instances. The write still happens; only the notification is suppressed. See the schema overview.

• 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

#Numeric primary keys

Schema introspection recognizes INTEGER PRIMARY KEY rowid aliases as non-null, including id({ scheme: 'numeric' }). SQLite may report no explicit NOT NULL flag for these columns; this does not require a nullability migration. Nullable text primary keys and inline INTEGER PRIMARY KEY DESC columns retain their actual nullability. The same interpretation applies to local Turso and libsql.

#Coordinating migration processes

For a file-backed SQLite database, Voltro serializes schema-writing CLI commands, development auto-migration and voltro migrate --create-only through a separate SQLite lock file: <database-file>.voltro-migration-lock. The path is resolved to the actual database file, so a symlink does not create a second independent lock. Read commands and db migrate --dry-run do not acquire this coordinator.

The lock covers the whole job without wrapping application DDL in an extra transaction. SQLite rebuilds and migration bodies that deliberately run without a transaction therefore retain their existing semantics. A competing job retries for approximately 30 seconds before failing; failure or process termination releases ownership. When an interrupted migration is already running non-abortable work, the coordinator retains ownership until that work settles; a waiting job can be interrupted immediately. The small lock database contains no application rows and remains on disk. Do not delete it while migration processes may be running or waiting: recreating its pathname would create an independent lock.

This coordinates local processes using the same database file. It does not enable SQLite cluster workflows, read replicas or cross-process reactive change delivery. In-memory databases have no shared file coordinator, and voltro serve still performs no schema migration.

The first migrating start records the application as the database schema owner. Later starts, including read-only starts, reject a different application name. If the application was deliberately renamed, voltro db claim --yes transfers ownership; this is not permission for unrelated apps to share a schema.

Newly initialized workspaces ignore *.voltro-migration-lock* in Git. Add that pattern to an existing workspace’s .gitignore if its database lives inside the checkout.