Databases with sqlx

sqlx is the most popular way to use SQL databases from Rust. It isn't an ORM: you write SQL, and sqlx sends it with bound parameters and maps the resulting rows to your structs. It's fully async, has a built-in connection pool, and supports PostgreSQL, MySQL, and SQLite with the same API.

The examples use SQLite in memory, so they run anywhere without a database server, which also makes them a good fit for tests. Moving to PostgreSQL mostly means changing the connection URL, the pool type, and the parameter placeholders ($1 instead of ?).

Terminal

cargo add sqlx --features runtime-tokio,sqlite   # or: postgres, mysql
cargo add tokio --features full

Task	sqlx
Connection pool	`SqlitePool`, `PgPool` (built-in pool)
Bind a parameter	`.bind(x)`
Run an `INSERT`, `UPDATE` or `DELETE`	`.execute(&pool).await?` → `rows_affected()`
Map rows to structs	`#[derive(FromRow)]` + `query_as`
Read a single value	`query_scalar`
Transactions	`pool.begin()` … `tx.commit()`
Detect a duplicate key	`is_unique_violation()` on the database error
Schema migrations	sqlx migrations
ORM	none in sqlx; SeaORM and Diesel are Rust's ORMs

Part 1: Queries

1. Connecting

A pool opens connections on demand and hands them out to queries. An in-memory SQLite database exists only inside the connection that created it, so these examples limit the pool to one connection. With a file (sqlite://shop.db?mode=rwc) or PostgreSQL (postgres://user:pass@host/db with PgPoolOptions), a pool of several connections is the normal setup.

Rust

use sqlx::sqlite::{SqlitePool, SqlitePoolOptions};

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool: SqlitePool = SqlitePoolOptions::new().max_connections(1).connect("sqlite::memory:").await?;

    let answer: i64 = sqlx::query_scalar("SELECT 40 + 2").fetch_one(&pool).await?;
    println!("the database says {answer}");

    // `raw_sql` runs one or more statements without parameters, handy for schemas.
    sqlx::raw_sql("CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, cents INTEGER NOT NULL); CREATE INDEX products_name ON products (name);")
        .execute(&pool)
        .await?;
    let tables: Vec<String> = sqlx::query_scalar("SELECT name FROM sqlite_master WHERE type = 'table'").fetch_all(&pool).await?;
    println!("tables: {tables:?}");
    Ok(())
}

Output

the database says 42
tables: ["products"]

In an application, create the pool once at startup and share it: it's cheap to clone, and axum handlers receive it as state (example 14).

2. Inserting with bind parameters

sqlx::query prepares a statement; each ? placeholder receives a value from .bind(...), in order. execute runs it and reports how many rows changed. To get a generated ID, read last_insert_rowid() (SQLite) or add RETURNING id (SQLite and PostgreSQL) and fetch it like a query result.

Rust

use sqlx::sqlite::SqlitePoolOptions;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = SqlitePoolOptions::new().max_connections(1).connect("sqlite::memory:").await?;
    sqlx::raw_sql("CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, cents INTEGER NOT NULL)").execute(&pool).await?;

    let result = sqlx::query("INSERT INTO products (name, cents) VALUES (?, ?)").bind("desk lamp").bind(4_990).execute(&pool).await?;
    println!("inserted {} row, id {}", result.rows_affected(), result.last_insert_rowid());

    let id: i64 = sqlx::query_scalar("INSERT INTO products (name, cents) VALUES (?, ?) RETURNING id").bind("office chair").bind(18_900).fetch_one(&pool).await?;
    println!("inserted id {id}");

    let updated = sqlx::query("UPDATE products SET cents = cents * 90 / 100 WHERE cents > ?").bind(10_000).execute(&pool).await?;
    println!("discounted {} product(s)", updated.rows_affected());
    Ok(())
}

Output

inserted 1 row, id 1
inserted id 2
discounted 1 product(s)

3. Reading rows into structs

#[derive(FromRow)] maps columns to fields by name. query_as runs the query and builds the structs. Choose the fetch method by how many rows you expect: fetch_all (a Vec), fetch_one (exactly one, or an error), or fetch_optional (zero or one, as an Option).

Rust

use sqlx::FromRow;
use sqlx::sqlite::SqlitePoolOptions;

