Autumn supports two production database tiers, and you pick one per app:

  • SQLite — an embedded, single-file database linked directly into your binary. A production deploy is one process plus one data file: no database server to install, secure, patch, or babysit. This is the zero-ops tier for a single host — a small VPS, an appliance, a daemon, or a self-hosted single-binary app via autumn deploy.
  • Postgres — a networked server you run alongside the app. This is the scale-out tier: it unlocks read replicas, native sharding, and multi-replica scheduled tasks — the features that only make sense when more than one process talks to the same data. (Full-text search is not in this list — it now works on both backends, see below.)

Both tiers run the same battery — models and repositories, embedded migrations with the production-safety classifier, durable #[job] background work, #[scheduled] tasks, DB-backed sessions and auth, and autumn db backup/restore. The difference is that on SQLite the coordination primitives that Postgres implements with a networked server collapse to their single-host form, and the genuinely distributed features are refused at boot rather than silently degraded. This guide is the published contract for exactly which is which.

Status. The SQLite runtime has landed. Build the app with the sqlite cargo feature and a sqlite:// target boots, runs its startup migrations, and serves, on a working connection pool with repository CRUD. Postgres remains the default for autumn new; SQLite is an opt-in target.

The Status column in the matrix below is per capability: Available now means verified in this build, Planned — #NNNN names the follow-on slice that wires that subsystem for SQLite. A Planned row does not mean the app refuses to boot — the runtime boots and serves; that one subsystem is simply not wired yet. The boot-refuse guarantees (the "fails fast" rows) hold from the first SQLite-enabled release, so an unsupported configuration never boots into a surprise at first query.


Runtime (behind the sqlite feature)

Enable the runtime by building the application with the sqlite cargo feature (it must be enabled only by the end application, never by a library). Autumn then boots and serves against a sqlite:// URL:

  • No Postgres client. The postgres feature adds pq-sys, tokio-postgres and diesel's Postgres backend. It is a default feature. To compile none of them, turn default features off and do not enable postgres:

    TOML
    autumn-web = { version = "0.11", default-features = false, features = [
        "sqlite", "maud", "htmx", "tailwind", "cache-moka", "http-client", "reporting", "flash",
        # The widget families. Keep the ones the app renders.
        "widgets-search", "widgets-social", "widgets-workflow", "widgets-charts",
    ] }
    

    If you keep default features on, the runtime is still SQLite, but the build also compiles the Postgres client. A build without postgres does not have the Postgres-only items (for example DbApiTokenStore, the pg stores and PgSyncBackend). CI checks a SQLite-only build with cargo tree.

  • Connection type. A RuntimeConnection alias abstracts the backend: diesel_async::AsyncPgConnection by default, and a SyncConnectionWrapper<SqliteConnection> under the sqlite feature. Generated repositories and hand-written queries take &mut RuntimeConnection, so they compile against either backend.

  • Pool pragmas. Each pooled connection is set up with PRAGMA busy_timeout = 5000 (first, so later statements queue on it), PRAGMA journal_mode = WAL, PRAGMA synchronous = NORMAL, and PRAGMA foreign_keys = ON. A read-only SQLite target skips the two writing pragmas (WAL + synchronous). An in-memory database is pinned to a single pooled connection.

  • Read tuning (opt-in). database.mmap_size and database.cache_size_kib add PRAGMA mmap_size and PRAGMA cache_size. Both are off by default. See Memory-mapped reads.

  • Migrations. Startup migrations run through diesel's MigrationHarness on a plain SqliteConnection with no advisory lock (SQLite is single-writer, so there is nothing to coordinate). Only busy_timeout is set on the migration connection — foreign_keys = ON is deliberately omitted there because it breaks table-recreating migrations. This applies to file-backed SQLite only: an in-memory target (sqlite::memory: / :memory: / file::memory:, including cache=shared) with registered startup migrations is refused at boot (std::process::exit(1)), because the schema is applied on a transient migration connection and is lost before the runtime pool anchors it. An in-memory target with no registered migrations is unaffected (it is the default test-harness configuration).

  • Repository CRUD. Generated #[repository] / #[model] CRUD targets SQLite via two seams: maybe_for_update! expands to a plain read on SQLite (which has no SELECT … FOR UPDATE), so a pessimistic-lock read degrades to a plain read while write-write correctness still rests on the optimistic lock_version check plus the pool busy_timeout; and backend_select! { pg => {…}, sqlite => {…} } picks backend-specific SQL for the shapes that differ (multi-row batch insert vs. per-row loop, batched ON CONFLICT upsert vs. per-row upsert, and RETURNING handling). Tenant scoping and lock_version semantics are preserved on both backends.

Memory-mapped reads and the page cache

By default, SQLite reads each page with read() into a private page cache of about 2 MiB for each connection. Autumn does not change this default.

Two settings can make reads faster on a large database that the app reads often. Both are off by default. Both apply only to SQLite. A Postgres pool ignores them and logs a warning.

TOML
[database]
mmap_size = 268435456   # bytes; 0 = off. AUTUMN_DATABASE__MMAP_SIZE
cache_size_kib = 0      # KiB; 0 = SQLite default. AUTUMN_DATABASE__CACHE_SIZE_KIB
  • mmap_size sets PRAGMA mmap_size on each pooled connection. SQLite then reads pages from a memory map of the file. The memory is the shared OS page cache, not heap memory. A full scan does not get faster.
  • cache_size_kib sets PRAGMA cache_size = -<value>. This sets the size of the private page cache of each connection. Use it where mmap is not safe. It is slower than mmap. It uses heap memory for each connection.

Autumn applies both settings to read-only connections too. They do not write to the file. The bundled SQLite caps mmap_size at 2147418112 bytes (about 2 GiB). SQLite uses the cap for a larger value. It does not return an error.

When mmap helps

  • Use it for one large database that gets many random or range reads.

  • Do not use it for small files. Each file is a few pages, and SQLite has those pages in its cache already. A tenant fleet saw no change.

  • Measure first. This bench compares mmap off and on with the bundled SQLite:

    Shell
    cargo bench -p autumn-web --features sqlite --bench sqlite_mmap_read
    

The bench result on a 90 MB file (1 KiB rows, 4-CPU Linux, OS cache warm):

Querymmap offmmap on (1 GiB)
256 random index lookups1.10 ms0.63 ms
Index range over 10% of the rows12.3 ms5.1 ms

Risks of mmap

  • An I/O error stops the process. With mmap, a read error kills the process with SIGBUS. Causes are a disk error, a full disk on a sparse file, or a file that another process truncates. Without mmap, SQLite returns an error and the request fails. Turn mmap on only where you accept this risk.
  • Local storage only. Do not put a mapped database on a network file system. SQLite file locks are not safe there. mmap increases the risk.
  • Address space. SQLite maps the file once for each connection, up to the smaller of mmap_size and the file size. On a 64-bit target this is not a problem. On a 32-bit target, the total is mmap_size × (primary pool size + replica pool size), plus the fleet total. Config validation refuses a total over 1 GiB.
  • SQLite limits. Config validation refuses a mmap_size over i64::MAX and a cache_size_kib over i32::MAX. SQLite cannot read those values.

