Database Access with sqlx

Reviewed & published by Brayan K

sqlx is an async, pure-Rust SQL toolkit whose standout feature is compile-time checked queries — your SQL is verified against a real database before your program ever runs.

Part of the free Rust course at LearnCodingFast — hands-on lessons with worked examples and the output they print, plus practice exercises and a quick quiz.

In this lesson you'll create a connection pool, run checked queries, map rows into structs with FromRow, group work in transactions, and apply migrations.

What You'll Learn in This Lesson

• Creating a PgPool connection pool
• Compile-time checked query! and query_as!
• Bind parameters that prevent SQL injection
• Mapping rows to structs with FromRow
• Transactions with begin / commit
• Applying migrations with sqlx::migrate!

1️⃣ A Pool and a Checked Query

Everything starts with a PgPool — a set of reusable connections shared across your app. The sqlx::query! macro verifies your SQL against the real database at compile time, and $1 bind parameters keep user input out of the SQL text.

// sqlx is an async, pure-Rust SQL toolkit with COMPILE-TIME checked queries.
// A PgPool manages a set of reusable Postgres connections.
use sqlx::postgres::PgPoolOptions;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    // Create a connection pool. Handlers share the pool; each query borrows
    // a connection from it and returns it automatically when done.
    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect("postgres://user:pass@localhost/app")
        .await?;

    // sqlx::query! checks this SQL against the real database AT COMPILE TIME.
    // $1 is a bind parameter (never string concatenation), so it is injection-safe.
    let row = sqlx::query!("SELECT $1::int AS answer", 42_i32)
        .fetch_one(&pool)
        .await?;

    println!("answer = {}", row.answer.unwrap());
    Ok(())
}

Because values travel as bind parameters, not string concatenation, sqlx is injection-safe by construction — there is no string to escape.

2️⃣ Mapping Rows to Structs

Derive FromRow on a struct and query_as! will produce rows as that type — checked at compile time. fetch_one returns exactly one row, fetch_all a Vec, and fetch_optional an Option.

// Map rows into a typed struct. Derive FromRow so sqlx knows the columns.
use sqlx::{FromRow, PgPool};

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

async fn find_user(pool: &PgPool, id: i32) -> Result<User, sqlx::Error> {
    // query_as! verifies the SQL and produces rows as User at compile time.
    let user = sqlx::query_as!(
        User,
        "SELECT id, name FROM users WHERE id = $1",
        id
    )
    .fetch_one(pool)   // exactly one row, or an error
    .await?;

    Ok(user)
}

async fn all_users(pool: &PgPool) -> Result<Vec<User>, sqlx::Error> {
    let users = sqlx::query_as!(User, "SELECT id, name FROM users ORDER BY id")
        .fetch_all(pool) // zero or more rows as a Vec
        .await?;
    Ok(users)
}

Rename a column without updating User and you get a clear compile error — the mapping is type-checked, not hopeful.

3️⃣ Transactions and Migrations

A transaction makes several statements atomic. pool.begin() starts one; commit() makes it permanent, and dropping it without committing rolls back. Migrations are versioned SQL files applied with sqlx::migrate!.

// Transactions group statements so they all succeed or all roll back.
// Migrations are versioned .sql files applied with sqlx::migrate!.
use sqlx::PgPool;

async fn transfer(pool: &PgPool, from: i32, to: i32, cents: i64) -> Result<(), sqlx::Error> {
    // begin() starts a transaction. Nothing is permanent until commit().
    let mut tx = pool.begin().await?;

    sqlx::query!("UPDATE accounts SET balance = balance - $1 WHERE id = $2", cents, from)
        .execute(&mut *tx)
        .await?;

    sqlx::query!("UPDATE accounts SET balance = balance + $1 WHERE id = $2", cents, to)
        .execute(&mut *tx)
        .await?;

    // If we got here, make both updates permanent atomically.
    tx.commit().await?; // dropping tx without commit rolls back instead
    Ok(())
}