#[derive(Debug, FromRow)]
struct Product {
    id: i64,
    name: String,
    cents: i64,
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = SqlitePoolOptions::new().max_connections(1).connect("sqlite::memory:").await?;
    sqlx::raw_sql(
        "CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, cents INTEGER NOT NULL);
         INSERT INTO products (name, cents) VALUES ('desk lamp', 4990), ('office chair', 18900), ('notebook', 350);",
    )
    .execute(&pool)
    .await?;

    let cheap: Vec<Product> = sqlx::query_as("SELECT id, name, cents FROM products WHERE cents < ? ORDER BY cents").bind(5_000).fetch_all(&pool).await?;
    println!("{cheap:?}");

    let chair: Product = sqlx::query_as("SELECT id, name, cents FROM products WHERE name = ?").bind("office chair").fetch_one(&pool).await?;
    println!("{} costs {}", chair.name, chair.cents);

    let missing: Option<Product> = sqlx::query_as("SELECT id, name, cents FROM products WHERE id = ?").bind(99).fetch_optional(&pool).await?;
    println!("id 99: {missing:?}");

    let err = sqlx::query_as::<_, Product>("SELECT id, name, cents FROM products WHERE id = ?").bind(99).fetch_one(&pool).await.unwrap_err();
    println!("fetch_one with no rows: {err}");
    Ok(())
}

Output

[Product { id: 3, name: "notebook", cents: 350 }, Product { id: 1, name: "desk lamp", cents: 4990 }]
office chair costs 18900
id 99: None
fetch_one with no rows: no rows returned by a query that expected to return at least one row

4. Scalars, tuples, and aggregates

For a single value, query_scalar returns it directly. For a few columns that don't deserve a struct, query_as also builds tuples.

Rust

use sqlx::sqlite::SqlitePoolOptions;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = SqlitePoolOptions::new().max_connections(1).connect("sqlite::memory:").await?;
    sqlx::raw_sql(
        "CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, cents INTEGER NOT NULL);
         INSERT INTO orders (customer, cents) VALUES ('acme', 1200), ('globex', 800), ('acme', 4500), ('initech', 99), ('acme', 300);",
    )
    .execute(&pool)
    .await?;

    let count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM orders").fetch_one(&pool).await?;
    let total: i64 = sqlx::query_scalar("SELECT SUM(cents) FROM orders").fetch_one(&pool).await?;
    println!("{count} orders, {total} cents in total");

    let per_customer: Vec<(String, i64, i64)> =
        sqlx::query_as("SELECT customer, COUNT(*), SUM(cents) FROM orders GROUP BY customer ORDER BY SUM(cents) DESC").fetch_all(&pool).await?;
    for (customer, orders, cents) in per_customer {
        println!("{customer:<8} {orders} orders {cents:>6} cents");
    }
    Ok(())
}

Output

5 orders, 6899 cents in total
acme     3 orders   6000 cents
globex   1 orders    800 cents
initech  1 orders     99 cents

5. NULL, `Option`, and types

A nullable column maps to an Option, so the type says whether a value can be missing. Declare nullable columns that way: reading a NULL into a plain String fails with PostgreSQL and MySQL ("unexpected null; try decoding as an Option"), and the SQLite driver quietly returns an empty string or 0 instead, as the last query below shows. Common mappings: INTEGER ↔ i64 (and i32, bool), REAL ↔ f64, TEXT ↔ String, BLOB ↔ Vec<u8>; with the matching sqlx features, also chrono dates, uuid, and serde_json::Value.

Rust

use sqlx::FromRow;
use sqlx::sqlite::SqlitePoolOptions;

#[derive(Debug, FromRow)]
struct Customer {
    name: String,
    phone: Option<String>, // NULL -> None
    vip: bool,             // stored as 0 or 1
    discount: f64,
}

#[derive(Debug, FromRow)]
struct Strict {
    name: String,
    phone: String, // wrong: the column is nullable
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = SqlitePoolOptions::new().max_connections(1).connect("sqlite::memory:").await?;
    sqlx::raw_sql(
        "CREATE TABLE customers (name TEXT NOT NULL, phone TEXT, vip BOOLEAN NOT NULL, discount REAL NOT NULL);
         INSERT INTO customers VALUES ('acme', '+49 30 1234', 1, 0.1), ('globex', NULL, 0, 0.0);",
    )
    .execute(&pool)
    .await?;