Continuous replication works with mmap on. The replicator can checkpoint a mapped database. A restore gets all the rows. A fleet has its own keys: see SQLite Database Fleet.

When to choose SQLite vs Postgres

SQLite is an excellent production database when your workload fits inside its two structural constraints. Neither is a bug to be worked around — they are the shape of an embedded, single-file engine, and understanding them is how you choose the right tier.

The single-writer ceiling

SQLite serializes writes: at any instant one writer holds the database, and concurrent write transactions queue behind it (Autumn runs SQLite in WAL mode, so readers never block writers and multiple readers run concurrently — but writes are still one-at-a-time). For the vast majority of apps — a solo developer's SaaS, an internal tool, an appliance, a personal service — write volume never approaches that ceiling and the operational simplicity is a pure win.

Choose Postgres when you expect sustained, high-concurrency write load, many independent writers contending on hot rows, or write throughput that a single serialized writer cannot keep up with. That is the point of the scale-out tier.

The single-host constraint

SQLite is a file on local disk. Every process that reads or writes it must run on the same host with the file on local storage (not NFS/networked filesystems, whose locking cannot be trusted). This is deliberate and per the issue's scope: Autumn's SQLite tier is single-host, single-writer only.

That means the SQLite tier does not do:

  • Multiple app replicas sharing one database over the network. If you need to run more than one process against the same data, that is Postgres.
  • Networked or multi-writer SQLite (LiteFS, rqlite, libsql/Turso) — out of scope; those are different products, not a mode of this tier.
  • Streaming replication to another server. Continuous replication ships the write-ahead log to offsite storage for recovery (durability) — it does not feed a second live process. There is still exactly one writer and exactly one host.

If your deployment is genuinely one host, all of the above are non-constraints and SQLite gives you the whole framework with none of the server toil. If your deployment is (or is about to become) many hosts, choose Postgres — and note that because configuration is uniform across tiers, moving is a config change, not a rewrite.

Rule of thumb

Your deployment is…Choose
One host, one process, zero-ops prioritySQLite
One host, write volume comfortably below a single serialized writerSQLite
One host, many tenants, writes that queue behind each otherSQLite fleet (one writer per tenant)
Multiple replicas / multiple hosts sharing dataPostgres
Read replicas, sharding, or heavy write concurrencyPostgres
You need Postgres-specific FTS features (language-stemming dictionaries), LISTEN/NOTIFY, or cross-host leader electionPostgres

Support matrix

The SQLite glyph below is the eventual single-host contract for each capability; the Status column tells you which slice delivers it and therefore whether it is available today. A row whose Status reads Planned names a subsystem whose SQLite support is still landing in a follow-on slice — the app still boots and serves, that subsystem just is not wired for SQLite yet. Every capability falls into one of three eventual buckets on SQLite:

  • ✅ Works — same behavior as Postgres (the mechanism may differ; the contract does not).
  • ⚠️ Degrades (documented) — works on a single host, with a coordination primitive that collapses to its single-host form. The behavior is defined below, not a silent no-op.
  • ⛔ Fails fast — refused at boot (or at generate time), with an actionable message, never at first query. This is a genuinely distributed feature that has no single-host meaning.
