PostgreSQL

PostgreSQL is umbral's first-class production backend. The plain Article::objects().fetch() you write against SQLite runs unchanged against Postgres; the migration engine emits Postgres-flavoured DDL; and a set of Postgres-only field types (arrays, INET/CIDR/MACADDR, JSONB operators, full-text search) opens up the engine where the workload calls for it.

Configure

Point DATABASE_URL (env or umbral.toml) at a postgres:// (or postgresql://) URL. umbral::db::connect dispatches on the URL scheme and returns the matching [DbPool] variant.

use umbral::prelude::*;
 
#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let settings = Settings::from_env()?;
    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 postgres:// but the pool is DbPool::Sqlite, or vice versa) is a hard BuildError::DatabaseBackendMismatch at boot, not a silent failure on the first query.

let settings = Settings::from_env()?;
let pool = sqlx::postgres::PgPoolOptions::new()
    .max_connections(settings.db_max_connections)
    .min_connections(settings.db_min_connections)
    .acquire_timeout(std::time::Duration::from_secs(settings.db_acquire_timeout_secs))
    .connect(&settings.database_url)
    .await?;
let app = App::builder()
    .settings(settings)
    .database("default", pool)
    .build()?;

Migrations

cargo run -- makemigrations    # writes a JSON migration under migrations/<plugin>/
cargo run -- migrate           # applies pending migrations against $DATABASE_URL

The engine renders Postgres DDL automatically:

• BIGSERIAL for i64 primary keys (vs INTEGER PRIMARY KEY AUTOINCREMENT on SQLite).
• ALTER TABLE ... ALTER COLUMN SET / DROP NOT NULL for nullability changes (vs SQLite's table-recreation dance).
• $1..$N placeholders and ON CONFLICT DO NOTHING on the migration apply path.
• Native types from the Type catalogue below - JSONB, UUID, TIMESTAMPTZ, arrays, INET/CIDR/MACADDR, TSVECTOR - instead of the TEXT/BLOB fallbacks SQLite uses.

The migration files themselves are backend-agnostic JSON; the same file applies to both backends. The engine renders the backend-specific SQL at migrate time from the active pool's backend.

Querying

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

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

If you hold a PgPool directly (integration tests, scripts that don't run through App::build()):

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

.on_pg(&pool) is the Postgres counterpart of .on(&sqlite_pool). The _pg variants on the QuerySet (fetch_pg, first_pg, get_pg, count_pg, exists_pg) take an explicit PgPool and skip the dispatch entirely - useful for models whose fields are Postgres-only and so don't satisfy the dual FromRow bound.

Postgres-only field types

Some workloads benefit enough from native PG features to make the SQLite story irrelevant. Umbral exposes them as first-class field types; the boot-time system check refuses to start if you register one against a SQLite pool, with a clear error pointing at the offending model.

Plus the standard cross-backend types (Boolean, Uuid, Timestamptz, etc.) all map to their native Postgres equivalents (BOOLEAN, UUID, TIMESTAMP WITH TIME ZONE) on this backend.

inspectdb (Postgres)

inspectdb walks pg_catalog and information_schema to produce umbral Model definitions for every table in an existing database:

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

--output is a directory: inspectdb writes a models.rs with one #[derive(Model)] struct per table plus a 0001_initial.json migration, ready to drop into a module and register from a plugin (or via .model::<T>()). Round-trip integration coverage lives in crates/umbral-core/tests/postgres_inspect.rs. See the inspectdb page for the full flag set (--mark-applied, adopting a populated DB).

Row-Level Security

Postgres's Row-Level Security is the right tool for per-row access control that the database enforces regardless of how the row is queried. The umbral-rls plugin (Row-Level Security (umbral-rls)) wraps the CREATE POLICY shape and runs at Plugin::on_ready time. On a SQLite backend the plugin logs and skips, so RLS-using apps stay dev-portable.

Type catalogue

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

See also

• SQLite backend - the dev / test counterpart.
• Row-Level Security (umbral-rls) - Postgres-only policy plugin.
• orm/models - Postgres-only column types - array / JSON / network / full-text field declarations and operator surface.