Step 5 — Talking to Postgres with SQLx

Our handlers return fake data. Let's connect a real PostgreSQL database using SQLx — an async, pure-Rust toolkit whose headline feature is compile-time-checked SQL.

Set up a database

Get a Postgres running (Docker is easiest):

docker run --name tasks-db -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=tasks -p 5432:5432 -d postgres:16

Set the connection string SQLx reads:

export DATABASE_URL="postgres://postgres:secret@localhost:5432/tasks"

Create the table (via psql, a migration, or the SQLx CLI):

CREATE TABLE tasks (
    id         BIGSERIAL PRIMARY KEY,
    title      TEXT      NOT NULL,
    done       BOOLEAN   NOT NULL DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

The connection pool

Open one pool at startup and share it via State (Step 4). A pool holds a set of live connections and hands them out per query — opening a fresh connection per request would be far too slow.

use sqlx::postgres::PgPoolOptions;

#[tokio::main]
async fn main() {
    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect(&std::env::var("DATABASE_URL").unwrap())
        .await
        .expect("failed to connect to Postgres");

    let app = Router::new()
        .route("/tasks", get(list_tasks).post(create_task))
        .route("/tasks/{id}", get(get_task))
        .with_state(pool);
    // ... serve as before
}

Mapping rows to structs

Add sqlx::FromRow (and chrono for the timestamp) so a database row can become a Task:

use serde::Serialize;
use chrono::{DateTime, Utc};

#[derive(Serialize, sqlx::FromRow)]
struct Task {
    id: i64,
    title: String,
    done: bool,
    created_at: DateTime<Utc>,
}

Type mapping matters. SQLx maps Rust types to Postgres types exactly: i64 ↔ bigint, DateTime<Utc> ↔ timestamptz, String ↔ text. A mismatch is a compile error with the macro form below — which is the whole point.

Querying

List and fetch

use axum::{extract::{State, Path}, Json};
use sqlx::PgPool;

async fn list_tasks(State(pool): State<PgPool>) -> Json<Vec<Task>> {
    let tasks = sqlx::query_as::<_, Task>("SELECT id, title, done, created_at FROM tasks ORDER BY id")
        .fetch_all(&pool)
        .await
        .unwrap();          // we'll replace unwrap with real error handling in Step 6
    Json(tasks)
}

async fn get_task(State(pool): State<PgPool>, Path(id): Path<i64>) -> Json<Task> {
    let task = sqlx::query_as::<_, Task>("SELECT id, title, done, created_at FROM tasks WHERE id = $1")
        .bind(id)           // $1 is bound safely — never string-concatenated
        .fetch_one(&pool)
        .await
        .unwrap();
    Json(task)
}

Insert

async fn create_task(
    State(pool): State<PgPool>,
    Json(body): Json<NewTask>,
) -> Json<Task> {
    let task = sqlx::query_as::<_, Task>(
        "INSERT INTO tasks (title) VALUES ($1) RETURNING id, title, done, created_at",
    )
    .bind(&body.title)
    .fetch_one(&pool)
    .await
    .unwrap();
    Json(task)
}

The $1, $2 placeholders with .bind(...) are parameterized queries — the values never get spliced into the SQL string, so SQL injection is structurally impossible. Never build a query with format!().

The superpower: compile-time-checked queries

Everything above used query_as::<_, Task>("...") — the string is validated at runtime. SQLx also offers macro forms that validate against your real database schema at compile time:

// query_as! (with the bang) checks the SQL against DATABASE_URL AT BUILD TIME:
//  - table/column names must exist
//  - result types must match the struct
//  - bind parameter types are verified
let task = sqlx::query_as!(
    Task,
    "SELECT id, title, done, created_at FROM tasks WHERE id = $1",
    id
)
.fetch_one(&pool)
.await?;

If you typo titel, the project doesn't compile. A schema drift becomes a build error, not a production 500.

Offline mode (how CI builds without a database)

The macros normally need a live database at compile time. That's impractical in CI and Docker builds, so SQLx supports offline mode: you run

cargo sqlx prepare

once locally, which writes a .sqlx/ directory of cached query metadata (JSON). Commit it. CI then builds with SQLX_OFFLINE=true, reading the cache instead of connecting.

Treat .sqlx/ as source code, not a build artifact. iKanban's guidelines are emphatic about this: deleting the cache breaks every Docker build immediately. When you add or change a query!/query_as! macro, regenerate the cache and commit the new files. When you don't use the macro (the plain query_as::<_, Task> string form), there's no cache to maintain — at the cost of losing compile-time checking.

Where we are

Your API now reads and writes real rows, safely. But every query ends in .unwrap(), which crashes the handler on any error. Next: Step 6 — Errors, Auth & JSON Contracts, where we make failures return proper HTTP responses and lock the API down.