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
sqlitecargo feature and asqlite://target boots, runs its startup migrations, and serves, on a working connection pool with repository CRUD. Postgres remains the default forautumn 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
postgresfeature addspq-sys,tokio-postgresand diesel's Postgres backend. It is a default feature. To compile none of them, turn default features off and do not enablepostgres:TOMLautumn-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
postgresdoes not have the Postgres-only items (for exampleDbApiTokenStore, thepgstores andPgSyncBackend). CI checks a SQLite-only build withcargo tree. -
Connection type. A
RuntimeConnectionalias abstracts the backend:diesel_async::AsyncPgConnectionby default, and aSyncConnectionWrapper<SqliteConnection>under thesqlitefeature. 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, andPRAGMA 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_sizeanddatabase.cache_size_kibaddPRAGMA mmap_sizeandPRAGMA cache_size. Both are off by default. See Memory-mapped reads. -
Migrations. Startup migrations run through diesel's
MigrationHarnesson a plainSqliteConnectionwith no advisory lock (SQLite is single-writer, so there is nothing to coordinate). Onlybusy_timeoutis set on the migration connection —foreign_keys = ONis 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:, includingcache=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 noSELECT … FOR UPDATE), so a pessimistic-lock read degrades to a plain read while write-write correctness still rests on the optimisticlock_versioncheck plus the poolbusy_timeout; andbackend_select! { pg => {…}, sqlite => {…} }picks backend-specific SQL for the shapes that differ (multi-row batch insert vs. per-row loop, batchedON CONFLICTupsert vs. per-row upsert, andRETURNINGhandling). Tenant scoping andlock_versionsemantics 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.
[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_sizesetsPRAGMA mmap_sizeon 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_kibsetsPRAGMA 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:
Shellcargo 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):
| Query | mmap off | mmap on (1 GiB) |
|---|---|---|
| 256 random index lookups | 1.10 ms | 0.63 ms |
| Index range over 10% of the rows | 12.3 ms | 5.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_sizeand the file size. On a 64-bit target this is not a problem. On a 32-bit target, the total ismmap_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_sizeoveri64::MAXand acache_size_kiboveri32::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 priority | SQLite |
| One host, write volume comfortably below a single serialized writer | SQLite |
| One host, many tenants, writes that queue behind each other | SQLite fleet (one writer per tenant) |
| Multiple replicas / multiple hosts sharing data | Postgres |
| Read replicas, sharding, or heavy write concurrency | Postgres |
You need Postgres-specific FTS features (language-stemming dictionaries), LISTEN/NOTIFY, or cross-host leader election | Postgres |
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.
| Capability | Postgres | SQLite (eventual) | Mechanism / behavior on SQLite | Status (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 | ✅ FTS5 | Available 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 restore | n/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 node | n/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(canonicalsqlite://followed by an absolute path),sqlite:app.db(the shorter scheme-only form),sqlite::memory:(in-memory), orfile:app.db. A bare filesystem path such as/var/lib/app.dbis intentionally not recognized and fails validation — prefix it withsqlite://(orsqlite:/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 generateemits SQLite column types for the supported field kinds (see field-type support). - Backend-aware app dependencies — a SQLite app's
Cargo.tomlgets diesel on itssqlitefeature with the bundledlibsqlite3-sys, nopq-sys, andautumn-web'ssqlitefeature, so the generated code actually compiles. - UUID primary keys —
--id uuidmakes aTEXTkey. The database sets its value. See UUID primary keys. - Generate-time rejections, each naming its tracking issue:
ADD COLUMN NOT NULLwithout a default (on both the add and rollback re-add paths).DROP INDEXemitted beforeDROP COLUMNon the forward and rollback paths (a plain--indexis 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,INTEGERforeign keys) instead of being refused, and the generated auth session store is typed against::autumn_web::RuntimeConnectionso 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: therole/statusCHECKenums, theUNIQUE (tenant_id, user_id)constraint, and the partialidx_invitations_pending_emailunique index (SQLite has had partial indexes since 3.8.0). Its#[repository]/#[model]templates needed no fork —#[repository]binds::autumn_web::RuntimeConnection, andsrc/teams/schema.rsuses 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 enablesautumn-web'ssqlitefeature, without which the app would refuse its ownsqlite://URL at boot. - DB-backed sessions store on SQLite — the
generate authtracked-sessions store bounds its query functions by::autumn_web::RuntimeBackendinstead of a hard-codeddiesel::pg::Pg, so the scaffolded store compiles and runs against the SQLiteRuntimeConnection. The scaffoldeddocs/guide/session-management.mdnow hands the operator SQL in the app's own dialect (datetime('now', '-90 days'), SQLite retrofit DDL) instead of Postgres-onlyNOW() - INTERVAL/BIGSERIAL. autumn doctorSQLite awareness — a SQLite app is no longer nagged about a missingpg_dumpor 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=sharedtarget logs aWARNnaming 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 withBEGIN 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 onbusy_timeoutinstead of failing its snapshot upgrade withSQLITE_BUSY_SNAPSHOT.Db::txitself 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 issueBEGIN IMMEDIATEsince #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 theautumn_scheduler_leasestable 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_lockon the same name in the same process observesNone.
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:
[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 terminalautumn_jobsrows whenretention.job_historyis set. Theautumn db retentionsweep 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.
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.
[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
| Setting | Default | What it bounds |
|---|---|---|
rpo_secs | 10 | Steady-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:
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:
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
-walfile grows. Lag climbs, the health indicator goesDOWN, 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_secsto0if you would rather verify out of band withautumn db replica verifyon another box. - Do not run a second writer. A
sqlite3shell 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 kind | On SQLite | SQLite type | Note |
|---|---|---|---|
String / Text | ✅ | TEXT | |
i32 | ✅ | INTEGER | |
i64 / references (foreign keys) | ✅ | INTEGER | Reference columns are i64 foreign keys. |
bool | ✅ | INTEGER | Stored 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 | ✅ | TEXT | The blob metadata JSON. Blob is an autumn-web type, so it carries its own Text/Sqlite conversion. |
Enum | ✅ | TEXT | The generated enum is local to your app, so autumn generate emits its ToSql/FromSql<Text, Sqlite> impls directly. |
Uuid | ✅ | TEXT | Renders as autumn_web::db::sqlite_types::SqliteUuid — see foreign field types. |
Decimal | ✅ | TEXT + CHECK | Renders 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 kind | Postgres | SQLite |
|---|---|---|
Uuid | uuid::Uuid | autumn_web::db::sqlite_types::SqliteUuid |
decimal{p,s} | rust_decimal::Decimal | autumn_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:
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:
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 mailernow 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,INTEGERforeign keys — including the--totp,--magic-link,--oauth, and--passkeystables and thegenerate mailer --list-unsubscribesuppression 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 thesqlitefeature), so it compiles on whichever backend the app selected. Its query functions bind::autumn_web::RuntimeBackendfor the same reason, and the scaffolded session-management guide emits its SQL in the app's dialect.
generate teamsnow generates on SQLite. Also historically refused, it emits its organizations / memberships / invitations migration in the app's dialect. Therole/statusCHECKenums, theUNIQUE (tenant_id, user_id)constraint and the partialidx_invitations_pending_emailunique index are portable and shared. Its#[repository]/#[model]templates needed no forking either — the#[repository]macro binds::autumn_web::RuntimeConnection, andsrc/teams/schema.rsuses 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 withSQLITE_BUSY_SNAPSHOTrather 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 (nopq-sys, and thereturning_clauses_for_sqlite_3_35the generated inserts need) and enablesautumn-web'ssqlitefeature, without which the app compiles but refuses its ownsqlite://URL at boot.
auth,mailer --list-unsubscribe,teamsandcommentablehand-write theirCREATE TABLEDDL rather than deriving it from a model's fields, which is the shape #1927 was opened about;notificationsandpwaderive theirs throughschema_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), soid BIGSERIAL PRIMARY KEYapplies 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: atsvectorgenerated column + GIN index on Postgres, and an external-content FTS5 virtual table (unicode61tokenization,bm25ranking) on SQLite. See Full-text search.
Scaffold smoke tests run on SQLite. The generated-scaffold smoke test (including the duplicate-
uniquerejection) uses the SQLiteTestDbanddb.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:
| Call | Effect |
|---|---|
TestDb::new().await | A new database. Its file is removed on drop. |
TestDb::shared().await | One 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).await | Run SQL. A statement list is allowed. |
db.reset_table("t").await | Delete 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 NULLrequires a default. SQLite cannot add aNOT NULLcolumn to an existing table without a default value, so a re-add that lacks one is rejected at generate time — on both theup(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 INDEXbeforeDROP 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 earliermigrations/*/up.sqlinto an in-memory SQLite database. It then tests which indexes block theDROP COLUMN, and emits aDROP INDEX IF EXISTSfor each one. It reads no SQL text, so composite, partial, expression, quoted and renamed indexes are all handled.down.sqlre-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
UNIQUEorPRIMARY KEY. SQLite cannot drop it, and so cannot a column that a CHECK, view or trigger uses.generate migrationstops with an error. For a constraint, rebuild the table —autumn schema diff --write-migrationemits the rebuild.
Each earlier migration must replay, for any table. If one does not,
generate migrationstops and names the file. - SQLite does not know an index made by hand outside
-
autumn migrate checkapplies SQLite rules. The safety classifier reads the app's backend. It never recommendsCREATE INDEX CONCURRENTLY(no such syntax), does not flag a plainDROP INDEX(a cheap catalog edit, and a precondition ofDROP COLUMN), and reports statements SQLite cannot run at a newunsupportedrisk level, which fails theup.sqlgate:Statement Write instead Any ALTER TABLEsubcommand outsideRENAME,ADD COLUMN,DROP COLUMN—ALTER COLUMN,ADD/DROP CONSTRAINT,SET SCHEMA,OWNER TORebuild the table ADD COLUMN … NOT NULLwith noDEFAULT, orDEFAULT NULLA constant DEFAULT, or a nullable columnADD COLUMNwith a non-constantDEFAULT—CURRENT_TIMESTAMP,now(),(1+2)A literal, or backfill with UPDATEADD COLUMNwith inlineUNIQUE/PRIMARY KEY, orGENERATED … STOREDAdd the column, then CREATE UNIQUE INDEX; or useVIRTUALCREATE 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 appTRUNCATEDELETE FROM <table>;MERGE,SELECT … FOR UPDATE,ON CONFLICT ON CONSTRAINT, a writing CTEINSERT … ON CONFLICT (<columns>); separate statementsSequences, 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 COLUMNrules: it has no rows, so SQLite accepts them.down.sqlfindings are reported but do not decide the exit code — that migration runs onautumn migrate down.One rule Autumn deliberately does not apply: SQLite rejects an added
REFERENCEScolumn with a non-NULL default only whenPRAGMA foreign_keysis 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 sameShardedDb/#[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
SqliteConnectionexposes no interrupt hook — so a non-zerodatabase.statement_timeouton asqliteURL is now refused at boot (PoolError::UnsupportedBackend) rather than silently ignored; long-running statements are otherwise bounded bybusy_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 asTEXT. A single-record write-RMW site also issues an explicitBEGIN IMMEDIATEwrite reservation on SQLite (viascoped_immediate_transaction, through diesel'sAnsiTransactionManager, so nested transactions become savepoints) — #2034 / #2038. The one remainingBEGIN IMMEDIATEgap 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
- Daemon mode:
autumn serve— the single-binary local service shape, database backups, and where state lives. - Alerting — the channels a replication-lag or verification-failure alert is escalated on.
- Deployment and
autumn deploy— persistent-state contract for the SQLite data file. - Migrations — the classifier, checksums, and advisory-lock serialization this guide contrasts against.
- Jobs and Multi-replica scheduled tasks.
- Sharding and Repositories — the Postgres-only scale-out features.
- Full-text search — available now on both
backends (Postgres
tsvector/GIN, SQLite FTS5).