Skip to content

Lesson 26 ยท Databases with SQLx

CRUD Endpoints with SQLx

Build create, read, update and delete endpoints in Axum with SQLx: RETURNING clauses, status codes, partial updates, pagination and a runnable repository stand-in showing the handler logic.

CRUD (create, read, update, delete) is the bread and butter of a web API. With Axum and SQLx each operation is a small handler: extract the input, run one SQL statement, and turn the result into a status code and JSON body. This lesson builds all four for a todos table, plus list pagination, and shows the status-code conventions that keep your API predictable.

The real handlers need Axum, SQLx and PostgreSQL, so they are read-only. The runnable stand-in implements the same decisions (which statuses, when None becomes 404) against an in-memory repository so you can try the logic.

The model and the types

Rust
// Real code: read-only.
use axum::{extract::{Path, Query, State}, http::StatusCode, routing::get, Json, Router};
use serde::{Deserialize, Serialize};
use sqlx::{FromRow, PgPool};

#[derive(Serialize, FromRow)]
struct Todo {
    id: i64,
    title: String,
    done: bool,
}

#[derive(Deserialize)]
struct NewTodo {
    title: String,
}

#[derive(Deserialize)]
struct UpdateTodo {
    title: Option<String>,
    done: Option<bool>,
}

#[derive(Deserialize)]
struct Page {
    limit: Option<i64>,
    offset: Option<i64>,
}

Separate input types from the database type. NewTodo has no id because the database assigns it; UpdateTodo uses Option fields so clients can send only what changes. This also stops clients from setting fields they should not control.

The four handlers

Rust
async fn create(State(db): State<PgPool>, Json(input): Json<NewTodo>)
    -> Result<(StatusCode, Json<Todo>), AppError>
{
    let todo = sqlx::query_as::<_, Todo>(
        "INSERT INTO todos (title) VALUES ($1) RETURNING id, title, done",
    )
    .bind(input.title)
    .fetch_one(&db)
    .await?;
    Ok((StatusCode::CREATED, Json(todo)))
}

async fn get_one(State(db): State<PgPool>, Path(id): Path<i64>)
    -> Result<Json<Todo>, AppError>
{
    sqlx::query_as::<_, Todo>("SELECT id, title, done FROM todos WHERE id = $1")
        .bind(id)
        .fetch_optional(&db)
        .await?
        .map(Json)
        .ok_or(AppError::NotFound)
}

async fn update(State(db): State<PgPool>, Path(id): Path<i64>, Json(input): Json<UpdateTodo>)
    -> Result<Json<Todo>, AppError>
{
    sqlx::query_as::<_, Todo>(
        "UPDATE todos
            SET title = COALESCE($2, title),
                done  = COALESCE($3, done)
          WHERE id = $1
      RETURNING id, title, done",
    )
    .bind(id)
    .bind(input.title)
    .bind(input.done)
    .fetch_optional(&db)
    .await?
    .map(Json)
    .ok_or(AppError::NotFound)
}

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

fn router(db: PgPool) -> Router {
    Router::new()
        .route("/todos", get(list).post(create))
        .route("/todos/{id}", get(get_one).patch(update).delete(remove))
        .with_state(db)
}

Details worth noticing:

  • RETURNING gives back the stored row (with the generated id) in the same round trip as the INSERT or UPDATE, so you never need a second SELECT.
  • COALESCE($2, title) keeps the old value when the client sent null or omitted the field, making a partial update (PATCH) in one statement.
  • rows_affected() tells you whether a DELETE matched anything.
  • Path syntax is /{id} in Axum 0.8 (older versions used /:id).
  • The ? operator works because AppError implements From<sqlx::Error>; see the error lesson.

Status codes to use

Operation Success Missing row Bad input
Create 201 Created (+ body, optionally Location) n/a 422 or 400
Read one 200 404 400 (bad id)
Update 200 with new row 404 422
Delete 204 No Content 404 n/a

Pagination

Never return an unbounded list. Offset pagination is simplest; clamp the limit so one client cannot request a million rows.

Rust
async fn list(State(db): State<PgPool>, Query(page): Query<Page>)
    -> Result<Json<Vec<Todo>>, AppError>
{
    let limit = page.limit.unwrap_or(20).clamp(1, 100);
    let offset = page.offset.unwrap_or(0).max(0);
    let todos = sqlx::query_as::<_, Todo>(
        "SELECT id, title, done FROM todos ORDER BY id LIMIT $1 OFFSET $2",
    )
    .bind(limit)
    .bind(offset)
    .fetch_all(&db)
    .await?;
    Ok(Json(todos))
}

Large offsets get slow because the database still walks the skipped rows. For big tables use keyset pagination: WHERE id > $last_id ORDER BY id LIMIT $n, where the client sends back the last id it saw.

Stand-in: the same logic in memory

Rust CRUD decisions against an in-memory repository (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.

A second pass over the pagination rule: clamping, and the difference between offset and keyset paging, is easy to see with numbers.

Rust Offset vs keyset paging (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.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does RETURNING id, title, done give you?

    RETURNING sends back the affected row, including generated values like id, so you skip a second SELECT.
  2. 2. Which status should a successful DELETE normally return?

    204 says the request succeeded and there is no body. A missing row gives 404.
  3. 3. How does COALESCE($2, title) help an update?

    COALESCE returns the first non-NULL argument, so omitted fields stay unchanged.
  4. 4. Why clamp the limit query parameter?

    Unbounded limits let a single request load huge result sets and strain the database and memory.

Summary

  • Use separate input types and a database type; keep generated fields out of inputs.
  • RETURNING and COALESCE make create and partial update one statement each.
  • Use fetch_optional and rows_affected to produce 404s.
  • Return 201 for create, 200 for read/update, 204 for delete.
  • Clamp limits and prefer keyset pagination on large tables.

Next: Migrations and Transactions