# Database Overview

> Async SQLite, PostgreSQL, and/or MySQL — even more than one at once — with a built-in connection pool via sqlx.

Source: https://rws8.tech/docs/database/overview/ · Last updated 2026-11-04

The database layer in `rust-web-server` is a JPA/Hibernate-style async ORM backed by [`sqlx`](https://github.com/launchbadge/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

| Flag | Driver | Notes |
|---|---|---|
| `model-sqlite` | `sqlx/sqlite` | Best for development and embedded use; implies `http2` |
| `model-postgres` | `sqlx/postgres` | Standard PostgreSQL driver; implies `http2` |
| `model-mysql` | `sqlx/mysql` | MySQL and MariaDB; implies `http2` |

Add the flag to `Cargo.toml`:

```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`](#multiple-backends-in-one-binary) 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:

| Variable | Default | Notes |
|---|---|---|
| `RWS_DB_BACKEND` | Inferred if exactly one `model-*` feature is compiled in | `"sqlite"` / `"postgres"` / `"mysql"`; **required** if more than one `model-*` feature is compiled in |
| `RWS_DB_HOST` | `localhost` | Ignored for SQLite |
| `RWS_DB_PORT` | `5432` | Ignored for SQLite |
| `RWS_DB_USER` | — | Ignored for SQLite |
| `RWS_DB_PASSWORD` | — | Ignored for SQLite |
| `RWS_DB_NAME` | **required** | File path for SQLite; use `:memory:` for in-memory |
| `RWS_DB_POOL_SIZE` | `10` | Maximum number of connections in the pool |
| `RWS_DB_REPLICA_URLS` | — | Read replicas, comma-separated database URLs (`DbPool::from_env` only; see [Read replicas](#read-replicas)) |

### 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`.

| Variable | Default | Notes |
|---|---|---|
| `RWS_DB_SSL_MODE` | `prefer` | `disable`, `prefer` (TLS if the server offers it, certificate not checked), `require` (always TLS, not checked), `verify-ca` (signed by a trusted CA), `verify-full` (and names the host). MySQL's spellings (`required`, `verify_identity`) work too |
| `RWS_DB_SSL_ROOT_CERT` | the system's roots | PEM file with the CA certificates that sign the server's certificate |
| `RWS_DB_SSL_CLIENT_CERT` | — | PEM client certificate, for servers that require one; needs `RWS_DB_SSL_CLIENT_KEY` |
| `RWS_DB_SSL_CLIENT_KEY` | — | Its private key |

```bash
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`.

```rust
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](/database/in-memory/).

## 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:

```rust
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:

```rust
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`](#pool-behaviour) variant — build one `DbConfig`/`DbPool` per backend, each with its own `backend`:

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

```rust
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 `DbPool`s — 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](#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):

```rust
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):

```bash
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
```

| Goes to a replica (round robin) | Goes to the primary |
|---|---|
| `query_rows`, `query`, `query_raw` | `execute`, `begin`, `transaction` (reads inside a transaction too) |
| `QueryBuilder::fetch_all` / `fetch_one` / `count` / `paginate` / `paginate_after` | `QueryBuilder::update` / `delete` / `force_delete` / `restore` |
| `Repository::find_by_id` / `find_all` / `count` / `exists_by_id` | `Repository::save` (it reads the saved row back from the primary), `delete_by_id`, factories' `create` |
| relation loads and `with()` | `migrate`, `migration_status`, `rollback`, `seed`, `seed_with`, `schema` |

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)]](/database/model-derive/) — map structs to tables
- [Repository](/database/repository/) — CRUD operations
- [Query Builder](/database/query-builder/) — filtered queries
- [Pagination](/database/pagination/) — `Page<T>` / `CursorPage<T>`
- [Raw SQL](/database/raw-sql/) — full control
- [Transactions](/database/transactions/) — atomic operations
- [Migrations](/database/migrations/) — schema versioning
- [Relations](/database/relations/) — HasMany, HasOne, BelongsTo, BelongsToMany, eager `with()`
- [In-memory SQLite](/database/in-memory/) — isolated databases for tests
