SQLite

SQLite is umbral's default backend. The framework starts there because it gives you a working app with no installed services and no connection string to manage - the first cargo run -- migrate creates a file and writes the schema. Production workloads that outgrow SQLite move to Postgres by changing the DATABASE_URL; the model definitions, QuerySet calls, and migration files all carry over unchanged.

When to use it

Reach for SQLite when:

• You're building locally. cargo run works with no services to start.
• You're running tests. The sqlite::memory: URL gives every test process a fresh database in microseconds. The pattern: each #[tokio::test] calls umbral::db::connect_sqlite("sqlite::memory:").await? and threads the pool through .on(&pool) so the ambient OnceLock stays untouched between tests.
• You're shipping an embedded / single-process app. CLI tools, single-tenant SaaS, internal admin dashboards. SQLite handles meaningful write traffic per process; the concurrency story is "one writer, many readers" at the file level.

Reach for Postgres when you outgrow any of:

• More than one process needs to write concurrently.
• You need Postgres-only field types (arrays, JSONB operators, INET/CIDR/MACADDR, full-text search).
• You want database-enforced per-row security (umbral-rls).
• You want the standard "OLTP at scale" toolchain (logical replication, pgbouncer, point-in-time recovery, etc.).

The migration files and Model impls don't change either way - the move is a DATABASE_URL swap plus a fresh migrate against the new database.

Configure

Three URL forms:

use umbral::prelude::*;
 
#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let settings = Settings::from_env()?;
    // Default DATABASE_URL is `sqlite::memory:`; override with `sqlite://app.db?mode=rwc`
    // (or the env var) to get a file-backed pool that survives restarts.
    let pool = umbral::db::connect(&settings.database_url).await?;
 
    let app = App::builder()
        .settings(settings)
        .database("default", pool)
        .plugin(blog::BlogPlugin)
        .build()?;
 
    app.serve("127.0.0.1:8000".parse::<std::net::SocketAddr>()?).await?;
    Ok(())
}

App::build() cross-checks settings.database_url against the pool's runtime backend. A mismatch (URL says sqlite:// but you handed in a PgPool) is a hard BuildError::DatabaseBackendMismatch at boot.

For callers that want a typed SqlitePool without the DbPool enum wrapper (tests that need to call sqlite-specific APIs straight after connecting):

let sqlite_pool: sqlx::SqlitePool = umbral::db::connect_sqlite("sqlite::memory:").await?;

Production PRAGMAs

connect_sqlite applies a standard set of production PRAGMAs to every connection in the pool - without them a fresh SqlitePool lands on SQLite's safe-but-slow defaults (journal_mode = DELETE + synchronous = FULL), which cost seconds per concurrent INSERT once any other connection touches the file.

Per-statement logging is also turned off (sqlx's default INFO-level logger reads every statement before execution, a measurable per-query overhead under load); the slow statement WARN at the 1-second threshold stays on.

Pooling

The SQLite pool honours the same UMBRAL_DB_* settings as Postgres - max_connections, min_connections, acquire_timeout, idle_timeout, max_lifetime, and test_before_acquire. SQLite is effectively single-writer (WAL serialises writers behind one lock), so a large max_connections mainly buys concurrent readers, but the knobs are honoured uniformly rather than hardcoding a divergent SQLite path. See Connection pooling for the full list and defaults.

Migrations

cargo run -- makemigrations    # writes migrations/<plugin>/0001_…json
cargo run -- migrate           # applies pending migrations against $DATABASE_URL

The engine renders SQLite-appropriate DDL automatically. Differences from the Postgres path:

• INTEGER PRIMARY KEY AUTOINCREMENT for i64 PKs (vs Postgres's BIGSERIAL).
• Nullability changes route through SQLite's "create-new-table-copy-rename" dance instead of ALTER COLUMN. SQLite has no in-place SET NOT NULL; the migration engine hides this.
• The same RETURNING * syntax that drives Manager::create works (SQLite ≥ 3.35).
• ON CONFLICT DO NOTHING works (SQLite ≥ 3.24).

The migration files are backend-agnostic JSON; the same file applies cleanly to both backends.

Querying

let articles = Article::objects().fetch().await?;

The ambient pool reads through whichever DbPool variant App::build() registered. No per-backend code in handler code.

If you hold a SqlitePool directly (test code, scripts outside an App):

let articles = Article::objects().on(&sqlite_pool).fetch().await?;

.on(&sqlite_pool) is the explicit-pool override. The cross-backend terminals (.fetch, .first, .get, .count, .exists) all dispatch on the resolved pool variant - the same terminal works on both backends.

inspectdb (SQLite)

inspectdb introspects via sqlite_master and PRAGMA table_info and produces umbral Model definitions for every table in an existing SQLite database:

cargo run -- inspectdb --output src/legacy
# -> Wrote src/legacy/models.rs
# -> Wrote src/legacy/migrations/app/0001_initial.json

--output names a directory; inspectdb writes a models.rs and a 0001_initial.json there. Useful when porting an existing SQLite-backed app onto umbral: the generated structs feed straight into the declare → migrate loop. Foreign-key detection is still deferred (the SQLite path reads columns only, not PRAGMA foreign_key_list). Round-trip integration coverage lives in crates/umbral-core/tests/inspect.rs. See the inspectdb page for the full flag set.

Limits

SQLite doesn't have:

• Native arrays. A Vec<T> field is Postgres-only; the boot-time system check refuses to start with a clear error pointing at the offending model and field.
• HSTORE / JSONB. The Json SqlType maps to TEXT on SQLite. The QuerySet's JSON-operator surface uses json_extract(col, '$.a.b') instead of Postgres's -> / ->>; the predicate carries both renderings and picks the right one at terminal time.
• Full-text search. The TsVector field is Postgres-only. SQLite has FTS5 as a virtual-table extension, but umbral doesn't surface it as a first-class field type at v1.
• Native UUIDs. UUID columns land on TEXT with the canonical 36-char hex representation. The Uuid Rust type still works transparently - sqlx encodes/decodes it.
• INET / CIDR / MACADDR. All Postgres-only.
• Row-Level Security. umbral-rls logs and skips on SQLite. Use application-layer permissions (umbral-rest's Permissions section).

The cross-backend SqlType catalogue handles the rest - booleans, dates, timestamps, integers, floats, text - with the right native SQLite affinity per column.

Type catalogue

The complete SqlType → SQLite column-type mapping lives in crates/umbral-core/src/backend.rs. Highlights:

See also

• PostgreSQL backend - the production counterpart, including the field types this page lists as missing.
• orm/models - model declaration, the cross-backend SqlType catalogue, and the Postgres-only sections you'll see fail loudly here.