Lesson 2 of 2Intermediate⏱ 15 mins

Async Database Queries with SQLx

Executing SQL queries asynchronously.

50% of path

Learning Objectives

Execute a SELECT query

##Talking to PostgreSQL with SQLx

SQLx is an async SQL toolkit for Rust. You write plain SQL, and SQLx maps the results to your structs. It is not an ORM, which means no hidden queries and full control.

#Setup

code_blockstoml

[dependencies]
sqlx = { version = "0.8", features = ["runtime-tokio", "postgres", "macros"] }
serde = { version = "1", features = ["derive"] }

Install the CLI to manage migrations:

terminalbash

cargo install sqlx-cli --no-default-features --features postgres

#Migrations

A migration is a versioned SQL file that changes the schema. Create one:

terminalbash

sqlx migrate add create_users

Edit the generated file:

code_blockssql

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

Apply all pending migrations with sqlx migrate run, or run them at startup with sqlx::migrate!().run(&pool).await?.

#Mapping rows to structs

Derive FromRow and use query_as:

code_blocksrust

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

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

#Reading data

code_blocksrust

use actix_web::{get, web, HttpResponse};
use sqlx::PgPool;

#[get("/users")]
async fn list_users(pool: web::Data<PgPool>) -> HttpResponse {
    match sqlx::query_as::<_, User>("SELECT id, name, email FROM users ORDER BY id")
        .fetch_all(pool.get_ref())
        .await
    {
        Ok(users) => HttpResponse::Ok().json(users),
        Err(_) => HttpResponse::InternalServerError().finish(),
    }
}

Use fetch_all for a list, fetch_one when exactly one row must exist, and fetch_optional when a row may be missing (a perfect fit for a 404).

#Writing data safely

Always pass values as bind parameters ($1, $2). Never build SQL by joining strings, because that allows SQL injection.

code_blocksrust

use serde::Deserialize;

#[derive(Deserialize)]
pub struct NewUser {
    pub name: String,
    pub email: String,
}

#[actix_web::post("/users")]
async fn create_user(
    pool: web::Data<PgPool>,
    body: web::Json<NewUser>,
) -> HttpResponse {
    let result = sqlx::query_as::<_, User>(
        "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, name, email",
    )
    .bind(&body.name)
    .bind(&body.email)
    .fetch_one(pool.get_ref())
    .await;

    match result {
        Ok(user) => HttpResponse::Created().json(user),
        Err(_) => HttpResponse::InternalServerError().finish(),
    }
}

RETURNING gives you the inserted row without a second query.

#Transactions

Group several statements so they succeed or fail together:

code_blocksrust

let mut tx = pool.begin().await?;
sqlx::query("UPDATE accounts SET balance = balance - $1 WHERE id = $2")
    .bind(100).bind(1).execute(&mut *tx).await?;
sqlx::query("UPDATE accounts SET balance = balance + $1 WHERE id = $2")
    .bind(100).bind(2).execute(&mut *tx).await?;
tx.commit().await?;

If an error occurs before commit, dropping tx rolls everything back.

"What If?" Experiment

Learning by doing. Modify the code to see what happens.

Goal

Observe rollback in a transaction.

Starting Code

let mut tx = pool.begin().await?;

Suggested Modification

Return an error before tx.commit() and then query the table.

Expected Result

None of the statements in the transaction were applied.

Explanation: Dropping the transaction without commit rolls it back, which keeps the data consistent.

Common Mistakes

Anticipate and avoid these frequent pitfalls when working with this concept.

Building SQL with format!

String formatting feels convenient but lets users inject SQL.

Incorrect Pattern

sqlx::query(&format!("SELECT * FROM users WHERE name = '{}'", name))

Correct Pattern

sqlx::query("SELECT * FROM users WHERE name = $1").bind(&name)

Explanation: Bind parameters are sent separately from the SQL, so user input can never change the query's meaning.

Using fetch_one when the row may not exist

fetch_one returns an error if there are no rows.

Incorrect Pattern

.fetch_one(pool.get_ref()).await? // returns an error for a missing user

Correct Pattern

.fetch_optional(pool.get_ref()).await? // Option<User>, map None to 404

Explanation: fetch_optional returns None for a missing row, which you can turn into a clean 404 response.

Practical Exercise

Fetch one user by id

Task

Write GET /users/{id} that returns the user as JSON or 404 when it does not exist.

Requirements

Use query_as with a $1 bind parameter
Use fetch_optional
Return 404 for None

Need a hint?

Try solving the exercise in your local environment first. Once you have a solution, or if you get completely stuck, reveal the answer.

Real-World Application

Real services use SQLx migrations in CI and deployment, bind parameters everywhere and transactions for anything that must be atomic, such as payments or inventory updates.

Key Takeaways

Write SQL and bind values with $1, $2; never concatenate user input into SQL.
Derive FromRow and use query_as to map rows to structs.
Choose fetch_all, fetch_one or fetch_optional based on the expected rows.
Use transactions so related changes succeed or fail together.

Finished reading?

Mark this lesson as done to track your progress and earn XP.

Previous Lesson

1. Connection Pools and State

Path Completed!

Back to Learning Paths