Database Overview

The database layer in rust-web-server is a JPA/Hibernate-style async ORM backed by sqlx. All database operations are async fn and require a tokio runtime, which is automatically included when any model-* feature is enabled (they all imply http2).

Feature flags#

Add the flag to Cargo.toml:

[dependencies]
rust-web-server = { version = "18", features = ["model-sqlite"] }

Unlike most feature pairs in this crate, model-sqlite, model-postgres, and model-mysql are not mutually exclusive — enable more than one and each gets its own DbPool variant, so a single binary can hold pools to more than one backend (e.g. SQLite for hot-path data, PostgreSQL for an analytics tier).

Environment variables#

DbConfig::from_env() reads the following variables:

TLS#

DbPool::from_env() also reads how PostgreSQL and MySQL connections use TLS (DbTls::from_env(); SQLite ignores it). A managed database usually requires TLS with a certificate from its own CA: give rws that CA and verify-full.

RWS_DB_HOST=app-db.shop.svc RWS_DB_NAME=app RWS_DB_USER=owner RWS_DB_PASSWORD=… \
RWS_DB_SSL_MODE=verify-full RWS_DB_SSL_ROOT_CERT=/etc/db/ca.crt ./app

In code, DbPool::new_with_tls(config, DbTls { mode: DbSslMode::VerifyFull, root_cert: Some("/etc/db/ca.crt".into()), ..DbTls::default() }). DbPool::new(config) keeps the default, prefer. User and password go to the driver as they are, so they may hold any character (@, :, / broke the connection URL before 18.28.0).

Quick start#

The typical startup sequence is: read config, create pool, run migrations, then serve requests. All pool operations are async fn.

use rust_web_server::model::{DbConfig, DbPool, Value};

#[tokio::main]
async fn main() {
    // 1. Build config from environment variables.
    let config = DbConfig::from_env().expect("database config");

    // 2. Create the async connection pool.
    let pool = DbPool::new(config).await.expect("connection pool");

    // 3. Run pending migrations.
    pool.migrate("migrations/").await.expect("migrations");

    // 4. Use the pool in request handlers (pool is Clone).
    let users: Vec<User> = pool
        .query("SELECT * FROM users WHERE active = ?", &[Value::Bool(true)])
        .await
        .expect("query");

    println!("active users: {}", users.len());
}

DbPool wraps a sqlx::Pool and is cheap to clone — pass it by value into your AppWithState state.

DbPool::from_env().await combines steps 1 and 2, and DbPool::memory().await (model-sqlite) opens an isolated in-memory SQLite pool — see In-memory SQLite.

Manual config#

If you prefer not to use environment variables, build DbConfig directly. backend selects which compiled-in driver this config is for — it only needs to be explicit when more than one model-* feature is enabled; with just one, DbConfig::from_env() (above) infers it for you, but a manually-built DbConfig always needs it stated:

use rust_web_server::model::{Backend, DbConfig};

let config = DbConfig {
    backend: Backend::Postgres,
    host: "localhost".into(),
    port: 5432,
    user: "app".into(),
    password: "secret".into(),
    database: "myapp".into(),
    pool_size: 5,
};

For SQLite, only database (and backend) matter — host/port/user/password are ignored, so any placeholder value works:

let config = DbConfig {
    backend: Backend::Sqlite,
    database: "app.db".into(),   // or ":memory:"
    pool_size: 5,
    host: String::new(),
    port: 0,
    user: String::new(),
    password: String::new(),
};

Multiple backends in one binary#

Enable more than one model-* feature and each gets its own DbPool variant — build one DbConfig/DbPool per backend, each with its own backend:

[dependencies]
rust-web-server = { version = "18", features = ["model-sqlite", "model-postgres"] }

use rust_web_server::model::{Backend, DbConfig, DbPool};

// Hot-path data in local SQLite:
let hot = DbPool::new(DbConfig {
    backend: Backend::Sqlite,
    database: "hot.db".into(),
    pool_size: 5,
    host: String::new(), port: 0, user: String::new(), password: String::new(),
}).await.expect("sqlite pool");

// Analytics tier in PostgreSQL:
let analytics = DbPool::new(DbConfig {
    backend: Backend::Postgres,
    host: "analytics-db.internal".into(),
    port: 5432,
    user: "app".into(),
    password: "secret".into(),
    database: "analytics".into(),
    pool_size: 10,
}).await.expect("postgres pool");

Both pools are ordinary DbPools — pass whichever one a given handler needs through your app state (e.g. as separate fields, or in a small struct: struct Pools { hot: DbPool, analytics: DbPool }). pool.backend() reports which backend a given DbPool is talking to, if you ever need to branch on it at runtime.

When reading config from the environment instead, set RWS_DB_BACKEND before each call to DbConfig::from_env() (or build two separate env namespaces and read them independently) — with more than one model-* feature compiled in, from_env() returns Err rather than guessing which backend you meant.

Pool behaviour#

DbPool::new(config).await creates a sqlx::Pool with max_connections = config.pool_size for whichever backend config.backend names. sqlx manages the connection lifecycle internally — connections are acquired on demand and returned to the pool automatically after each await point. There is no get()/PooledConnection API; just pool.execute(...) and pool.query_rows(...) directly.

Under the hood, DbPool is an enum with one variant per compiled-in backend (Sqlite/Postgres/MySql), plus Replicated for a pool with read replicas — execute, query_rows, begin, and every other method dispatch to the matching variant internally, so calling code never needs to match on it itself unless it specifically wants to know which backend it's holding (pool.backend()). Code that does match on the variants needs a Replicated arm since 18.30.0; pool.close().await closes any pool without matching.

Read replicas#

A pool can send reads to replicas and everything else to the primary (18.30.0):

use rust_web_server::model::DbPool;

let pool = DbPool::with_replicas(
    DbPool::connect("postgres://app:secret@db-primary/app").await?,
    vec![
        DbPool::connect("postgres://app:secret@db-replica-1/app").await?,
        DbPool::connect("postgres://app:secret@db-replica-2/app").await?,
    ],
)?;

Or from the environment: DbPool::from_env() connects the RWS_DB_* database as the primary and, when RWS_DB_REPLICA_URLS is set, each of its comma-separated URLs as a replica (at most RWS_DB_POOL_SIZE connections each):

RWS_DB_BACKEND=postgres RWS_DB_HOST=db-primary RWS_DB_NAME=app RWS_DB_USER=app RWS_DB_PASSWORD=secret \
RWS_DB_REPLICA_URLS=postgres://app:secret@db-replica-1/app,postgres://app:secret@db-replica-2/app

A replica lags behind its primary, so a read right after a write may not see it. Where it must, read from the primary: User::query(pool.primary()), pool.primary().query_rows(…). pool.replica() is the pool the next read goes to, pool.replicas() all of them. Replicas must use the primary's backend; with no replicas every statement goes to the primary.

What's next#

• #[derive(Model)] — map structs to tables
• Repository — CRUD operations
• Query Builder — filtered queries
• Pagination — Page<T> / CursorPage<T>
• Raw SQL — full control
• Transactions — atomic operations
• Migrations — schema versioning
• Relations — HasMany, HasOne, BelongsTo, BelongsToMany, eager with()
• In-memory SQLite — isolated databases for tests