Database: one codebase, SQLite and Postgres #

TL;DR

• DATABASE_URL picks SQLite or Postgres at runtime; both drivers are always compiled in; tests run on both.
• Everything goes through SeaORM; backend-specific SQL only in src/kernel/ or behind an explicit backend match.
• SQLite: WAL, foreign_keys = ON, busy_timeout; in-memory test DBs use one connection.
• Write transactions on SQLite start with kernel::db::begin (BEGIN IMMEDIATE), or they fail with "database is locked" when the job worker writes.

Read the rest when touching connections, the kernel's db module, portability, or moving data between backends.

DATABASE_URL's scheme picks the backend at runtime. Nothing is recompiled. Both drivers are always compiled in.

Which backend where #

• Development: SQLite. There's nothing to install and bin/setup just works. Switch to Postgres locally with docker compose up -d db and the Postgres URL in .env when debugging a Postgres-only issue.
• Test: SQLite in-memory by default (fast, isolated). TEST_DATABASE_URL=postgres://… runs the whole suite on Postgres. CI runs both, and both must pass.
• Production: Postgres (managed, from the deploy platform). Single-server apps may run SQLite in production, as Rails 8 does, if the interview chose it: the DB lives on a persistent volume and is backed up (Litestream or the platform's volume snapshots).

kernel/db.rs spec #

pub async fn connect(url: &str, max_connections: u32) -> Result<DatabaseConnection, DbErr>. One path for both backends: sea_orm::Database::connect(ConnectOptions). The file is template/app/src/kernel/db.rs (in an app: src/kernel/db.rs, from .offrails/template/app/), verified with SeaORM 2.0.4 / sqlx 0.9. What it does and why:

• SQLite: create_if_missing, foreign_keys(true) (critical: SQLite ignores FKs otherwise), busy_timeout(5 s), synchronous(Normal), WAL for file databases, a 1 MiB cache_size per connection, idle connections closed after 60 s, and a single connection for in-memory databases (each connection to :memory: is a different, empty database).
• sea_orm::sqlx is the re-export; never a separate sqlx dependency.
• begin is the only way a transaction starts:

/// Every transaction starts here, never with `db.begin()`.
pub async fn begin<C: TransactionTrait + ?Sized>(db: &C) -> Result<C::Transaction, DbErr> {
    db.begin_with_options(TransactionOptions {
        sqlite_transaction_mode: Some(SqliteTransactionMode::Immediate),
        ..Default::default()
    })
    .await
}

• Transactions are BEGIN IMMEDIATE on SQLite (kernel::db::begin; Postgres ignores the mode, and on a transaction it nests as a savepoint). A plain db.begin() is deferred: it reads first, and when it then writes after another connection wrote in between (the job worker's heartbeat writes every few seconds), SQLite fails it with SQLITE_BUSY_SNAPSHOT ("database is locked", code 517). busy_timeout can't retry that. Rails 8 makes the same choice. The in-memory test database has a single connection and can't show the bug, so a kernel test opens a file database with a pool of 4, reads in a transaction, writes from another connection, then writes in the transaction. It fails with db.begin() and passes with kernel::db::begin.

• max_connections comes from DATABASE_POOL (default 4 on SQLite, 10 on Postgres). Always set it: SeaORM defaults SQLite pools to 1 connection.

• opts.acquire_timeout(Duration::from_secs(5)) (sea-orm 2.0.4 ConnectOptions::acquire_timeout; sqlx's default is 30 s). A request that can't get a connection in 5 s fails with DbErr::ConnectionAcquire(ConnAcquireErr::Timeout), which AppError maps to 503 + Retry-After (errors), instead of hanging until the request timeout. Live re-fetches are additionally capped to a third of the pool (realtime).

• The pragmas are applied to every pooled connection (map_sqlx_sqlite_opts), not once per pool.

• TestApp calls connect and then db.set_metric_callback(...) to count queries. The callback needs &mut, so set it before the connection is cloned into AppState.

• A test asserts PRAGMA foreign_keys returns 1 (query_one_raw(Statement::from_string(DbBackend::Sqlite, "PRAGMA foreign_keys"))).

SQLite memory #

Small apps are lean by default (template, memory):

• cache_size is 1 MiB per connection (PRAGMA cache_size = -1024; SQLite's default is 2 MiB). Each pooled connection keeps its own page cache, so the pool size multiplies it. The OS page cache still holds the database file, so a small app loses little. Raise it (e.g. -8192 for 8 MiB) when a read-heavy app's database outgrows the cache and /dev/dashboard or EXPLAIN shows the same pages read again and again.
• The pool is 4 connections on SQLite (DATABASE_POOL; 10 on Postgres). SQLite has one writer at a time, and WAL readers rarely need more. Idle connections close after 60 s, so a burst's extra caches don't stay.
• Postgres is unaffected: its memory is the server's, and DATABASE_POOL sizes the client pool.
• Measured on the bare template (release, macOS, 1000 requests): 15 MiB RSS (tools/ci/memory-budget.sh). Runtime threads are the other lever: APP_WORKER_THREADS and APP_BLOCKING_THREADS (config).

Portability rules #

1. All app queries go through SeaORM's builder. It quotes, parameterizes, and emits the right dialect.
2. Use only portable types: i64, i32, bool, String, DateTimeUtc, Date, Uuid, Json (opaque), Vec<u8>. No arrays, no JSONB operators, no INET, no native enums, no NUMERIC money.
3. No DB-side logic: no triggers, stored procedures, or views. Defaults and timestamps are set in Rust.
4. Case-insensitive matching: lowercase in Rust on write, compare lowercased on read. Never ILIKE.
5. Ordering is explicit. Always order_by a unique column last (id). Backends order ties differently.
6. No reliance on error message text. Use DbErr::sql_err() variants.
7. Booleans and timestamps round-trip through SeaORM correctly on both backends. Don't compare them with raw SQL literals.

Escape hatch #

When a spec needs something the builder can't express portably (full-text search, SKIP LOCKED, upserts with complex conflict targets):

match db.get_database_backend() {
    DbBackend::Postgres => {
        let stmt = Statement::from_sql_and_values(DbBackend::Postgres, "SELECT … WHERE x = $1", [value.into()]);
        db.query_all_raw(stmt).await?        // SeaORM 2: *_raw for Statements (execute_raw, query_one_raw, query_all_raw)
    }
    DbBackend::Sqlite => { /* SQLite SQL with `?` binds, or a simpler portable fallback */ }
    _ => unreachable!("only SQLite and Postgres are supported"),
}

• Both branches are required, and both are tested (the CI matrix covers it).
• Values are always bound ($1 for Postgres, ? for SQLite). Never format! values into SQL.
• Log every escape-hatch use in docs/decisions.md.
• Full-text search: Postgres tsvector generated column + GIN index; SQLite FTS5 virtual table kept in sync by the model fn that writes the row (not by triggers).

Kernel features per backend #

Moving data between backends #

Not automated. Schemas are identical, so for a one-time move (SQLite prod → Postgres), write an app task copy_db <from_url> <to_url> that streams each table in FK order with keyset pagination. Log it in decisions.