How to map rows to structs

Short answer: add #[derive(FromRow)] to the struct and swap sqlx::query for sqlx::query_as::<_, Dish>("SELECT ...") — every field is read from the column with the same name. The mapping is checked when the query runs, not when the code compiles, so a field whose type does not match its column is a run-time ColumnDecode error, not a compile error.

Where the hand-written mapping stops

The query page ended with a mapping written by hand: a struct on one side, and one row.get("column") per field on the other. For one query that is fine, but every query that returns rows repeats the shape — three fields, three gets — and the two sides drift. Rename a column in the SQL and the compiler says nothing, because the SQL is a string and the get is a string.

FromRow is the trait that holds this mapping, and its derive writes it. Add the same dependencies as the earlier pages in this chapter:

[dependencies]
sqlx = { version = "0.9", features = ["runtime-tokio", "tls-rustls", "postgres"] }
tokio = { version = "1", features = ["macros", "rt-multi-thread"] }

FromRow writes the mapping

The table in this article has two more columns than the struct will read — views BIGINT and notes TEXT — on purpose: they are the two ways a mapping goes wrong later in the page.

use sqlx::postgres::PgPoolOptions;
use sqlx::FromRow;

#[derive(Debug, FromRow)]
struct Dish {
    id: i32,
    name: String,
    price_cents: i32,
}

#[tokio::main]
async fn main() {
    let pool = PgPoolOptions::new()
        .connect("postgres://postgres:dev@localhost:5432/postgres")
        .await
        .expect("failed to connect to Postgres");

    sqlx::query("DROP TABLE IF EXISTS demo_dishes")
        .execute(&pool)
        .await
        .expect("drop failed");
    sqlx::query(
        "CREATE TABLE demo_dishes (
            id          INT PRIMARY KEY,
            name        TEXT NOT NULL,
            price_cents INT NOT NULL,
            views       BIGINT NOT NULL,
            notes       TEXT
        )",
    )
    .execute(&pool)
    .await
    .expect("create failed");
    sqlx::query(
        "INSERT INTO demo_dishes (id, name, price_cents, views, notes) VALUES
            (1, 'edamame', 350, 12, NULL),
            (2, 'ramen', 1450, 4007, 'extra nori')",
    )
    .execute(&pool)
    .await
    .expect("insert failed");

    let dishes: Vec<Dish> =
        sqlx::query_as("SELECT id, name, price_cents FROM demo_dishes ORDER BY id")
            .fetch_all(&pool)
            .await
            .expect("query failed");

    for dish in &dishes {
        println!("{dish:?}");
    }
}

Dish { id: 1, name: "edamame", price_cents: 350 }
Dish { id: 2, name: "ramen", price_cents: 1450 }

query_as is query with a target type: the SQL is identical, fetch_all still borrows a connection and waits for the rows, and what comes back is a Vec<Dish> instead of a Vec<PgRow>. The derived FromRow impl does the per-field get that the manual version wrote out — same decoding, generated from the struct.

Two things to notice. The field name has to match the column name, which is why the SELECT spells its columns out instead of using *. And the struct still describes only what Rust expects: nothing in this file has looked at the schema. So far that is fine, because every type in the struct is the type the column actually holds.

The column type that does not match

views is BIGINT — 8 bytes, i64 in Rust. A struct that reads it as i32 looks perfectly reasonable and compiles without a warning:

use sqlx::postgres::PgPoolOptions;
use sqlx::FromRow;

#[derive(Debug, FromRow)]
struct DishStats {
    id: i32,
    views: i32,
}