    let all: Vec<Customer> = sqlx::query_as("SELECT * FROM customers").fetch_all(&pool).await?;
    println!("{all:#?}");

    sqlx::query("INSERT INTO customers VALUES (?, ?, ?, ?)").bind("initech").bind(None::<String>).bind(true).bind(0.25).execute(&pool).await?;

    // Don't do this: `phone` is nullable, but `Strict` says it never is.
    match sqlx::query_as::<_, Strict>("SELECT name, phone FROM customers").fetch_all(&pool).await {
        Ok(rows) => println!("NULL read into a String: {rows:?}"),
        Err(e) => println!("error: {e}"),
    }
    Ok(())
}

Output

[
    Customer {
        name: "acme",
        phone: Some(
            "+49 30 1234",
        ),
        vip: true,
        discount: 0.1,
    },
    Customer {
        name: "globex",
        phone: None,
        vip: false,
        discount: 0.0,
    },
]
NULL read into a String: [Strict { name: "acme", phone: "+49 30 1234" }, Strict { name: "globex", phone: "" }, Strict { name: "initech", phone: "" }]

Part 2: Doing it safely

6. SQL injection is a compile error

Building SQL with format! and user input is the classic SQL injection bug. Since version 0.9, sqlx refuses it at compile time: query only accepts string literals, so values have to go through .bind, where they're sent separately from the SQL and can never change its meaning.

Rust✗ does not compile · E0277

use sqlx::SqlitePool;

async fn find_user(pool: &SqlitePool, name: &str) -> Result<(), sqlx::Error> {
    let sql = format!("SELECT * FROM users WHERE name = '{name}'");
    sqlx::query(&sql).execute(pool).await?;
    Ok(())
}

fn main() {}

Output

error[E0277]: dynamic SQL strings should be audited for possible injections
   --> src/main.rs:5:17
    |
  5 |     sqlx::query(&sql).execute(pool).await?;
    |     ----------- ^^^^ dynamic SQL string
    |     |
    |     required by a bound introduced by this call
    |
    = help: the trait `SqlSafeStr` is not implemented for `&std::string::String`
    = note: prefer literal SQL strings with bind parameters or `QueryBuilder` to add dynamic data to a query.

With a bind parameter, even a hostile input is just a string to compare with:

Rust

use sqlx::sqlite::SqlitePoolOptions;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = SqlitePoolOptions::new().max_connections(1).connect("sqlite::memory:").await?;
    sqlx::raw_sql("CREATE TABLE users (name TEXT NOT NULL, admin BOOLEAN NOT NULL); INSERT INTO users VALUES ('ana', 1), ('ben', 0);").execute(&pool).await?;

    for input in ["ben", "x' OR '1'='1"] {
        let found: Vec<(String, bool)> = sqlx::query_as("SELECT name, admin FROM users WHERE name = ?").bind(input).fetch_all(&pool).await?;
        println!("{input:<14} -> {found:?}");
    }
    Ok(())
}

Output

ben            -> [("ben", false)]
x' OR '1'='1   -> []

When SQL genuinely has to be assembled at runtime from trusted parts, wrap it in sqlx::AssertSqlSafe(sql), which documents that someone checked it, or better, use QueryBuilder.

7. Dynamic queries with `QueryBuilder`

Search screens need queries whose shape depends on the input: optional filters, IN (...) lists of varying length, optional sorting. QueryBuilder appends SQL text and bind parameters separately, so the result is still a safe, parameterized query.

Rust

use sqlx::sqlite::SqlitePoolOptions;
use sqlx::{FromRow, QueryBuilder, Sqlite};

#[derive(Debug, FromRow)]
struct Product {
    name: String,
    category: String,
    cents: i64,
}

#[derive(Default)]
struct Search<'a> {
    categories: Vec<&'a str>,
    max_cents: Option<i64>,
    name_contains: Option<&'a str>,
}