CapabilityPostgresSQLite (eventual)Mechanism / behavior on SQLiteStatus (today)
Core models / CRUD / repositories✅✅Same repository API and query path on the SQLite runtime pool.✅ Available now (behind the sqlite feature)
Embedded migrations + autumn migrate up/down✅✅Startup migrations apply on file-backed SQLite through diesel's MigrationHarness (unlocked); an in-memory target with registered migrations is refused at boot (the migrated schema is lost before the runtime pool anchors it). The autumn migrate CLI up/down still routes through the Postgres advisory-lock path (hold_migration_lock → PgConnection), so it is not available for a sqlite:// URL yet. (The separate declarative autumn schema migrate verb — see Declarative schema — does apply pending migrations on SQLite, unlocked, but only when the CLI was built with the non-default sqlite cargo feature — the default/published autumn binary is Postgres-only and stops with a "rebuild with --features sqlite" error.)⚠️ Partial — startup migrations apply on file-backed SQLite (MigrationHarness; in-memory + migrations boot-refused); the classic autumn migrate CLI up/down is Postgres-only (planned)
autumn migrate check (production-safety classifier)✅✅Offline SQL-file safety linter (reads no DB URL, so it does not fail on a sqlite:// target); its safety rules target Postgres migration semantics — there is no SQLite-specific classification yet.⚠️ Partial — the linter runs (no DB connection), but its rules are Postgres-oriented; no SQLite-specific classification
Migration serialization (concurrent boot)✅ pg_advisory_lock⚠️Startup migrations run unlocked — no advisory lock and no BEGIN IMMEDIATE reservation on the migration path. Concurrent same-host starts are not serialized by an explicit reservation; they rely on SQLite's single-writer semantics plus the pool busy_timeout. (Note: application write-RMW sites do issue BEGIN IMMEDIATE since #1996 — this row is only about the migration path.)⚠️ Not serialized — no advisory lock / no migration-path BEGIN IMMEDIATE; explicit reservation is a known gap (planned)
Sessions + auth (DB-backed)✅✅Session/auth tables live in SQLite; no external store. The generate auth tracked-sessions store binds RuntimeBackend rather than diesel::pg::Pg, so it compiles and runs on either backend, and its migration DDL and scaffolded guide are emitted in the app's own dialect. Its schema.rs block is backend-independent (every column kind the table uses maps to the same diesel sql-type on both backends), not dialect-forked. The cookie-session backends (`[session] backend = "memory""redis") are backend-independent and unchanged. **Still Postgres-only, and out of this row's scope:** the framework DbApiTokenStore (api_tokens— machine tokens, not login sessions) is typedPool<AsyncPgConnection>with Postgres-only DDL, and the--starter saasscaffold pinsAsyncPgConnection` throughout.
Durable #[job] background jobs✅ FOR UPDATE SKIP LOCKED✅jobs.backend = "sqlite": a single-writer claim on the autumn_jobs table in the app's own file — durable and restart-safe, no Redis required. Retries, backoff, dead-lettering, uniqueness windows, concurrency limits, and the job dashboard match Postgres.✅ Available now
#[scheduled] tasks✅ tick-table leader election⚠️scheduler.backend = "in_process" (the default) fires every tick locally, because one process is always the leader. scheduler.backend = "sqlite" leases each tick in a table, so several processes on the host elect exactly one leader per tick.✅ Available now
Distributed lock (autumn_web::lock)✅ pg_advisory_lock⚠️autumn_web::lock::Lock takes a lease row in autumn_locks instead of a session lock, so the processes on the host contend. A live holder renews; a dead one's lock frees at the lease expiry.⚠️ Available now — single-host scope, lease not session, not re-entrant
Feature-flag / experiment cache invalidation✅ polling⚠️In-process invalidation only (a single host has nothing to notify). PgFlagStore / PgExperimentStore are Postgres-only (they open a PgConnection and use pg_notify), so from_database_config returns None on a SQLite target rather than building a store that cannot connect. Autumn never picks a store for you — the app passes one to with_flag_store, so on SQLite pass InMemoryFlagStore (an .expect() on the None now fails at boot instead of at the first flag read). A store built with new(url) refuses a non-Postgres target at its first connect, with the target redacted.⚠️ Available now — in-process only
Runtime config store (runtime_config::pg)✅ pg_advisory_xact_lock⚠️Same shape: PgConfigStore::from_database_config returns None on a SQLite target, and new(url) refuses one at its first connect; pass InMemoryConfigStore or a custom ConfigStore instead.⚠️ Available now — no DB-backed store
ISR regeneration coordinator (static_gen)✅ pg_try_advisory_lock⚠️PostgresIsrCoordinator takes a Pool<AsyncPgConnection>, which a SQLite build's app state cannot produce — it is unreachable under the flip rather than refused. Single-host ISR uses the in-process coordinator, which is what one host needs.⚠️ Available now — in-process coordinator
autumn db backup / restore✅ pg_dump/pg_restore✅Online-safe snapshot of the data file with SQLite's own VACUUM INTO — one transactional statement, safe against a live app, and no external tools. restore verifies the artifact with PRAGMA integrity_check before replacing the file, and clears the stale -wal/-shm. Retention (--keep) and offsite upload (--upload) work unchanged.✅ Available now
autumn db scrub✅✅Runs against the SQLite file, or a .sqlite backup artifact (--artifact), with the same fail-closed classification as Postgres. One transaction per file. VACUUM and a WAL truncate follow the commit, so free pages do not keep the old values. No external tools. --sample is Postgres-only and is refused. See Data scrubbing.✅ Available now
Retention sweeps✅✅autumn db retention sweeps job_history, commit_hooks and experiment_assignments in the SQLite file. The cutoff comes from the database clock. The report has the same shape as on Postgres.✅ Available now
autumn deploy data-file persistence✅✅The data file is persistent state: it lives in app_dir/shared/data and each release is linked at it, so deploy, rollback and release retention never clobber or orphan it. See where a SQLite data file lives.✅ Available now
Read replicas (replica_url)✅⛔Boot-refuse. No networked replicas on a single-file DB — out of scope.✅ Available now — boot-refuse
Sharding / shard directory✅⛔Boot-refuse. Native sharding is Postgres-only.✅ Available now — boot-refuse
autumn-search Postgres store (SearchPlugin::postgres())✅ tsvector + pgvector⛔Boot-refuse. The store runs tsvector, pgvector and advisory-lock SQL. On a SQLite build, boot stops with a message that names the cause, also when [search] enabled = false. A store an app builds without the plugin refuses every query. Install a different search backend with SearchPlugin::backend.✅ Available now — boot-refuse
Full-text search (--searchable / #[searchable])✅ tsvector + GIN✅ FTS5Available now on both backends. Postgres uses a tsvector generated column + GIN index; SQLite uses an external-content FTS5 virtual table with unicode61 tokenization and bm25 ranking (weights from #[searchable(weight=…)]). The --searchable / #[searchable] scaffold generates on both.✅ Available now
Continuous replication + point-in-time restoren/a✅Built into the running process: the write-ahead log is shipped to an S3-compatible or filesystem destination as it is written, and autumn db replica restore rebuilds the database on a fresh box, optionally at a chosen instant. See durability.✅ Available now
Streaming replication to a second live noden/a⛔Out of scope; continuous replication targets storage, not a standby process.Contract (out of scope)
Multi-writer / networked SQLite (LiteFS, rqlite)n/a⛔Out of scope; single-host, single-writer only.Contract (out of scope)

What ships in this slice

The SQLite runtime has landed: a sqlite:// app boots, runs its startup migrations, serves against a working connection pool, and runs repository CRUD, on top of the earlier config detection, boot-time validation, backend-aware generator, autumn doctor awareness, and this published support contract. Available today:

  • sqlite: / file: config recognition + boot-time validation — a SQLite target is recognized and validated when the URL carries one of the accepted schemes: sqlite:///var/lib/app.db (canonical sqlite:// followed by an absolute path), sqlite:app.db (the shorter scheme-only form), sqlite::memory: (in-memory), or file:app.db. A bare filesystem path such as /var/lib/app.db is intentionally not recognized and fails validation — prefix it with sqlite:// (or sqlite: / file:). An in-memory target is recognized for a no-migration configuration, but combining it with registered startup migrations is refused at boot — the migrated schema lives only on the transient migration connection and is gone before the runtime pool anchors it, so a durable deploy must be file-backed. Postgres-only settings (read replicas, shard directory, Postgres-only job/scheduler backends) are refused at boot with an actionable message rather than silently at first query. A Postgres-only API has no config key to screen, so it refuses where it is called instead — see the distributed lock below.
  • Backend-aware DDL generator — autumn generate emits SQLite column types for the supported field kinds (see field-type support).
  • Backend-aware app dependencies — a SQLite app's Cargo.toml gets diesel on its sqlite feature with the bundled libsqlite3-sys, no pq-sys, and autumn-web's sqlite feature, so the generated code actually compiles.
  • UUID primary keys — --id uuid makes a TEXT key. The database sets its value. See UUID primary keys.
  • Generate-time rejections, each naming its tracking issue:
    • ADD COLUMN NOT NULL without a default (on both the add and rollback re-add paths).
    • DROP INDEX emitted before DROP COLUMN on the forward and rollback paths (a plain --index is dropped before its column is removed).
  • Backend-aware generate auth / generate mailer — both now scaffold SQLite-dialect migrations on a SQLite app (INTEGER PRIMARY KEY AUTOINCREMENT, DEFAULT CURRENT_TIMESTAMP, INTEGER foreign keys) instead of being refused, and the generated auth session store is typed against ::autumn_web::RuntimeConnection so it compiles on either backend.
  • Backend-aware generate teams — the organizations / memberships / invitations scaffold, refused on SQLite until now, emits its migration in the app's dialect. The portable parts are shared: the role / status CHECK enums, the UNIQUE (tenant_id, user_id) constraint, and the partial idx_invitations_pending_email unique index (SQLite has had partial indexes since 3.8.0). Its #[repository]/#[model] templates needed no fork — #[repository] binds ::autumn_web::RuntimeConnection, and src/teams/schema.rs uses only sql-types both diesel backends carry — but its route handlers' 19 .for_update() row locks now go through ::autumn_web::maybe_for_update! (diesel implements the locking clause for Postgres and MySQL only), and the generator now selects the SQLite dependency set and enables autumn-web's sqlite feature, without which the app would refuse its own sqlite:// URL at boot.
  • DB-backed sessions store on SQLite — the generate auth tracked-sessions store bounds its query functions by ::autumn_web::RuntimeBackend instead of a hard-coded diesel::pg::Pg, so the scaffolded store compiles and runs against the SQLite RuntimeConnection. The scaffolded docs/guide/session-management.md now hands the operator SQL in the app's own dialect (datetime('now', '-90 days'), SQLite retrofit DDL) instead of Postgres-only NOW() - INTERVAL / BIGSERIAL.
  • autumn doctor SQLite awareness — a SQLite app is no longer nagged about a missing pg_dump or a non-postgres:// URL.
  • Backup, restore and deploy data-file persistence — see backup, restore, scrub, retention and deploy: the data file is persistent state.

Scaffold smoke tests. A scaffolded SQLite app has a SQLite-native smoke test. Under the sqlite feature, autumn_web::test::TestDb is a temp-file SQLite database, so the test needs no Docker and runs in a plain cargo test. See Testing with TestDb.

One support-matrix row is still Planned: durable jobs and #[scheduled] tasks. #1909 added backup/restore and autumn deploy data-file persistence. #2553 added scrub and retention sweeps. A Planned row does not mean the app refuses to boot — the runtime boots and serves; those subsystems are simply not wired for SQLite until their tracking issue lands.


How the degrades behave

These describe the single-host behavior on the SQLite runtime. The runtime now boots and serves, so the behaviors for landed capabilities apply today; those tied to a still-Planned subsystem take effect when that subsystem's SQLite slice lands.

Each ⚠️ row above works on a single host. Here is the exact behavior, so you can reason about it rather than guess.

Migration serialization

On Postgres, concurrent booters race for a pg_advisory_lock so that exactly one process applies pending migrations while the rest wait and then observe no pending work (see Migrations). On SQLite there is only one host, so there is nothing to serialize across. The startup path applies migrations through diesel's MigrationHarness unlocked — there is no advisory lock and, today, no BEGIN IMMEDIATE reservation. Two processes on the same box overlapping during a restart (an old and new binary) are therefore not serialized by an explicit reservation; they rely on SQLite's single-writer semantics plus the pool busy_timeout. An explicit BEGIN IMMEDIATE reservation to close that same-host overlap window is planned, not yet implemented.

Shared-cache mode and write transactions

A SQLite target whose URL carries cache=shared (e.g. sqlite:file:app?mode=memory&cache=shared) enables SQLite's shared-cache mode: several pooled connections multiplex over one database inside the process. Autumn's own test harness uses it deliberately (no files, no cleanup), and it stays supported — but it has a sharp edge you should know about.

Shared cache has its own table-lock protocol, and it is where SQLite's "deferred is the default" stops being harmless: two connections that each read, then write in a deferred transaction can deadlock permanently. The deferred read→write lock upgrade fails with SQLITE_LOCKED (SQLITE_BUSY_SNAPSHOT), which bypasses the busy-timeout handler — so no amount of retrying unblocks it, and the pool's 5-second busy_timeout never gets a say. WAL mode does not fix this; the table-lock protocol is orthogonal to the journal mode. SQLite's own docs now call shared-cache mode "obsolete" and "discouraged", recommending WAL mode instead.

Two things Autumn does about it:

  • A loud boot warning. Building a pool over a cache=shared target logs a WARN naming the deadlock class and the remedies. It is a warning, not a refusal — the test suite needs shared cache — but in production you should treat it as a nudge toward a WAL-mode file database.
  • Db::tx_immediate. The explicit user-facing transaction for write-heavy closures: on SQLite it begins with BEGIN IMMEDIATE, taking the write lock up front through diesel's transaction manager (so nested savepoints keep working). On a WAL-mode (or rollback-journal) file database a concurrent writer then queues on busy_timeout instead of failing its snapshot upgrade with SQLITE_BUSY_SNAPSHOT. Db::tx itself deliberately stays deferred so read-only transactions keep their read concurrency; the generated write-RMW paths (with_lock, update, delete_by_id, find_or_create_by) already issue BEGIN IMMEDIATE since #1996.

Db::tx_immediate is only half a shared-cache remedy. Under cache=shared the first BEGIN IMMEDIATE takes the write transaction before it reads, so the participating writers cannot all read first and then fail the read→write upgrade together. It is not a completion guarantee: a concurrent reader holding a table read lock can still fail that writer's write with SQLITE_LOCKED_SHAREDCACHE, so a retry round need not have a winner. And a second connection's BEGIN IMMEDIATE fails at once with SQLITE_LOCKED_SHAREDCACHE: SQLite never consults the busy handler for SQLITE_LOCKED, so the losers do not queue. On a shared-cache target, concurrent writers must be serialized, or retried with backoff by the application with each attempt through Db::tx_immediate — or, better, move to a WAL-mode file database.

Rule of thumb on a file database: pure reads go through Db::tx, anything that reads-then-writes goes through Db::tx_immediate.

#[scheduled] tasks

The multi-replica scheduler uses a Postgres tick table so that a fleet fires each tick at most once. SQLite has no shared server, so it gets two single-host coordinators instead:

  • scheduler.backend = "in_process" (the default). The single process is always the leader, so every tick fires locally with no coordination round-trip. This is right for the ordinary one-process deployment.
  • scheduler.backend = "sqlite". Each (task, tick) is leased in the autumn_scheduler_leases table in the app's own database file, so several processes on the one host elect exactly one leader per tick. Use it when a web tier and a worker tier run side by side, or across a rolling restart where the old and new process overlap.

The lease carries an expiry, not a session. The row is what makes the tick claimed, and it stays for the whole of scheduler.lease_ttl_secs (default 300) whether the leader finished or died — so a second process whose timer reaches the same tick a moment later cannot run it again. The next acquire reaps the row once it expires.

Set the TTL longer than both the spread between the processes' timers and the longest a tick body can take, so a live leader is never preempted mid-tick.

This is stricter than the Postgres coordinator, whose pg_advisory_unlock frees the tick key the moment the leader finishes.

scheduler.backend = "postgres" is refused at boot under SQLite, with a message naming both substitutes.

Design scheduled tasks to be idempotent regardless of tier.

Distributed lock

autumn_web::lock::Lock is a cluster-wide named lock built on Postgres advisory locks. On SQLite the same API takes a lease row in an autumn_locks table in the app's own file, so it provides single-host mutual exclusion across the processes sharing that file. Three differences a caller can observe:

  • The scope is one host. Processes sharing the database file contend; two hosts do not.
  • It is a lease, not a session. A holder that dies frees the lock at the lease expiry rather than wedging it, and a live holder renews in the background, so a long critical section is not preempted. Postgres releases on connection loss instead.
  • It is not re-entrant. A Postgres session lock can be taken twice on one connection; a second try_lock on the same name in the same process observes None.

lock() polls rather than waiting server-side, because SQLite has no pg_advisory_lock to block in. Tune the interval with with_poll_interval. Because a SQLite deployment is single-host by definition, a lock used for across-host coordination has no counterpart. Every Postgres-only primitive that would imply one — a replica_url, a shard directory, a Postgres job or scheduler backend — is refused at boot, not silently downgraded to a no-op that would let two replicas both believe they hold it.

Feature-flag / experiment cache invalidation

On Postgres, each replica polls the database, so a flag or experiment change reaches every replica within one poll interval. On SQLite the invalidation is in-process only — correct and immediate, because the single host is the only cache there is. See Feature flags and Experiments.

Durable jobs without Redis

This is the headline of the tier. #[job] work is durable and restart-safe on SQLite with no Redis and no Postgres. Set:

TOML
[jobs]
backend = "sqlite"

The queue is the autumn_jobs table in the same SQLite file. A worker claims work with a single-writer claim — one UPDATE … WHERE id = (SELECT … LIMIT 1) RETURNING …, which is the SQLite analogue of FOR UPDATE SKIP LOCKED, because SQLite serializes writers. A crash mid-job leaves the row reclaimable: a claim older than jobs.sqlite.visibility_timeout_ms (default 30s) is re-enqueued, or dead-lettered when its attempts are spent. The runtime creates the table and its indexes at start, so no migration is needed.

Everything the Postgres queue gives you carries over: attempt counting, exponential backoff, dead-lettering, #[job(unique)] windows, #[job(concurrency = N)] limits, named queues and [jobs] pin, the actuator backlog gauges, and the /admin/jobs dashboard — which reads the table, so every process on the host sees the same queue. enqueue_tracked records go in the same file too, so GET /_autumn/jobs/{token} survives a restart and works across a web/worker split.

Three differences from Postgres, all by design:

  • Workers poll, with an in-process wake. An enqueue in the same process wakes a worker at once. The Postgres backend has no such wake. Work that another process enqueued is seen within jobs.sqlite.poll_interval_ms (default 250ms). Lower it for latency, raise it to cut idle wakeups.
  • The queue is host-local, and must be a file. Two processes on one host share it; two hosts do not. A split web/worker role on an in-memory target is refused at boot, because each process would get its own database. Nothing enforces the two-hosts case at boot — the tier's Postgres-only primitives (replica_url, shards, jobs.backend = "postgres", scheduler.backend = "postgres") are each refused, but a second host pointed at the same file over a network filesystem is not detected. Do not do it; see The single-host constraint.
  • History is pruned by the runtime and by autumn db retention. The SQLite job runtime prunes its own tables: expired tracked-job records always, and terminal autumn_jobs rows when retention.job_history is set. The autumn db retention sweep applies the same rule on demand. Leave the window unset and job history is kept forever, exactly as on Postgres.

jobs.backend = "local" (the default) stays the right choice when the work does not need to survive a restart: it is in-process and needs no table. jobs.backend = "postgres" is refused at boot under SQLite, with a message naming the durable substitute. See Jobs.

Backup, restore, scrub, retention

autumn db backup takes an online-safe snapshot of the SQLite file with VACUUM INTO — one transactional statement, so the snapshot is a single consistent point in time even while the app writes, and in WAL mode it neither blocks the writer nor corrupts anything. It needs no external tools: there is no SQLite equivalent of installing postgresql-client.

Shell
autumn db backup --keep 7 --dir /var/backups/myapp

The backup opens the database with the same URI options the app uses (for example vfs=unix-dotfile), so its locks match the app's. It opens the file with mode=rw, so it never creates a database: a file that is removed before the backup opens it fails the run. The --dir path is used exactly, also when it is not UTF-8 or starts with file:.

The run directory is the same one Postgres backups write — manifest.json plus one artifact per target — except the artifact is control.sqlite, a real SQLite database you can open with any tool, and the manifest records "backend": "sqlite". --keep, --upload and autumn db restore offsite:prod/latest behave exactly as they do on Postgres; --format grades Postgres artifacts only and is ignored for a SQLite target (the completion line says SQLite snapshot so there is no ambiguity).

autumn db restore verifies the artifact with PRAGMA integrity_check before touching the database, stages a copy beside the target, verifies that copy too, removes the target's -wal, -shm and -journal — a leftover journal describes pages of the file being replaced, and SQLite would replay it onto the restored one — and renames the copy into place, keeping the target's mode and owner. When the restore cannot give the copy to the old owner (only root can chown) and the mode does not let that owner read and write it, the restore stops before the rename and tells you which account to use. A restore refused at verification leaves the target byte-identical. The production guard is the same as Postgres: a non-dev/test profile needs --force.

Stop the app before restoring. A running process keeps open handles to the old file, so after the rename it serves the pre-restore database and loses every write it makes. This differs from Postgres, where pg_restore goes through the server.

An in-memory database is refused by both: there is no file to snapshot. The check reads the URI as SQLite does: option names and values are percent-decoded, the last mode and the last vfs win, and mode=memory or vfs=memdb is in memory. On Windows, file:///C:/data/app.db names C:\data\app.db.

db scrub and the retention sweeps run against the SQLite file. autumn db scrub --artifact <run-dir> restores a .sqlite backup and scrubs it in one command. Snapshots are the coarse-grained, cross-backend story; for second-granularity durability see Durability: continuous replication below, which composes with them rather than replacing them.

Deploy: the data file is persistent state

autumn deploy never puts your database where a deploy can delete it. A relative sqlite://app.db would otherwise resolve inside the per-release directory — new on every deploy, deleted by release retention — so the deploy keeps the real file in app_dir/shared/data and links each release at the configured path. An absolute path the deploy does not manage is left alone; one inside the app directory but outside shared/, or an in-memory URL, is refused at preflight rather than after the first cutover. An app deployed before this contract existed keeps its file in the serving release; the next deploy stops and prints the one-time move to make, rather than relocating a live database. The full contract is in where a SQLite data file lives.


Durability: continuous replication and point-in-time restore

Snapshot backups bound your data loss at one backup interval — hours, if you back up nightly. Continuous replication bounds it at seconds, from inside the process you already run. No sidecar to install, supervise and monitor; no second binary; no new credential conventions.

TOML
[replication]
enabled = true

[replication.s3]
bucket = "myapp-replicas"
region = "auto"
endpoint = "https://<account-id>.r2.cloudflarestorage.com"
access_key_id_env = "AUTUMN_REPLICA_ACCESS_KEY_ID"
secret_access_key_env = "AUTUMN_REPLICA_SECRET_ACCESS_KEY"
force_path_style = true

Credentials are supplied by env-var indirection: config names the variables, never the secrets, exactly as [backup.offsite] does. Every key also has an AUTUMN_REPLICATION__* override, so an all-env deployment needs no TOML at all. A path = "/mnt/backup-disk/replica" destination replicates to a directory instead — a second disk, an NFS/SSHFS mount, or a bind-mounted volume.

By default replication refuses to share a bucket with the app's own blob storage ([storage.s3]): a lifecycle rule written for user uploads would quietly expire your replicas. Set allow_shared_bucket = true to opt in.

The RPO contract

SettingDefaultWhat it bounds
rpo_secs10Steady-state data loss if the machine dies right now.

One exception to the objective. Opening a new generation compresses and uploads a fresh base snapshot on the replication thread, and a commit made during that upload is not offsite until it finishes — on a large database, longer than rpo_secs. Everything committed before the rollover is shipped first, so only that window is affected. snapshot_interval_secs (default one hour) sets how often it happens.

| sync_interval_secs | rpo_secs / 2 | How often committed frames are shipped. Must not exceed rpo_secs. | | snapshot_interval_secs | 3600 | How long one generation runs before a fresh base snapshot. | | max_wal_bytes | 16777216 | WAL size that forces a checkpoint (and the next WAL index). | | retention_hours | 168 | How far back a point-in-time restore can reach. | | verify_interval_secs | 21600 | How often the replica is proved restorable. |

The contract: at most rpo_secs of committed writes are lost when the machine is destroyed, under normal write load. Only committed transactions are ever shipped — a segment always ends on a commit boundary, so a replica is never half a transaction.

A database fleet. One process ships all its open fleet databases. The bound holds while one pass over these databases fits in sync_interval_secs. With many busy databases in one process, the bound can fail. Monitor worst_lag_seconds in replication:fleet. Set database.fleet.replication_workers. See Replication lag.

That bound covers the machine disappearing. A planned stop — a deploy, a restart, a SIGTERM — loses nothing: once in-flight requests have drained, the replicator ships one final time and shutdown waits for that flush inside the same server.shutdown_timeout_secs budget as the other shutdown phases. If the budget runs out first the process still exits, and says so in the log.

The same bound applies when another machine restores from the replica after a crash, as a fleet failover with restore_missing does, even if the old machine and its disk survive. A request returns when its transaction commits on the local disk. Its write is offsite only after the next sync, so the new machine can be missing up to rpo_secs of writes that already returned success. On a busy fleet the lag can pass rpo_secs; see the fleet's replication.

Two surfaces report it, from two vantage points. autumn db replica status (add --json for monitoring) reads the destination and reports the current generation, how many segments it holds, the instant a restore would land on, and the lag to now — so it works from any machine with the credentials, including one where the app is not running. /actuator/health reports what the running process knows, under the sqlite-replication indicator: lag_seconds, generation, segments_shipped, pending_bytes, last_verified_at.

A fleet app can ask if one write is offsite. fleet.replicated_through(&key) gives an instant. Every commit to that database at or before it is in the replica. fleet.wait_replicated waits for it. Wait for it before you write the second half of a write that spans two databases. Then a takeover cannot lose one half. See Know when a write is in the replica.

What you actually pay for. Steady-state upload is the size of your WAL writes, not the size of your database. The -wal file cannot grow forever, so the replicator checkpoints it once max_wal_bytes is reached — but a checkpoint only opens the next WAL index inside the current generation, which costs nothing extra offsite. A full base snapshot is uploaded once per snapshot_interval_secs (hourly by default), and that interval is also what bounds how much WAL a restore has to replay. Lowering snapshot_interval_secs makes restores faster and uploads larger; raising it does the reverse.

Alerting

The sqlite-replication indicator goes DOWN when lag exceeds three times the configured RPO, when shipping is failing, or when a periodic verification could not restore the replica. An indicator that stays unhealthy past the alerter's grace period is escalated on every channel configured under alerting, with a recovery notice when it clears — no separate replication alert to configure.

Verification is a real restore, not a checksum: on verify_interval_secs the process downloads the replica into a scratch directory, replays it, and runs PRAGMA integrity_check. "Uploaded" is never mistaken for "restorable".

Fresh-box recovery runbook

The disk is gone. On a new machine, with nothing but the autumn binary, your autumn.toml, and the destination credentials in the environment:

Shell
export AUTUMN_ENV=prod
export AUTUMN_REPLICA_ACCESS_KEY_ID=…
export AUTUMN_REPLICA_SECRET_ACCESS_KEY=…

# 1. What does the destination hold, and how fresh is it?
autumn db replica status

# 2. Rebuild the database (writes to the configured database.url).
#    --force clears the production-profile guard; the box is empty, so no
#    --overwrite is needed.
autumn db replica restore --force

# 3. Start the app.
autumn serve

To land before a specific moment — a bad migration, a runaway delete — add a timestamp inside the retention window:

Shell
autumn db replica restore --timestamp 2026-09-02T14:29:00Z --force --overwrite

--force and --overwrite are deliberately separate: --force clears the production-profile guard, --overwrite allows replacing a database file that is already there. A recovery drill that always passes --force therefore cannot silently destroy a live database.

The command prints the instant it actually landed on. A timestamp older than the window is refused, with the oldest reachable instant named, rather than silently rounded to whatever happened to be there.

Restore is gated the same way autumn db restore is: the production profile needs --force, and overwriting an existing database file needs --force whatever the profile. Nothing is written until the rebuilt database has passed PRAGMA integrity_check, so a refused restore never leaves a half-built database in place of a good one.

What makes it safe against a live app

In WAL journal mode SQLite writes the main database file only during a checkpoint. When replication is enabled Autumn's pool therefore sets PRAGMA wal_autocheckpoint = 0 and the replicator becomes the only component that ever checkpoints — which is also why the tier is single-host and single-writer (see the single-host constraint). Two consequences worth knowing:

  • A dead destination costs disk, never data. A checkpoint is attempted only once everything in the WAL is already offsite, so an unreachable endpoint stalls checkpointing and the -wal file grows. Lag climbs, the health indicator goes DOWN, and you get paged. Watch free space on the database's filesystem alongside the lag.
  • Verification needs headroom. A periodic verification restores the whole replica into a scratch directory beside the database, so it transiently needs about as much free space again on that filesystem. Lower verify_interval_secs to 0 if you would rather verify out of band with autumn db replica verify on another box.
  • Do not run a second writer. A sqlite3 shell that checkpoints — or a second app process on the same file — breaks the invariant. The single-host/single-writer contract is not advisory here.

Replication does not block or slow the writer: it reads the -wal with ordinary file I/O, takes no database locks to ship, and its checkpoints use a one-second busy timeout so a contended checkpoint gives up and retries on the next tick rather than holding up a write.

Limits of this slice

  • SQLite only. Postgres deployments have mature continuous-archiving ecosystems (WAL-G, pgBackRest); [replication] is refused at boot on a Postgres target.
  • One destination. Multi-destination fan-out and cross-region policy are out of scope, the same boundary as #1619.
  • Server-side encryption at rest and TLS in transit; client-side encryption of replicated data is a named follow-up.
  • Replication is not a read replica and not clustering — see What is NOT supported on SQLite.

SQLite field-type support

The backend-aware generator maps model field kinds to SQLite storage types at autumn generate time. As of #1924 every field kind maps to a working SQLite column type and a Rust type that compiles; two of them render an autumn-web newtype rather than the crate type Postgres uses (see foreign field types). A future kind with no working conversion is rejected at generate time with an actionable message rather than emitted as output that compiles on Postgres but breaks on SQLite.

Field kindOn SQLiteSQLite typeNote
String / Text✅TEXT
i32✅INTEGER
i64 / references (foreign keys)✅INTEGERReference columns are i64 foreign keys.
bool✅INTEGERStored as 0 / 1.
f32✅REAL
f64✅REAL
Bytea✅BLOB
CivilDateTime (NaiveDateTime)✅Timestamp (TEXT)Core, ungated diesel::sql_types::Timestamp, through autumn_web::db::SqlDateTime.
Timestamp (DateTime)✅TimestamptzSqlite (TEXT)UTC text with a +00:00 offset (2024-01-02 03:04:05.100+00:00), so the column sorts chronologically. Through autumn_web::db::SqlTimestamp.
Date / time✅Date / Time (TEXT)2024-01-02 / 03:04:05.100, through SqlDate / SqlTime.
json / jsonb✅Json (TEXT)diesel's own serde_json::Value conversion.
Attachment / Blob✅TEXTThe blob metadata JSON. Blob is an autumn-web type, so it carries its own Text/Sqlite conversion.
Enum✅TEXTThe generated enum is local to your app, so autumn generate emits its ToSql/FromSql<Text, Sqlite> impls directly.
Uuid✅TEXTRenders as autumn_web::db::sqlite_types::SqliteUuid — see foreign field types.
Decimal✅TEXT + CHECKRenders as autumn_web::db::sqlite_types::SqliteDecimal; the CHECK enforces the declared precision and scale — see foreign field types.

<a id="foreign-field-types-on-sqlite"></a>

Foreign field types on SQLite

Two field kinds render a different Rust type on SQLite:

Field kindPostgresSQLite
Uuiduuid::Uuidautumn_web::db::sqlite_types::SqliteUuid
decimal{p,s}rust_decimal::Decimalautumn_web::db::sqlite_types::SqliteDecimal

uuid::Uuid and rust_decimal::Decimal belong to other crates, and so does every diesel item their SQLite conversion would name, so autumn-web can implement nothing for them — diesel already blanket-implements AsExpression for every Expression, which rules out even a custom SQL type. A newtype is the wrapper diesel prescribes for exactly this case.

Both wrappers are Copy, deref to the wrapped type, convert with From/Into, and are #[serde(transparent)], so Display, FromStr and JSON match the wrapped type exactly:

Rust
use autumn_web::db::sqlite_types::SqliteUuid;

let id: SqliteUuid = some_uuid.into();
assert_eq!(id.to_string(), some_uuid.to_string());
let back: uuid::Uuid = *id;

Both store TEXT, and both write a canonical form, because SQLite compares TEXT byte for byte — two spellings of one value would break = and UNIQUE. SqliteUuid writes hyphenated lowercase; SqliteDecimal writes the normalized decimal, so 19.990 and 19.99 are one row. The value stays numerically exact, which REAL would not.

Precision and scale are enforced by a CHECK. The column is TEXT, so the migration carries a generated CHECK that holds the declared budget — at most scale fractional digits and precision - scale integer digits — that the value is a plain decimal literal at all, and that it is genuinely stored as TEXT, so a hand-written row cannot satisfy the constraint and then fail to load. (That last one matters because TEXT affinity converts an unquoted number but not a blob: x'31392e3939' would otherwise sit in the column spelling 19.99 while refusing to load.) Postgres rounds a value to scale; SQLite has no way to, so it rejects instead. Round before saving (Decimal::round_dp) if your input can carry more digits than the column declares.

A --default on a decimal column is written as a quoted, normalized text literal for the same reason the runtime normalizes: the default and every later write must be the same text, or a row holding its own default would not match a lookup for that value.

Scale is not padded. Postgres NUMERIC(10,2) reads 19.9 back as 19.90; SQLite reads it back as 19.9. The two are numerically equal — format for display rather than relying on the stored scale.

Ordering. A TEXT column sorts lexicographically, so ORDER BY / < / > on a SqliteDecimal column compares strings, not numbers ("9" after "10", "-1.4" before "-1.5"). Sort or range-filter in Rust, or store minor units in an i64, when SQL ordering matters. For this reason a scaffolded index does not offer a sortable header on a decimal column on SQLite — a dead link that stamped a misleading aria-sort would be worse than no control. SqliteUuid is unaffected: hyphenated lowercase text sorts in UUID byte order. Postgres NUMERIC columns are unaffected.

Equality on rows Autumn did not write. = and UNIQUE match the stored bytes. Everything written through these types is canonical, so lookups agree with Rust equality. A row inserted by hand or migrated from elsewhere may not be: SqliteUuid reads any form Uuid::parse_str accepts (braced, URN, unhyphenated, uppercase), and such a row loads correctly but will not match a find_by_… for the same UUID. Write canonical text.

Smoke test. A scaffolded SQLite app's tests/<model>.rs uses the SQLite TestDb (see Testing with TestDb). It is not #[ignore]d, and each test gets its own database.

<a id="uuid-primary-keys-on-sqlite"></a>

UUID primary keys on SQLite

autumn generate model Post --id uuid works on a SQLite app. SQLite has no uuid type and no gen_random_uuid(), so the generator writes:

Sql
id TEXT PRIMARY KEY NOT NULL DEFAULT (lower(hex(randomblob(4))) || '-' || ...)

The default makes a random version-4 UUID in lowercase text. The database makes the id, as on Postgres. NOT NULL is required: SQLite lets a TEXT PRIMARY KEY hold NULL unless the column forbids it. The model field is pub id: SqliteUuid, and the schema type is Text.

A table that you wrote by hand as TEXT PRIMARY KEY has no such default. The schema tools expect the default for a SqliteUuid key, so autumn schema diff reports it as drift on that table. Add the default in a migration to clear it.

On both backends, a UUID key cannot be used with #[repository], --searchable, generate scaffold or comments:commentable. These need an i64 key.

generate auth / generate mailer now generate on SQLite. Historically refused at generate time, both now scaffold SQLite-dialect migrations on a SQLite app (INTEGER PRIMARY KEY AUTOINCREMENT, DEFAULT CURRENT_TIMESTAMP, INTEGER foreign keys — including the --totp, --magic-link, --oauth, and --passkeys tables and the generate mailer --list-unsubscribe suppression table). The generated auth DB-backed session store is typed against ::autumn_web::RuntimeConnection (which resolves to the Postgres connection by default and the SQLite connection under the sqlite feature), so it compiles on whichever backend the app selected. Its query functions bind ::autumn_web::RuntimeBackend for the same reason, and the scaffolded session-management guide emits its SQL in the app's dialect.

generate teams now generates on SQLite. Also historically refused, it emits its organizations / memberships / invitations migration in the app's dialect. The role / status CHECK enums, the UNIQUE (tenant_id, user_id) constraint and the partial idx_invitations_pending_email unique index are portable and shared. Its #[repository]/#[model] templates needed no forking either — the #[repository] macro binds ::autumn_web::RuntimeConnection, and src/teams/schema.rs uses only sql-types both diesel backends carry.

DDL was not the whole of it. The generated route handlers took 19 pessimistic row locks with .for_update(), which diesel implements for Postgres and MySQL only; they now go through ::autumn_web::maybe_for_update!, which is a plain read on SQLite. Write-write correctness then rests on SQLite's single-writer transaction: a second writer that read the same snapshot fails closed with SQLITE_BUSY_SNAPSHOT rather than losing the update, so the "don't remove the sole Owner" and "don't accept one invitation twice" invariants still hold — but under contention a request errors instead of queueing behind a row lock. The generator also now writes the SQLite dependency set (no pq-sys, and the returning_clauses_for_sqlite_3_35 the generated inserts need) and enables autumn-web's sqlite feature, without which the app compiles but refuses its own sqlite:// URL at boot.

auth, mailer --list-unsubscribe, teams and commentable hand-write their CREATE TABLE DDL rather than deriving it from a model's fields, which is the shape #1927 was opened about; notifications and pwa derive theirs through schema_edit. A guard covers all six: it plans each against a SQLite app, applies and rolls back every migration it emits on a real in-memory SQLite, scans the SQL for Postgres-only spellings, and scans the generated Rust for constructs SQLite has no diesel implementation for. Neither scan is redundant. SQLite accepts an unknown type name (falling back to BLOB affinity), so id BIGSERIAL PRIMARY KEY applies cleanly there and simply stops auto-incrementing; and applying SQL cannot see a generated crate that would not compile.

Full-text search now generates on SQLite. The --searchable / #[searchable] scaffold — historically rejected at generate time on SQLite — now emits a backend-appropriate index on both backends: a tsvector generated column + GIN index on Postgres, and an external-content FTS5 virtual table (unicode61 tokenization, bm25 ranking) on SQLite. See Full-text search.

Scaffold smoke tests run on SQLite. The generated-scaffold smoke test (including the duplicate-unique rejection) uses the SQLite TestDb and db.reset_table(...). It is not #[ignore]d, and each test gets its own database.

<a id="testing-with-testdb-on-sqlite"></a>

Testing with TestDb on SQLite

Under --features sqlite, autumn_web::test::TestDb is a temp-file SQLite database. Its API is the same as the Postgres TestDb:

CallEffect
TestDb::new().awaitA new database. Its file is removed on drop.
TestDb::shared().awaitOne database for the test binary. Its file is not removed.
db.pool()The connection pool. Pass it to TestApp::with_db.
db.execute_sql(sql).awaitRun SQL. A statement list is allowed.
db.reset_table("t").awaitDelete all rows and restart the id counter.

Use TestDb::new() for parallel tests that use the same table names. Each call makes a new database. SQLite has no TRUNCATE. reset_table runs DELETE, then clears the row in sqlite_sequence.

Migration mechanics on SQLite

A few SQLite-specific mechanics apply when the generator emits migrations:

  • ADD COLUMN NOT NULL requires a default. SQLite cannot add a NOT NULL column to an existing table without a default value, so a re-add that lacks one is rejected at generate time — on both the up (add) and rollback (re-add) paths — rather than emitting SQL that fails at migrate time.

  • Rollback drops indexes before columns. On the SQLite rollback path the generator emits DROP INDEX before DROP COLUMN, since SQLite will not drop a column that an index still references.

  • Pre-existing indexes are dropped too. A Remove…From… migration replays the project's earlier migrations/*/up.sql into an in-memory SQLite database. It then tests which indexes block the DROP COLUMN, and emits a DROP INDEX IF EXISTS for each one. It reads no SQL text, so composite, partial, expression, quoted and renamed indexes are all handled. down.sql re-creates them after it re-adds the column.

    Two cases stay outside the replay:

    • SQLite does not know an index made by hand outside migrations/. The drop then fails when you apply the migration.
    • A column behind an inline UNIQUE or PRIMARY KEY. SQLite cannot drop it, and so cannot a column that a CHECK, view or trigger uses. generate migration stops with an error. For a constraint, rebuild the table — autumn schema diff --write-migration emits the rebuild.

    Each earlier migration must replay, for any table. If one does not, generate migration stops and names the file.

  • autumn migrate check applies SQLite rules. The safety classifier reads the app's backend. It never recommends CREATE INDEX CONCURRENTLY (no such syntax), does not flag a plain DROP INDEX (a cheap catalog edit, and a precondition of DROP COLUMN), and reports statements SQLite cannot run at a new unsupported risk level, which fails the up.sql gate:

    StatementWrite instead
    Any ALTER TABLE subcommand outside RENAME, ADD COLUMN, DROP COLUMN — ALTER COLUMN, ADD/DROP CONSTRAINT, SET SCHEMA, OWNER TORebuild the table
    ADD COLUMN … NOT NULL with no DEFAULT, or DEFAULT NULLA constant DEFAULT, or a nullable column
    ADD COLUMN with a non-constant DEFAULT — CURRENT_TIMESTAMP, now(), (1+2)A literal, or backfill with UPDATE
    ADD COLUMN with inline UNIQUE / PRIMARY KEY, or GENERATED … STOREDAdd the column, then CREATE UNIQUE INDEX; or use VIRTUAL
    CREATE INDEX … USING / INCLUDE / WITH / TABLESPACEDrop the clause — SQLite indexes are always B-trees
    CREATE TABLE … PARTITION BY / AS IDENTITY / INHERITSINTEGER PRIMARY KEY AUTOINCREMENT; partition in the app
    TRUNCATEDELETE FROM <table>;
    MERGE, SELECT … FOR UPDATE, ON CONFLICT ON CONSTRAINT, a writing CTEINSERT … ON CONFLICT (<columns>); separate statements
    Sequences, types, extensions, materialized views, COMMENT ON, GRANT/REVOKERemove, or gate the migration to Postgres
    DROP INDEX sqlite_autoindex_*Rebuild the table without the constraint

    A table the same migration creates is exempt from the ADD COLUMN rules: it has no rows, so SQLite accepts them. down.sql findings are reported but do not decide the exit code — that migration runs on autumn migrate down.

    One rule Autumn deliberately does not apply: SQLite rejects an added REFERENCES column with a non-NULL default only when PRAGMA foreign_keys is ON, and the migration connection leaves it OFF, so the statement applies.


What is NOT supported on SQLite

These are Postgres-only by design. They are not missing features to be filed as bugs; they are the scale-out tier's reason to exist, and every one of them fails fast at boot with an actionable message:

  • Read replicas (replica_url / replica routing) — a single file has no networked replica to route reads to.
  • Configured shards ([[database.shards]], the shard directory) — see Sharding. The SQLite tier shards differently: a database fleet gives each tenant (or slot) its own file behind the same ShardedDb / #[repository(sharded)] code.
  • Replication to a second live node (a standby process serving from the same data). Continuous replication to offsite storage is supported — see durability — but there is no second process to fail over to.
  • Multi-writer clustering / networked SQLite (LiteFS, rqlite, libsql/Turso) — the tier is single-host, single-writer only.
  • A server-side statement timeout has no SQLite equivalent — diesel's async SqliteConnection exposes no interrupt hook — so a non-zero database.statement_timeout on a sqlite URL is now refused at boot (PoolError::UnsupportedBackend) rather than silently ignored; long-running statements are otherwise bounded by busy_timeout (lock contention) only, not a wall-clock cap.

Now supported on SQLite (previously listed here). Full-text search (the --searchable / #[searchable] scaffold) is available via FTS5, searchable repositories generate and run on SQLite, and version-history (versioned = true) columns are supported on the SQLite runtime with JSON stored as TEXT. A single-record write-RMW site also issues an explicit BEGIN IMMEDIATE write reservation on SQLite (via scoped_immediate_transaction, through diesel's AnsiTransactionManager, so nested transactions become savepoints) — #2034 / #2038. The one remaining BEGIN IMMEDIATE gap is the startup migration path, which still applies unlocked with no explicit reservation (planned).


Fail fast at boot, never at first query

The core promise of the two-tier design is that a mismatched configuration is caught the instant the app starts, with a message that tells you what to fix — never as a runtime surprise on some unlucky code path days later.

  • A Postgres-shaped setting on a SQLite app (or a SQLite target where a Postgres feature is configured — a replica_url, a shard directory, a Postgres-only job/scheduler backend, a multi-replica lock) fails at boot with an actionable diagnostic.
  • A generator that would emit output which compiles on Postgres but breaks on SQLite (for example --sharded) is rejected at generate time, with the reason stated — never silent output that fails later.

So the operational rule is simple: if a SQLite app boots, every feature it is configured to use is supported on SQLite. There is no third state where an unsupported configuration lurks until first use.

The bound on that rule is the word configured. A Postgres-only API that no config key selects — Lock::from_state, PgFlagStore::from_database_config and its siblings — cannot be screened at boot, because nothing at boot knows the app will call it. Those refuse at the call, with a named error rather than a silent no-op: PoolUnavailable for the lock, None for the stores. Both are listed in the support matrix above.


See also