async fn setup(pool: &PgPool) -> Result<(), sqlx::Error> {
    // Embeds ./migrations and applies any that have not run yet.
    sqlx::migrate!("./migrations").run(pool).await?;
    Ok(())
}

If either UPDATE fails, the whole transfer is undone — you never lose money to a half-applied transfer.

Mini-Challenge: Insert and Return

Insert a row with a bind parameter and read it straight back into a struct using RETURNING and query_as!.

// 🎯 MINI-CHALLENGE: insert a row and read it back.
//
// 1. Define struct Todo { id: i32, title: String } and derive FromRow.
// 2. Write async fn add(pool: &PgPool, title: &str) -> Result<Todo, sqlx::Error>
//    that INSERTs (use $1 for the title) and RETURNING id, title, then maps
//    the result with query_as! into a Todo and returns it.
// 3. fetch_one the inserted row.
//
// Expected (for title "ship it"):  Todo { id: 1, title: "ship it" }

use sqlx::{FromRow, PgPool};

// your code here

Common Errors (and the fix)

• "set DATABASE_URL to use query macros" — The query! macros need a database at build time, or run cargo sqlx prepare for offline mode.
• "no column found for name" — Your struct fields must match the selected column names and types.
• fetch_one on zero rows — fetch_one errors if no row; use fetch_optional for zero-or-one.
• Forgetting to commit — A transaction dropped without commit() rolls back; call it explicitly.

Pro Tips

• 💡 Build one pool at startup: store it in your Axum State and clone it per request.
• 💡 Use cargo sqlx prepare: cache query metadata so CI builds without a live database.
• 💡 Always use bind parameters: $1, never string formatting, keeps you injection-safe.

📋 Quick Reference — sqlx

🎉 Lesson Complete!

• ✅ A PgPool shares reusable connections across your app
• ✅ query! and query_as! check SQL at compile time
• ✅ FromRow maps rows into typed structs
• ✅ Transactions and sqlx::migrate! keep your data consistent

Practice quiz

Continue this course

• Previous: Web Framework with Axum
• Next: FFI & C Interop — Call C from Rust and Rust from C with extern, repr(C), and the FFI types
• Quick reference: Rust cheat sheet

Frequently asked questions

What does compile-time query checking actually do?

When you use the sqlx::query! or query_as! macros, sqlx connects to a development database (using the DATABASE_URL environment variable) at compile time and asks it to prepare your SQL. That tells sqlx whether the statement is valid, what columns it returns, and what types they are. The macro then generates Rust code with those exact types, so a typo in a column name or a type mismatch becomes a compile error rather than a runtime panic. For CI or offline builds you can run cargo sqlx prepare to cache the query metadata in a .sqlx folder so no live database is needed at build time.

Why use a connection pool instead of one connection?

Opening a database connection is relatively expensive, and a single connection cannot serve concurrent requests. A PgPool keeps a set of open connections ready to reuse; each query borrows one from the pool and returns it automatically when it finishes. This caps how many connections you hold open (set with max_connections), provides backpressure when the database is busy, and is safe to clone and share across async tasks. In a web app you typically build one pool at startup, store it in your application state, and pass a reference to every handler that needs the database.

How does sqlx map rows to my structs?

Derive sqlx::FromRow on a struct whose field names and types match the selected columns, and sqlx can build instances of it from query results. The query_as! macro ties a query directly to such a struct and checks at compile time that the columns line up. Methods like fetch_one return a single value of your type, fetch_all returns a Vec, and fetch_optional returns an Option for the zero-or-one case. Because the mapping is type-checked, renaming a column without updating the struct produces a clear compile error.

How do transactions and migrations work?

A transaction groups several statements so they commit together or not at all. Call pool.begin().await to get a transaction handle, run your queries against it with .execute(&mut *tx), then tx.commit().await to make them permanent; if the handle is dropped without committing, sqlx rolls everything back. Migrations are versioned .sql files in a migrations directory. The sqlx::migrate!("./migrations") macro embeds them into your binary, and calling .run(&pool) applies any that have not yet been recorded in the schema-version table, so each migration runs exactly once.