fn build(search: &Search) -> QueryBuilder<Sqlite> {
    let mut q = QueryBuilder::new("SELECT name, category, cents FROM products WHERE 1 = 1");
    if !search.categories.is_empty() {
        q.push(" AND category IN (");
        let mut list = q.separated(", ");
        for c in &search.categories {
            list.push_bind(c.to_string());
        }
        list.push_unseparated(")");
    }
    if let Some(max) = search.max_cents {
        q.push(" AND cents <= ").push_bind(max);
    }
    if let Some(text) = search.name_contains {
        q.push(" AND name LIKE ").push_bind(format!("%{text}%"));
    }
    q.push(" ORDER BY cents");
    q
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = SqlitePoolOptions::new().max_connections(1).connect("sqlite::memory:").await?;
    sqlx::raw_sql(
        "CREATE TABLE products (name TEXT, category TEXT, cents INTEGER);
         INSERT INTO products VALUES ('desk lamp', 'office', 4990), ('office chair', 'office', 18900), ('rust book', 'books', 3900), ('sql book', 'books', 4200), ('mug', 'kitchen', 900);",
    )
    .execute(&pool)
    .await?;

    let searches = [
        Search { categories: vec!["books", "kitchen"], ..Default::default() },
        Search { max_cents: Some(5_000), name_contains: Some("book"), ..Default::default() },
        Search::default(),
    ];
    for search in &searches {
        let mut query = build(search);
        println!("{}", query.sql().as_str());
        let rows: Vec<Product> = query.build_query_as().fetch_all(&pool).await?;
        println!("  -> {:?}", rows.iter().map(|p| format!("{} ({}, {})", p.name, p.category, p.cents)).collect::<Vec<_>>());
    }
    Ok(())
}

Output

SELECT name, category, cents FROM products WHERE 1 = 1 AND category IN (?, ?) ORDER BY cents
  -> ["mug (kitchen, 900)", "rust book (books, 3900)", "sql book (books, 4200)"]
SELECT name, category, cents FROM products WHERE 1 = 1 AND cents <= ? AND name LIKE ? ORDER BY cents
  -> ["rust book (books, 3900)", "sql book (books, 4200)"]
SELECT name, category, cents FROM products WHERE 1 = 1 ORDER BY cents
  -> ["mug (kitchen, 900)", "rust book (books, 3900)", "sql book (books, 4200)", "desk lamp (office, 4990)", "office chair (office, 18900)"]

The printed SQL shows that the values never appear in the query text: each became a ? parameter.

8. Transactions

pool.begin() starts a transaction; queries run on it with &mut *tx, and tx.commit() makes them permanent. If the transaction is dropped without a commit, for example because ? returned early with an error, it's rolled back automatically, a guarantee enforced by ownership.

Rust

use sqlx::SqlitePool;
use sqlx::sqlite::SqlitePoolOptions;

async fn transfer(pool: &SqlitePool, from: &str, to: &str, cents: i64) -> Result<(), String> {
    let mut tx = pool.begin().await.map_err(|e| e.to_string())?;

    sqlx::query("UPDATE accounts SET cents = cents - ? WHERE owner = ?").bind(cents).bind(from).execute(&mut *tx).await.map_err(|e| e.to_string())?;
    let balance: i64 = sqlx::query_scalar("SELECT cents FROM accounts WHERE owner = ?").bind(from).fetch_one(&mut *tx).await.map_err(|e| e.to_string())?;
    if balance < 0 {
        return Err(format!("{from} can't cover {cents}")); // `tx` is dropped here: rolled back
    }
    sqlx::query("UPDATE accounts SET cents = cents + ? WHERE owner = ?").bind(cents).bind(to).execute(&mut *tx).await.map_err(|e| e.to_string())?;

    tx.commit().await.map_err(|e| e.to_string())
}

async fn balances(pool: &SqlitePool) -> Vec<(String, i64)> {
    sqlx::query_as("SELECT owner, cents FROM accounts ORDER BY owner").fetch_all(pool).await.unwrap()
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = SqlitePoolOptions::new().max_connections(1).connect("sqlite::memory:").await?;
    sqlx::raw_sql("CREATE TABLE accounts (owner TEXT PRIMARY KEY, cents INTEGER NOT NULL); INSERT INTO accounts VALUES ('ana', 10000), ('ben', 2000);").execute(&pool).await?;

    println!("{:?}", transfer(&pool, "ana", "ben", 3_000).await);
    println!("{:?}", balances(&pool).await);
    println!("{:?}", transfer(&pool, "ben", "ana", 9_000).await);
    println!("{:?}", balances(&pool).await); // unchanged: the failed transfer was rolled back
    Ok(())
}

