Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 38 ยท 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