#[tokio::main]
async fn main() {
    let pool = PgPoolOptions::new()
        .connect("postgres://postgres:dev@localhost:5432/postgres")
        .await
        .expect("failed to connect to Postgres");

    sqlx::query("DROP TABLE IF EXISTS demo_dishes")
        .execute(&pool)
        .await
        .expect("drop failed");
    sqlx::query(
        "CREATE TABLE demo_dishes (
            id          INT PRIMARY KEY,
            name        TEXT NOT NULL,
            price_cents INT NOT NULL,
            views       BIGINT NOT NULL,
            notes       TEXT
        )",
    )
    .execute(&pool)
    .await
    .expect("create failed");
    sqlx::query(
        "INSERT INTO demo_dishes (id, name, price_cents, views, notes) VALUES
            (1, 'edamame', 350, 12, NULL),
            (2, 'ramen', 1450, 4007, 'extra nori')",
    )
    .execute(&pool)
    .await
    .expect("insert failed");

    match sqlx::query_as::<_, DishStats>("SELECT id, views FROM demo_dishes ORDER BY id")
        .fetch_all(&pool)
        .await
    {
        Ok(rows) => println!("{rows:?}"),
        Err(e) => println!("{e:?}"),
    }
}

ColumnDecode { index: "\"views\"", source: "mismatched types; Rust type `i32` (as SQL type `INT4`) is not compatible with SQL type `INT8`" }

The compile step passed because there was nothing to check: the compiler saw a struct with an i32 in it, and the SQL was a string. The check happens in sqlx’s decoder, after Postgres has already told the driver what each column’s type is. The error is a good one — it names the column, the Rust type on one side, and the Postgres type on the other, using Postgres’ own names (INT4, INT8), which is exactly what \d demo_dishes shows.

The fix is to name the type the column actually is:

#[derive(Debug, sqlx::FromRow)]
struct DishStats {
    id: i32,
    views: i64,
}

With i64 the same query returns the rows. The query! macro page is the other half of the answer: when the query is checked against the real schema at compile time, this mismatch stops the build instead of the first request.

The column that is NULL

notes TEXT has no NOT NULL, and the first row has a NULL in it. A struct that promises a String there compiles and then fails the same way:

use sqlx::postgres::PgPoolOptions;
use sqlx::FromRow;

#[derive(Debug, FromRow)]
struct DishNotes {
    id: i32,
    notes: String,
}

#[derive(Debug, FromRow)]
struct DishNotesOption {
    id: i32,
    notes: Option<String>,
}

#[tokio::main]
async fn main() {
    let pool = PgPoolOptions::new()
        .connect("postgres://postgres:dev@localhost:5432/postgres")
        .await
        .expect("failed to connect to Postgres");

    sqlx::query("DROP TABLE IF EXISTS demo_dishes")
        .execute(&pool)
        .await
        .expect("drop failed");
    sqlx::query(
        "CREATE TABLE demo_dishes (
            id          INT PRIMARY KEY,
            name        TEXT NOT NULL,
            price_cents INT NOT NULL,
            views       BIGINT NOT NULL,
            notes       TEXT
        )",
    )
    .execute(&pool)
    .await
    .expect("create failed");
    sqlx::query(
        "INSERT INTO demo_dishes (id, name, price_cents, views, notes) VALUES
            (1, 'edamame', 350, 12, NULL),
            (2, 'ramen', 1450, 4007, 'extra nori')",
    )
    .execute(&pool)
    .await
    .expect("insert failed");

    match sqlx::query_as::<_, DishNotes>("SELECT id, notes FROM demo_dishes WHERE id = 1")
        .fetch_one(&pool)
        .await
    {
        Ok(row) => println!("{row:?}"),
        Err(e) => println!("{e:?}"),
    }

    let rows: Vec<DishNotesOption> =
        sqlx::query_as("SELECT id, notes FROM demo_dishes ORDER BY id")
            .fetch_all(&pool)
            .await
            .expect("query failed");

    for row in &rows {
        println!("{row:?}");
    }
}

ColumnDecode { index: "\"notes\"", source: UnexpectedNullError }
DishNotesOption { id: 1, notes: None }
DishNotesOption { id: 2, notes: Some("extra nori") }

UnexpectedNullError is the honest version of the problem: the column is NULL, and NULL has no String representation. Option<String> is the type that says so — None where the column is NULL, Some where it is not — and the same program with that struct reads both rows.

Both failures in this page have the same root: the schema lives in the database, and the struct only describes a hope about it. FromRow checks the hope one row at a time at run time; the query! macro checks it once, at compile time, against a schema snapshot. Pick per query — FromRow when the row shape is simple or built dynamically, query! when the compiler can carry the schema for you.