Output

Ok(())
[("ana", 7000), ("ben", 5000)]
Err("ben can't cover 9000")
[("ana", 7000), ("ben", 5000)]

9. Constraint violations as domain errors

When the database rejects a write because of a unique or foreign key constraint, sqlx returns an error you can inspect. Translating it into a domain error, such as "email already registered", gives callers something meaningful to act on.

Rust

use sqlx::SqlitePool;
use sqlx::sqlite::SqlitePoolOptions;

#[derive(Debug)]
enum SignupError {
    EmailTaken(String),
    UnknownTeam(i64),
    Database(String),
}

async fn add_user(pool: &SqlitePool, email: &str, team_id: i64) -> Result<i64, SignupError> {
    let result = sqlx::query_scalar("INSERT INTO users (email, team_id) VALUES (?, ?) RETURNING id").bind(email).bind(team_id).fetch_one(pool).await;
    result.map_err(|e| match e.as_database_error() {
        Some(db) if db.is_unique_violation() => SignupError::EmailTaken(email.to_string()),
        Some(db) if db.is_foreign_key_violation() => SignupError::UnknownTeam(team_id),
        _ => SignupError::Database(e.to_string()),
    })
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = SqlitePoolOptions::new().max_connections(1).connect("sqlite::memory:").await?;
    sqlx::raw_sql(
        "CREATE TABLE teams (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
         CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, team_id INTEGER NOT NULL REFERENCES teams (id));
         INSERT INTO teams (name) VALUES ('payments');",
    )
    .execute(&pool)
    .await?;

    println!("{:?}", add_user(&pool, "ana@example.com", 1).await);
    println!("{:?}", add_user(&pool, "ana@example.com", 1).await);
    println!("{:?}", add_user(&pool, "ben@example.com", 42).await);
    Ok(())
}

Output

Ok(1)
Err(EmailTaken("ana@example.com"))
Err(UnknownTeam(42))

sqlx enables SQLite's foreign key checks by default; SQLite itself leaves them off unless asked.

Part 3: Running a real application

10. Migrations

Schema changes live in numbered SQL files, applied in order and recorded in a _sqlx_migrations table so each runs only once. The sqlx-cli tool creates the files (cargo install sqlx-cli, then sqlx migrate add create_users). This example writes two migration files and runs them twice; the second run does nothing.

Rust

use sqlx::migrate::Migrator;
use sqlx::sqlite::SqlitePoolOptions;
use std::fs;
use std::path::Path;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let dir = Path::new("migrations-demo");
    let _ = fs::remove_dir_all(dir);
    fs::create_dir_all(dir)?;
    fs::write(dir.join("20260901000000_create_users.sql"), "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);")?;
    fs::write(dir.join("20260915000000_add_email.sql"), "ALTER TABLE users ADD COLUMN email TEXT;")?;

    let pool = SqlitePoolOptions::new().max_connections(1).connect("sqlite::memory:").await?;
    let migrator = Migrator::new(dir).await?;
    migrator.run(&pool).await?;
    migrator.run(&pool).await?; // already applied: nothing to do

    let applied: Vec<(i64, String)> = sqlx::query_as("SELECT version, description FROM _sqlx_migrations ORDER BY version").fetch_all(&pool).await?;
    println!("{applied:?}");
    sqlx::query("INSERT INTO users (name, email) VALUES (?, ?)").bind("ana").bind("ana@example.com").execute(&pool).await?;
    println!("users table has the email column");
    Ok(())
}

Output

[(20260901000000, "create users"), (20260915000000, "add email")]
users table has the email column

In an application, embed the migrations in the binary at compile time and run them at startup:

Rust

// Reads ./migrations at compile time; the files ship inside the executable.
sqlx::migrate!("./migrations").run(&pool).await?;

11. Streaming large results

fetch_all loads every row into a Vec. For exports and batch jobs over many rows, fetch returns a stream that yields rows one at a time as the database sends them.

Rust

use futures::TryStreamExt;
use sqlx::sqlite::SqlitePoolOptions;
use sqlx::FromRow;

