Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 38 ยท Databases with SQLx

Queries and Row Mapping with SQLx

Run SQL from Axum handlers with sqlx::query, query_as and the query! macros, bind parameters safely, map rows with FromRow, and choose fetch_one, fetch_optional or fetch_all, with a runnable mapping stand-in.

With a pool in place you can talk to the database. SQLx offers a ladder of three styles, from the loosest to the strictest: sqlx::query returns raw rows, sqlx::query_as maps rows to your own struct, and the query!/query_as! macros check the SQL against a real database at compile time. All three share the same idea: write plain SQL, pass values as bind parameters, and pick a fetch method that matches how many rows you expect.

SQLx and a database are not available on this site's runner, so the SQLx code is read-only; the runnable examples model the mapping and fetch semantics with an in-memory table.

Binding parameters

Never build SQL by pasting user input into a string. That is how SQL injection happens. Use placeholders ($1, $2 on PostgreSQL; ? on MySQL and SQLite) and .bind():

Rust
// Real code: read-only.
let row = sqlx::query("SELECT id, name FROM users WHERE email = $1")
    .bind(&email)
    .fetch_one(&pool)
    .await?;

The database receives the statement and the values separately, so a value like '; DROP TABLE users; -- is just a string. The difference is easy to see by printing what each approach sends:

Rust Concatenation vs bind parameters (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with io::stdin().read_line()), it gets the next line from this box.

The concatenated version now matches every row; the bound version still compares one literal string.

query_as and FromRow

sqlx::query_as::<_, T> maps every row into T, where T derives sqlx::FromRow. Column names must match field names (use #[sqlx(rename = "...")] otherwise), and field types must be compatible with the column types.

Rust
use serde::Serialize;
use sqlx::FromRow;

#[derive(Debug, FromRow, Serialize)]
struct Todo {
    id: i64,
    title: String,
    done: bool,
    notes: Option<String>, // nullable column
}

async fn list_todos(pool: &PgPool) -> Result<Vec<Todo>, sqlx::Error> {
    sqlx::query_as::<_, Todo>("SELECT id, title, done, notes FROM todos ORDER BY id")
        .fetch_all(pool)
        .await
}

Nullable columns must be Option<T>; a NULL in a non-Option field is a runtime decode error.

Choosing a fetch method

Method Returns Use when
execute QueryResult (rows affected) INSERT/UPDATE/DELETE without RETURNING
fetch_one T, error if no row exactly one row must exist
fetch_optional Option<T> zero or one row (lookups by id)
fetch_all Vec<T> a bounded list
fetch async Stream<T> large results processed row by row

fetch_one with no matching row yields sqlx::Error::RowNotFound. In a handler you usually want fetch_optional and then turn None into a 404 yourself.

Rust Mapping rows and picking a fetch method (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with io::stdin().read_line()), it gets the next line from this box.

The same table of choices applies to real SQLx: fetch_optional returns Ok(None), while fetch_one turns an empty result into an error.

Compile-time checked queries

sqlx::query! and sqlx::query_as! connect to the database while compiling (using DATABASE_URL), check that the SQL is valid and that the columns exist, and generate an anonymous struct with exactly the right types.

Rust
// Real code: read-only.
let todo = sqlx::query_as!(
    Todo,
    "SELECT id, title, done, notes FROM todos WHERE id = $1",
    id
)
.fetch_optional(&pool)
.await?;

A typo in a column name becomes a compile error rather than a 500 in production. The cost is that builds need a database, or cached metadata: run cargo sqlx prepare to write .sqlx/ files you commit, then set SQLX_OFFLINE=true in CI. Nullability is inferred; override it with column aliases like notes AS "notes?" or id AS "id!" when SQLx guesses wrong.

Dynamic filtering

Search endpoints need optional filters. Use QueryBuilder to append SQL pieces while still binding values:

Rust
use sqlx::QueryBuilder;

let mut qb = QueryBuilder::new("SELECT id, title, done, notes FROM todos WHERE 1=1");
if let Some(done) = filter.done {
    qb.push(" AND done = ").push_bind(done);
}
if let Some(q) = &filter.q {
    qb.push(" AND title ILIKE ").push_bind(format!("%{q}%"));
}
qb.push(" ORDER BY id LIMIT ").push_bind(50i64);
let todos = qb.build_query_as::<Todo>().fetch_all(&pool).await?;

Only trusted string literals go into push; every user value goes through push_bind. You will use these pieces to build the endpoints in CRUD endpoints with SQLx.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why use .bind() instead of formatting values into the SQL string?

    Bound values are never parsed as SQL, so hostile input cannot change the statement.
  2. 2. A column can be NULL. How should the struct field be typed?

    SQLx decodes NULL into None only for Option types; a NULL in a plain String field is a decode error.
  3. 3. Which fetch method fits "find a user by id, which might not exist"?

    fetch_optional returns Ok(None) when no row matches, letting you answer 404 cleanly.
  4. 4. What does query_as! do at compile time that query_as does not?

    The macros connect to the database during compilation (or use prepared .sqlx metadata) and verify the SQL and column types.

Summary

  • Use placeholders and .bind() for every value; never concatenate input into SQL.
  • query_as plus #[derive(FromRow)] maps rows to structs; nullable columns are Option<T>.
  • Pick execute, fetch_one, fetch_optional, fetch_all or fetch by how many rows you expect.
  • query!/query_as! check SQL at compile time; commit .sqlx data for offline builds.
  • QueryBuilder builds dynamic filters while binding values.

Next: CRUD Endpoints with SQLx