Databases

zapline::db connects your app to Postgres or SQLite through sqlx: a connection pool created at startup, SQL migrations applied automatically, a Db extractor, a readiness check, and database errors that become the right HTTP status.

[dependencies]
zapline = { version = "0.1", features = ["sqlite"] }   # or "postgres", or both
sqlx = { version = "0.8", default-features = false, features = ["derive"] }   # for #[derive(FromRow)]

use serde::Serialize;
use zapline::db::{sqlx, Database, Db};
use zapline::prelude::*;

#[derive(Serialize, sqlx::FromRow)]
struct Post { id: i64, title: String }

#[get("/posts/{id}")]
async fn get_post(db: Db, Path(id): Path<i64>) -> Result<Json<Post>> {
    let post = sqlx::query_as::<_, Post>("SELECT id, title FROM posts WHERE id = ?")
        .bind(id)
        .fetch_one(&*db)   // no row → 404
        .await?;
    Ok(Json(post))
}

fn main() -> std::io::Result<()> {
    App::new()
        .database(Database::from_env())   // DATABASE_URL
        .mount(get_post)
        .run("0.0.0.0:8080")
}

Connecting

Pool settings: .max_connections(n) (default 4 × CPUs, at least 10), .min_connections(n), .acquire_timeout(d) (default 5 s; a request that can't get a connection in time gets a 503).

With both features enabled, Db and Database default to Postgres; use SqliteDb (or Db<sqlx::Sqlite>) and Database::sqlite(url) for SQLite.

Queries

Db dereferences to sqlx::Pool, so every sqlx API works: .fetch_one(&*db), .execute(&*db), and db.begin().await? for a transaction (commit with tx.commit().await?; dropping it rolls back).

? on a sqlx::Error gives:

Resources made by zapline g resource turn the two 409s into errors that name the field: 422 author_not_found for a reference to a row that doesn't exist, 409 email_taken for a duplicate in an email:string:unique column (see Resource fields). The generated write_error function shows how: on a foreign-key violation it checks which parent is missing, on a unique violation it matches the index name (users_email_key).

List endpoints: paging, sorting, filters, search

Listing turns the query string into a safe, paged query. Callers get pagination, sorting, filters and search; you write one expression:

#![allow(unused)]
fn main() {
use zapline::db::{sqlx, Db, ListParams, Listing, Paged};

#[get("/api/posts")]
async fn list(db: Db, params: ListParams) -> Result<Json<Paged<Post>>> {
    let page = Listing::new("posts", "id, title, published, author_id")
        .sortable(["title", "published"])
        .search(["title", "body"])
        .filter_bool("published")
        .filter_int("author_id")
        .fetch(&db, &params)
        .await?;
    Ok(Json(page))
}
}

GET /api/posts?q=rust&published=true&sort=-id&limit=20&offset=40 returns:

{"items": [...], "total": 57, "limit": 20, "offset": 40}

Nothing from the request reaches the SQL text: values are bound as parameters, and sort columns must be on the allow-list. Bad input is a 400 with code invalid_sort, invalid_filter or invalid_page, and the message lists what's allowed.

For per-user or per-tenant lists, .scope("user_id", user.id) adds a condition the request can't remove. zapline generate resource writes a listing() like this for every resource, with sorting on every column but long text, search on the text fields and a filter per string, number, boolean and reference field; a reference (author:references) also gets a nested list, GET /api/authors/{id}/posts, which is listing().scope("author_id", id). It works on SQLite and Postgres, and ListParams documents limit, offset, sort and q in the OpenAPI spec.

Migrations

Migrations are plain SQL files in migrations/, applied in version order, each exactly once (sqlx tracks them in _sqlx_migrations). Each one is a pair: <version>_<name>.up.sql does the change and <version>_<name>.down.sql undoes it.

zapline db new create_posts     # migrations/20261022062406_create_posts.up.sql + .down.sql
zapline db migrate              # apply pending migrations, then exit

The app also applies pending migrations at startup, so zapline dev and deploys need no extra step. Turn that off with Database::from_env().migrate(false), or read them from elsewhere with .migrations("db/migrations"). .migrations(zapline::embed!("migrations")) compiles them into the binary, with the same checksums as the files (One binary). zapline db migrate runs your app with ZAPLINE_MIGRATE=up, so it uses exactly the URL and settings the app uses.

In Docker, copy migrations/ next to the binary (the generated Dockerfiles do).

Writing them for you

zapline g migration writes both halves from the name and field:type arguments (CLI):

zapline g migration create_tags name slug                             # CREATE TABLE / DROP TABLE
zapline g migration add_published_at_to_posts published_at:datetime?   # ADD COLUMN / DROP COLUMN
zapline g migration add_views_to_posts views:int=0                     # ... with the default existing rows get
zapline g migration remove_legacy_from_posts legacy:bool               # DROP COLUMN / ADD COLUMN
zapline g migration backfill_slugs                                     # an empty pair to fill in

A required column added to an existing table gets a default so the rows already there stay valid: the one after =, or '', 0, FALSE; dates, UUIDs, JSON and references need an = value or have to be optional there. :unique and :index work here too (add_slug_to_posts slug:string?:unique) and add the index; the down file, and a later remove_ migration, drop it before the column. When the table came from zapline g resource, its module, pages and test are updated to read and write the new columns, your own edits kept (The resource follows the table). zapline g resource and g auth write their down file (DROP TABLE) too.

Status, rollback, redo, reset

zapline db status               # every migration, applied or not, and edited files
zapline db rollback             # revert the last migration with its .down.sql
zapline db rollback --steps 3   # ... or the last three, newest first
zapline db redo                 # revert the last one and apply it again
zapline db reset                # revert everything, migrate, run the seed (development)

VERSION         NAME                APPLIED AT           STATE                  DOWN
20260101000000  create todos        2026-10-23 08:54:03  applied                yes
20261022062406  add email to users  2026-10-23 08:54:03  CHANGED since applied  yes
20261023090000  create comments     -                    pending                yes

2 applied, 1 pending

CHANGED since applied means the file was edited after it ran: the app refuses to start until it matches again (edit a migration that already shipped by adding a new one instead). zapline db status then exits non-zero and names the file to restore, as it does for an applied migration whose file was deleted, so a CI step can catch both. Nothing is reverted unless every migration involved has a down file; a one-way migration from an older project (<version>_<name>.sql) becomes reversible when you add <version>_<name>.down.sql next to it. zapline db new keeps writing one-way files in a project whose migrations are all one-way, unless you pass --reversible.

Every command but new runs your app with ZAPLINE_MIGRATE=status|up|down:<n>|redo|reset (plus ZAPLINE_SEED=1 for reset), so it uses exactly the URL and settings the app uses; the app does the work and exits instead of serving. The same commands are there in code, for tests and one-off tools:

use zapline::db::{Migrations, MigrationState};

let migrations = Migrations::open("migrations").await?;
migrations.up(&pool).await?;
let pending = migrations
    .status(&pool)
    .await?
    .iter()
    .filter(|m| m.state == MigrationState::Pending)
    .count();
migrations.down(&pool, 1).await?;

Guard rails

Rolling back drops tables, so production is protected twice:

• zapline db rollback, redo and reset refuse --release (a release build) and APP_ENV/ZAPLINE_ENV=production unless you add --force.
• The app itself, built in release mode, refuses ZAPLINE_MIGRATE=down:<n>, redo and reset unless ZAPLINE_ALLOW_DESTRUCTIVE=1 is set (--force sets it).

The MCP tools db_rollback, db_redo and db_reset need confirm=true, so an assistant has to ask first.

Dates, times and UUIDs

Turn on zapline's chrono and uuid features and use zapline::chrono and zapline::uuid in models: sqlx, serde and the OpenAPI schema (format: date, date-time, uuid) all understand them, with no extra dependencies. zapline g resource turns the features on when its fields need them.

use zapline::chrono::{DateTime, NaiveDate, Utc};
use zapline::uuid::Uuid;

#[model]
#[derive(sqlx::FromRow)]
pub struct Event {
    pub id: i64,
    pub day: NaiveDate,             // DATE on Postgres, TEXT ("2026-01-31") on SQLite
    pub starts_at: DateTime<Utc>,   // TIMESTAMPTZ / TEXT
    pub token: Uuid,                // UUID / BLOB
    pub created_at: DateTime<Utc>,  // DEFAULT CURRENT_TIMESTAMP works on both
}

#[model]
pub struct NewEvent {
    pub day: NaiveDate,
    // Accepts RFC 3339 and what <input type="datetime-local"> sends ("2026-01-31T09:30", as UTC).
    #[serde(deserialize_with = "zapline::extract::datetime")]
    pub starts_at: DateTime<Utc>,
}

zapline::extract::optional_datetime is the same for an Option<DateTime<Utc>> field (empty means None).

JSON columns

With the sqlite or postgres feature, sqlx reads and binds serde_json::Value (re-exported as zapline::db::sqlx::types::JsonValue) for a JSONB column on Postgres and a TEXT one on SQLite, and sqlx::types::Json<T> for your own type. zapline g resource settings prefs:json generates such a column.

use zapline::db::sqlx::types::JsonValue;

#[model]
#[derive(sqlx::FromRow)]
pub struct Settings {
    pub id: i64,
    pub prefs: JsonValue,           // JSONB on Postgres, TEXT on SQLite
}

#[model]
pub struct NewSettings {
    // A JSON body sends {"prefs": {"theme": "dark"}}; a form's <textarea> sends the text.
    #[serde(deserialize_with = "zapline::extract::json_text")]
    pub prefs: JsonValue,
}

zapline::extract::json_text parses a string that holds a JSON object or array, reads an empty string as null (so Option<JsonValue> is None; add #[serde(default)]), and leaves other values alone. Malformed JSON is a 422 naming the field. In templates, {{ item.prefs|tojson }} writes the value back as JSON.

Postgres can query into JSONB (WHERE prefs->>'theme' = $1); SQLite has json_extract(prefs, '$.theme'). Neither is portable, so write those queries for the dialect you use.

Seed data

Demo or development data goes in App::seed, after .database(..):

use zapline::db::{Database, Factory};

App::new()
    .database(Database::from_env())
    .seed(|db| async move {
        Factory::new("todos").set("title", "Try zapline").create(&db).await?;
        Factory::new("todos").set("done", true).create_many(&db, 5).await?;
        Ok(())
    })

zapline db seed     # migrate, run the seed once, exit

Normal runs and tests never run it. zapline db seed starts the app with ZAPLINE_SEED=1, like db migrate, so it uses the app's own DATABASE_URL. Running it twice inserts the rows twice. The fullstack template comes with a seed.

Health

use zapline::db::DbCheck;

App::new()
    .database(Database::from_env())
    .plugin(Health::new().check("database", DbCheck::new()));   // /health/ready pings the database

Testing

Each Database::sqlite_memory() is a fresh, empty database with your migrations applied, so tests run in parallel without stepping on each other:

fn app(db: Database) -> App {
    App::new().database(db).mount((list_posts, create_post))
}

fn main() -> std::io::Result<()> {
    app(Database::from_env()).run("0.0.0.0:8080")
}

#[tokio::test]
async fn creates_a_post() {
    let client = app(Database::sqlite_memory()).test();
    let res = client.post("/posts").json(&serde_json::json!({"title": "Hi"})).send().await;
    assert_eq!(res.status(), 201);
}

Test data with Factory

Factory inserts a row and fills in every column you don't set that the database requires (NOT NULL, no default), read from the schema: unique numbers, text like title 7, user7@example.com for email columns, false, the current time, {} for JSON. A required foreign key gets a parent row, made the same way. Set only what the test is about:

use zapline::db::{sqlx, DefaultDb, Factory};

#[tokio::test]
async fn lists_only_published_posts() {
    let client = app(Database::sqlite_memory()).test();
    let pool: sqlx::Pool<DefaultDb> = client.resources().await.require().unwrap();

    Factory::new("posts").set("published", true).create_many(&pool, 2).await.unwrap();
    Factory::new("posts").set("published", false).create(&pool).await.unwrap();   // its author is created too

    let posts: Vec<serde_json::Value> = client.get("/posts").send().await.json();
    assert_eq!(posts.len(), 2);
}

create returns a Record: get("title") as text, int("id"), json() with typed numbers and booleans (compare it with your API's response), or parse::<Post>() into your own type. .null("column") stores NULL. It works on SQLite and Postgres, in tests and in seeds.

If your SQL is portable between SQLite and Postgres you can test against SQLite and run Postgres in production; otherwise point a test at a real Postgres with Database::postgres(url).

Going further

Ecosystem has complete, tested apps for transactions, savepoints and idempotency keys with sqlx, and for SeaORM, Diesel and Leptos.