#[derive(FromRow)]
struct Event {
    id: i64,
    kind: String,
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = SqlitePoolOptions::new().max_connections(1).connect("sqlite::memory:").await?;
    sqlx::raw_sql(
        "CREATE TABLE events (id INTEGER PRIMARY KEY, kind TEXT NOT NULL);
         WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 50000)
         INSERT INTO events (kind) SELECT CASE WHEN i % 10 = 0 THEN 'error' ELSE 'info' END FROM n;",
    )
    .execute(&pool)
    .await?;

    let mut rows = sqlx::query_as::<_, Event>("SELECT id, kind FROM events ORDER BY id").fetch(&pool);
    let (mut seen, mut errors, mut last_id) = (0, 0, 0);
    while let Some(event) = rows.try_next().await? {
        seen += 1;
        last_id = event.id;
        if event.kind == "error" {
            errors += 1;
        }
    }
    println!("streamed {seen} rows (last id {last_id}), {errors} errors");
    Ok(())
}

Output

streamed 50000 rows (last id 50000), 5000 errors

12. A repository type

Keeping all the SQL for one table in one type, a repository, gives the rest of the code a typed API and puts queries where they're easy to review. With thiserror, the repository's error type converts sqlx::Error automatically.

Rust

use sqlx::sqlite::SqlitePoolOptions;
use sqlx::{FromRow, SqlitePool};

#[derive(Debug, Clone, FromRow)]
pub struct Customer {
    pub id: i64,
    pub name: String,
    pub email: String,
    pub credit_cents: i64,
}

#[derive(Debug, thiserror::Error)]
pub enum RepoError {
    #[error("customer {0} not found")]
    NotFound(i64),
    #[error("email {0} is already in use")]
    DuplicateEmail(String),
    #[error(transparent)]
    Database(#[from] sqlx::Error),
}

#[derive(Clone)]
pub struct CustomerRepo {
    pool: SqlitePool,
}

impl CustomerRepo {
    pub fn new(pool: SqlitePool) -> Self {
        CustomerRepo { pool }
    }

    pub async fn create(&self, name: &str, email: &str) -> Result<Customer, RepoError> {
        sqlx::query_as("INSERT INTO customers (name, email) VALUES (?, ?) RETURNING id, name, email, credit_cents")
            .bind(name)
            .bind(email)
            .fetch_one(&self.pool)
            .await
            .map_err(|e| match e.as_database_error() {
                Some(db) if db.is_unique_violation() => RepoError::DuplicateEmail(email.to_string()),
                _ => RepoError::Database(e),
            })
    }

    pub async fn get(&self, id: i64) -> Result<Customer, RepoError> {
        sqlx::query_as("SELECT id, name, email, credit_cents FROM customers WHERE id = ?")
            .bind(id)
            .fetch_optional(&self.pool)
            .await?
            .ok_or(RepoError::NotFound(id))
    }

    pub async fn add_credit(&self, id: i64, cents: i64) -> Result<Customer, RepoError> {
        let result = sqlx::query("UPDATE customers SET credit_cents = credit_cents + ? WHERE id = ?").bind(cents).bind(id).execute(&self.pool).await?;
        if result.rows_affected() == 0 {
            return Err(RepoError::NotFound(id));
        }
        self.get(id).await
    }

    pub async fn top_by_credit(&self, limit: i64) -> Result<Vec<Customer>, RepoError> {
        Ok(sqlx::query_as("SELECT id, name, email, credit_cents FROM customers ORDER BY credit_cents DESC, id LIMIT ?").bind(limit).fetch_all(&self.pool).await?)
    }
}

#[tokio::main]
async fn main() -> Result<(), RepoError> {
    let pool = SqlitePoolOptions::new().max_connections(1).connect("sqlite::memory:").await?;
    sqlx::raw_sql("CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, credit_cents INTEGER NOT NULL DEFAULT 0)").execute(&pool).await?;
    let repo = CustomerRepo::new(pool);

    let acme = repo.create("Acme", "ap@acme.test").await?;
    let globex = repo.create("Globex", "billing@globex.test").await?;
    repo.add_credit(globex.id, 2_500).await?;
    repo.add_credit(acme.id, 700).await?;

    println!("{:?}", repo.top_by_credit(5).await?.iter().map(|c| (c.name.as_str(), c.credit_cents)).collect::<Vec<_>>());
    println!("{}", repo.create("Acme 2", "ap@acme.test").await.unwrap_err());
    println!("{}", repo.add_credit(99, 100).await.unwrap_err());
    Ok(())
}

Output

[("Globex", 2500), ("Acme", 700)]
email ap@acme.test is already in use
customer 99 not found

13. Queries checked at compile time

sqlx's best-known feature is the query! and query_as! macros: at compile time, they connect to a development database (from the DATABASE_URL environment variable), check the SQL, and verify that the parameter and column types match your Rust types. A typo in a column name becomes a compile error.

Rust

// DATABASE_URL=sqlite://dev.db must point to a database with the schema.
let product = sqlx::query_as!(Product, "SELECT id, name, cents FROM products WHERE id = ?", id)
    .fetch_one(&pool)
    .await?;

To build without a database, for example in CI, run cargo sqlx prepare, which saves the query metadata to a .sqlx directory that the macros read instead. This page uses the plain functions, which check nothing at compile time but need no setup.

14. Realistic example: a REST API backed by SQLite

The notes API from Web Services with axum, now stored in a database. The pool is the router's state, each handler runs its query, database errors map to HTTP responses, and the tests run the whole stack, HTTP to SQL, in-process.

Rustcargo test

use axum::extract::{Path, State};
use axum::http::StatusCode;
use axum::response::{IntoResponse, Response};
use axum::routing::get;
use axum::{Json, Router};
use serde::{Deserialize, Serialize};
use serde_json::json;
use sqlx::sqlite::SqlitePoolOptions;
use sqlx::{FromRow, SqlitePool};

#[derive(Serialize, FromRow)]
pub struct Note {
    id: i64,
    title: String,
    body: String,
}

#[derive(Deserialize)]
pub struct NoteInput {
    title: String,
    #[serde(default)]
    body: String,
}

pub enum ApiError {
    NotFound(i64),
    Invalid(&'static str),
    Database(sqlx::Error),
}

impl From<sqlx::Error> for ApiError {
    fn from(e: sqlx::Error) -> Self {
        ApiError::Database(e)
    }
}

impl IntoResponse for ApiError {
    fn into_response(self) -> Response {
        let (status, message) = match self {
            ApiError::NotFound(id) => (StatusCode::NOT_FOUND, format!("note {id} not found")),
            ApiError::Invalid(why) => (StatusCode::UNPROCESSABLE_ENTITY, why.to_string()),
            ApiError::Database(e) => {
                eprintln!("database error: {e}");
                (StatusCode::INTERNAL_SERVER_ERROR, String::from("internal error"))
            }
        };
        (status, Json(json!({ "error": message }))).into_response()
    }
}

async fn list(State(db): State<SqlitePool>) -> Result<Json<Vec<Note>>, ApiError> {
    Ok(Json(sqlx::query_as("SELECT id, title, body FROM notes ORDER BY id").fetch_all(&db).await?))
}

async fn create(State(db): State<SqlitePool>, Json(input): Json<NoteInput>) -> Result<(StatusCode, Json<Note>), ApiError> {
    if input.title.trim().is_empty() {
        return Err(ApiError::Invalid("title must not be empty"));
    }
    let note = sqlx::query_as("INSERT INTO notes (title, body) VALUES (?, ?) RETURNING id, title, body").bind(input.title).bind(input.body).fetch_one(&db).await?;
    Ok((StatusCode::CREATED, Json(note)))
}

async fn read(State(db): State<SqlitePool>, Path(id): Path<i64>) -> Result<Json<Note>, ApiError> {
    let note = sqlx::query_as("SELECT id, title, body FROM notes WHERE id = ?").bind(id).fetch_optional(&db).await?;
    note.map(Json).ok_or(ApiError::NotFound(id))
}

async fn delete(State(db): State<SqlitePool>, Path(id): Path<i64>) -> Result<StatusCode, ApiError> {
    let result = sqlx::query("DELETE FROM notes WHERE id = ?").bind(id).execute(&db).await?;
    if result.rows_affected() == 0 { Err(ApiError::NotFound(id)) } else { Ok(StatusCode::NO_CONTENT) }
}

pub async fn app(database_url: &str) -> Result<Router, sqlx::Error> {
    let db = SqlitePoolOptions::new().max_connections(1).connect(database_url).await?;
    sqlx::raw_sql("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, title TEXT NOT NULL, body TEXT NOT NULL)").execute(&db).await?;
    Ok(Router::new().route("/notes", get(list).post(create)).route("/notes/{id}", get(read).delete(delete)).with_state(db))
}

#[cfg(test)]
mod tests {
    use super::*;
    use axum::body::Body;
    use axum::http::Request;
    use serde_json::Value;
    use tower::ServiceExt;

    async fn call(app: &Router, method: &str, uri: &str, body: Value) -> (StatusCode, Value) {
        let request = Request::builder().method(method).uri(uri).header("content-type", "application/json").body(Body::from(body.to_string())).unwrap();
        let response = app.clone().oneshot(request).await.unwrap();
        let status = response.status();
        let bytes = axum::body::to_bytes(response.into_body(), usize::MAX).await.unwrap();
        (status, serde_json::from_slice(&bytes).unwrap_or(Value::Null))
    }

    #[tokio::test]
    async fn notes_round_trip_through_the_database() {
        let app = app("sqlite::memory:").await.unwrap();
        let (status, created) = call(&app, "POST", "/notes", json!({ "title": "groceries", "body": "milk, eggs" })).await;
        assert_eq!(status, StatusCode::CREATED);
        assert_eq!(created["id"], 1);

        let (status, read) = call(&app, "GET", "/notes/1", Value::Null).await;
        assert_eq!((status, read["body"].as_str()), (StatusCode::OK, Some("milk, eggs")));

        call(&app, "POST", "/notes", json!({ "title": "second" })).await;
        let (_, list) = call(&app, "GET", "/notes", Value::Null).await;
        assert_eq!(list.as_array().unwrap().len(), 2);
    }

    #[tokio::test]
    async fn invalid_and_missing_notes() {
        let app = app("sqlite::memory:").await.unwrap();
        let (status, body) = call(&app, "POST", "/notes", json!({ "title": " " })).await;
        assert_eq!((status, body["error"].as_str()), (StatusCode::UNPROCESSABLE_ENTITY, Some("title must not be empty")));

        let (status, _) = call(&app, "DELETE", "/notes/5", Value::Null).await;
        assert_eq!(status, StatusCode::NOT_FOUND);
    }
}

Output

running 2 tests
test tests::invalid_and_missing_notes ... ok
test tests::notes_round_trip_through_the_database ... ok

test result: ok. 2 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.00s

For production, main would call app("sqlite://notes.db?mode=rwc") (or a PostgreSQL URL from the environment), run the migrations, and serve the router as in the axum lesson. The same tests keep working against an in-memory database.

Summary

Create one pool at startup (SqlitePoolOptions, PgPoolOptions) and share it; in-memory SQLite needs a single connection.
sqlx::query + .bind + execute/fetch_* run statements; query_as with #[derive(FromRow)] maps rows to structs; query_scalar returns single values.
Choose fetch_one, fetch_optional, fetch_all, or the streaming fetch by how many rows you expect.
Nullable columns are Options. Values always travel as bind parameters; sqlx 0.9 rejects SQL built with format! at compile time, and QueryBuilder builds dynamic queries safely.
Transactions commit explicitly and roll back when dropped. Constraint violations can be recognized with is_unique_violation() and is_foreign_key_violation().
Migrations are SQL files applied in order; query! macros check SQL against a real database at compile time.

This completes the Essential Crates course. Next: the Practical Examples course puts these crates to work, starting with the Access Log Analyzer.

Part 1: Queries

1. Connecting

2. Inserting with bind parameters

3. Reading rows into structs

4. Scalars, tuples, and aggregates

5. NULL, Option, and types

Part 2: Doing it safely

6. SQL injection is a compile error

7. Dynamic queries with QueryBuilder

8. Transactions

9. Constraint violations as domain errors

Part 3: Running a real application

10. Migrations

11. Streaming large results

12. A repository type

13. Queries checked at compile time

14. Realistic example: a REST API backed by SQLite

Summary

5. NULL, `Option`, and types

7. Dynamic queries with `QueryBuilder`