REST API with axum and SQLite

This project builds a small web service: a JSON API for a task list, backed by SQLite. It combines the lessons on serde, axum, and sqlx, plus async traits, into one layered application.

Method and path	Does	Success
`POST /tasks`	create a task from `{"title": "..."}`	`201 Created` with a `Location` header
`GET /tasks?done=false&q=milk&limit=10&offset=0`	list, filtered and paginated	`200 OK`
`GET /tasks/{id}`	fetch one	`200 OK` or `404`
`PATCH /tasks/{id}`	change `title` and/or `done`	`200 OK` or `404`
`DELETE /tasks/{id}`	delete	`204 No Content` or `404`

Every error has a JSON body such as {"error":"task 9 not found"}.

The application is split into the usual layers, each built from plain Rust types:

Layer	This project
HTTP handlers	handler functions with extractors (`State`, `Path`, `Query`, `Json`)
Data access	a `TaskRepo` trait + `SqliteRepo` with sqlx
Error handling	`ApiError` implementing `IntoResponse`
Validation	a `validate_title` function called by handlers
Tests	`tower::ServiceExt::oneshot` against the `Router`

Terminal

cargo new tasks-api
cd tasks-api
cargo add axum serde_json
cargo add tokio --features full
cargo add serde --features derive
cargo add sqlx --features runtime-tokio,sqlite
cargo add --dev tower --features util

Step 1: The API's types and validation

The JSON shapes are plain structs. Input types are separate from the stored Task, which has an id the client doesn't send. #[serde(deny_unknown_fields)] turns a typo such as "titel" into an error instead of silently ignoring it. For PATCH, every field is an Option: a missing field means "don't change it".

Validation is a function rather than annotations. It returns the cleaned value, so a handler can't use the unvalidated one by accident.

Rust

use serde::{Deserialize, Serialize};

#[derive(Debug, Clone, Serialize, PartialEq)]
struct Task {
    id: i64,
    title: String,
    done: bool,
}

#[derive(Debug, Deserialize)]
#[serde(deny_unknown_fields)]
struct NewTask {
    title: String,
}

#[derive(Debug, Deserialize)]
#[serde(deny_unknown_fields)]
struct TaskPatch {
    title: Option<String>,
    done: Option<bool>,
}

fn validate_title(title: &str) -> Result<String, String> {
    let title = title.trim();
    if title.is_empty() {
        return Err(String::from("title must not be empty"));
    }
    if title.chars().count() > 100 {
        return Err(String::from("title must be at most 100 characters"));
    }
    Ok(title.to_string())
}

fn main() {
    for body in [r#"{"title": "  buy milk "}"#, r#"{"title": ""}"#, r#"{"titel": "typo"}"#, r#"{}"#] {
        let parsed = serde_json::from_str::<NewTask>(body).map_err(|e| e.to_string()).and_then(|t| validate_title(&t.title));
        println!("{body:<26} -> {parsed:?}");
    }

    let patch: TaskPatch = serde_json::from_str(r#"{"done": true}"#).unwrap();
    println!("{patch:?}");

    let task = Task { id: 1, title: String::from("buy milk"), done: false };
    println!("{}", serde_json::to_string(&task).unwrap());
}

Output

{"title": "  buy milk "}   -> Ok("buy milk")
{"title": ""}              -> Err("title must not be empty")
{"titel": "typo"}          -> Err("unknown field `titel`, expected `title` at line 1 column 8")
{}                         -> Err("missing field `title` at line 1 column 2")
TaskPatch { title: None, done: Some(true) }
{"id":1,"title":"buy milk","done":false}

Step 2: The repository over SQLite

Handlers shouldn't contain SQL, so data access goes behind a trait. The trait is written with -> impl Future<Output = ...> + Send instead of async fn: axum runs handlers on a multi-threaded runtime, so a handler that's generic over the repository needs to know the repository's futures are Send. Implementations still write plain async fn.

The SQL uses features worth knowing:

RETURNING gives back the inserted or updated row in the same statement.
COALESCE(?, title) keeps the old value when a PATCH field is NULL (missing).
QueryBuilder builds the list query from optional filters, with every value still sent as a bind parameter, never pasted into the SQL.

Rust

use serde::Deserialize;
use sqlx::sqlite::{SqlitePool, SqlitePoolOptions};
use sqlx::{FromRow, QueryBuilder, Sqlite};
use std::future::Future;

#[derive(Debug, Clone, FromRow)]
struct Task {
    id: i64,
    title: String,
    done: bool,
}

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

#[derive(Debug, Default, Deserialize)]
struct ListParams {
    done: Option<bool>,
    q: Option<String>,
    limit: Option<u32>,
    offset: Option<u32>,
}

#[derive(Debug)]
struct StorageError(String);

impl From<sqlx::Error> for StorageError {
    fn from(error: sqlx::Error) -> Self {
        StorageError(error.to_string())
    }
}

type Result<T> = std::result::Result<T, StorageError>;

trait TaskRepo: Clone + Send + Sync + 'static {
    fn create(&self, title: &str) -> impl Future<Output = Result<Task>> + Send;
    fn get(&self, id: i64) -> impl Future<Output = Result<Option<Task>>> + Send;
    fn list(&self, params: &ListParams) -> impl Future<Output = Result<Vec<Task>>> + Send;
    fn update(&self, id: i64, patch: TaskPatch) -> impl Future<Output = Result<Option<Task>>> + Send;
    fn delete(&self, id: i64) -> impl Future<Output = Result<bool>> + Send;
}

#[derive(Clone)]
struct SqliteRepo {
    pool: SqlitePool,
}

impl SqliteRepo {
    async fn connect(url: &str) -> Result<SqliteRepo> {
        let pool = SqlitePoolOptions::new().max_connections(1).connect(url).await?;
        sqlx::query(
            "CREATE TABLE IF NOT EXISTS tasks (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                done BOOLEAN NOT NULL DEFAULT FALSE
            )",
        )
        .execute(&pool)
        .await?;
        Ok(SqliteRepo { pool })
    }
}

impl TaskRepo for SqliteRepo {
    async fn create(&self, title: &str) -> Result<Task> {
        let sql = "INSERT INTO tasks (title) VALUES (?) RETURNING id, title, done";
        Ok(sqlx::query_as(sql).bind(title).fetch_one(&self.pool).await?)
    }

    async fn get(&self, id: i64) -> Result<Option<Task>> {
        let sql = "SELECT id, title, done FROM tasks WHERE id = ?";
        Ok(sqlx::query_as(sql).bind(id).fetch_optional(&self.pool).await?)
    }

    async fn list(&self, params: &ListParams) -> Result<Vec<Task>> {
        let mut query: QueryBuilder<Sqlite> = QueryBuilder::new("SELECT id, title, done FROM tasks WHERE 1 = 1");
        if let Some(done) = params.done {
            query.push(" AND done = ").push_bind(done);
        }
        if let Some(text) = &params.q {
            query.push(" AND title LIKE ").push_bind(format!("%{text}%"));
        }
        let limit = params.limit.unwrap_or(20).min(100); // never let a client ask for everything
        query.push(" ORDER BY id LIMIT ").push_bind(limit as i64);
        query.push(" OFFSET ").push_bind(params.offset.unwrap_or(0) as i64);
        Ok(query.build_query_as().fetch_all(&self.pool).await?)
    }

    async fn update(&self, id: i64, patch: TaskPatch) -> Result<Option<Task>> {
        let sql = "UPDATE tasks SET title = COALESCE(?, title), done = COALESCE(?, done)
                   WHERE id = ? RETURNING id, title, done";
        Ok(sqlx::query_as(sql).bind(patch.title).bind(patch.done).bind(id).fetch_optional(&self.pool).await?)
    }

    async fn delete(&self, id: i64) -> Result<bool> {
        let result = sqlx::query("DELETE FROM tasks WHERE id = ?").bind(id).execute(&self.pool).await?;
        Ok(result.rows_affected() == 1)
    }
}

#[tokio::main]
async fn main() -> Result<()> {
    let repo = SqliteRepo::connect("sqlite::memory:").await?;
    for title in ["buy milk", "write report", "call plumber", "buy stamps"] {
        repo.create(title).await?;
    }
    repo.update(2, TaskPatch { done: Some(true), ..TaskPatch::default() }).await?;

    let titles = |tasks: Vec<Task>| tasks.into_iter().map(|t| format!("{}:{}{}", t.id, t.title, if t.done { " (done)" } else { "" })).collect::<Vec<_>>();
    println!("all:        {:?}", titles(repo.list(&ListParams::default()).await?));
    println!("open:       {:?}", titles(repo.list(&ListParams { done: Some(false), ..Default::default() }).await?));
    println!("q=buy:      {:?}", titles(repo.list(&ListParams { q: Some("buy".into()), ..Default::default() }).await?));
    println!("page 2:     {:?}", titles(repo.list(&ListParams { limit: Some(2), offset: Some(2), ..Default::default() }).await?));
    println!("get 9:      {:?}", repo.get(9).await?);
    println!("delete 3:   {}, again: {}", repo.delete(3).await?, repo.delete(3).await?);
    Ok(())
}

Output

all:        ["1:buy milk", "2:write report (done)", "3:call plumber", "4:buy stamps"]
open:       ["1:buy milk", "3:call plumber", "4:buy stamps"]
q=buy:      ["1:buy milk", "4:buy stamps"]
page 2:     ["3:call plumber", "4:buy stamps"]
get 9:      None
delete 3:   true, again: false

The trait makes the storage replaceable. The final step uses that to test how the API behaves when the database is down, without stopping a database.

Step 3: The complete API

Handlers are generic over R: TaskRepo, and the router is built by app(repo), so the same code serves requests with SQLite in production and with any other implementation in tests. Everything that can go wrong becomes an ApiError, and its IntoResponse implementation is the only place that decides status codes and error bodies:

`ApiError`	Status
`Invalid` (validation, malformed JSON, unknown fields)	`422 Unprocessable Entity`
`NotFound`	`404 Not Found`
`Internal` (storage failures)	`500 Internal Server Error`, details logged, not sent to the client

Axum's own Json extractor would reject bad JSON with a plain-text body. Taking Result<Json<T>, JsonRejection> instead lets the handler turn that into the same JSON error format as everything else.

main starts the real server with cargo run -- serve. Without arguments, it sends a series of requests through oneshot, the way the tests do, and prints each request and response.

Rust

use axum::extract::rejection::JsonRejection;
use axum::extract::{Path, Query, State};
use axum::http::{StatusCode, header};
use axum::response::{IntoResponse, Response};
use axum::routing::get;
use axum::{Json, Router};
use serde::{Deserialize, Serialize};
use sqlx::sqlite::{SqlitePool, SqlitePoolOptions};
use sqlx::{FromRow, QueryBuilder, Sqlite};
use std::future::Future;

// ---------- model.rs ----------

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

#[derive(Debug, Deserialize)]
#[serde(deny_unknown_fields)]
struct NewTask {
    title: String,
}

#[derive(Debug, Default, Deserialize)]
#[serde(deny_unknown_fields)]
struct TaskPatch {
    title: Option<String>,
    done: Option<bool>,
}

#[derive(Debug, Default, Deserialize)]
struct ListParams {
    done: Option<bool>,
    q: Option<String>,
    limit: Option<u32>,
    offset: Option<u32>,
}

fn validate_title(title: &str) -> Result<String, ApiError> {
    let title = title.trim();
    if title.is_empty() {
        return Err(ApiError::Invalid(String::from("title must not be empty")));
    }
    if title.chars().count() > 100 {
        return Err(ApiError::Invalid(String::from("title must be at most 100 characters")));
    }
    Ok(title.to_string())
}

// ---------- error.rs ----------

#[derive(Debug)]
struct StorageError(String);

impl From<sqlx::Error> for StorageError {
    fn from(error: sqlx::Error) -> Self {
        StorageError(error.to_string())
    }
}

#[derive(Debug)]
enum ApiError {
    Invalid(String),
    NotFound(i64),
    Internal(String),
}

impl From<StorageError> for ApiError {
    fn from(error: StorageError) -> Self {
        ApiError::Internal(error.0)
    }
}

impl From<JsonRejection> for ApiError {
    fn from(rejection: JsonRejection) -> Self {
        ApiError::Invalid(rejection.body_text())
    }
}

impl IntoResponse for ApiError {
    fn into_response(self) -> Response {
        let (status, message) = match self {
            ApiError::Invalid(message) => (StatusCode::UNPROCESSABLE_ENTITY, message),
            ApiError::NotFound(id) => (StatusCode::NOT_FOUND, format!("task {id} not found")),
            ApiError::Internal(detail) => {
                eprintln!("internal error: {detail}"); // a real service would use tracing::error!
                (StatusCode::INTERNAL_SERVER_ERROR, String::from("internal error"))
            }
        };
        (status, Json(serde_json::json!({ "error": message }))).into_response()
    }
}

// ---------- repo.rs ----------

type RepoResult<T> = Result<T, StorageError>;

trait TaskRepo: Clone + Send + Sync + 'static {
    fn create(&self, title: &str) -> impl Future<Output = RepoResult<Task>> + Send;
    fn get(&self, id: i64) -> impl Future<Output = RepoResult<Option<Task>>> + Send;
    fn list(&self, params: &ListParams) -> impl Future<Output = RepoResult<Vec<Task>>> + Send;
    fn update(&self, id: i64, patch: TaskPatch) -> impl Future<Output = RepoResult<Option<Task>>> + Send;
    fn delete(&self, id: i64) -> impl Future<Output = RepoResult<bool>> + Send;
}

#[derive(Clone)]
struct SqliteRepo {
    pool: SqlitePool,
}

impl SqliteRepo {
    async fn connect(url: &str) -> RepoResult<SqliteRepo> {
        let pool = SqlitePoolOptions::new().max_connections(1).connect(url).await?;
        sqlx::query(
            "CREATE TABLE IF NOT EXISTS tasks (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                done BOOLEAN NOT NULL DEFAULT FALSE
            )",
        )
        .execute(&pool)
        .await?;
        Ok(SqliteRepo { pool })
    }
}

impl TaskRepo for SqliteRepo {
    async fn create(&self, title: &str) -> RepoResult<Task> {
        let sql = "INSERT INTO tasks (title) VALUES (?) RETURNING id, title, done";
        Ok(sqlx::query_as(sql).bind(title).fetch_one(&self.pool).await?)
    }

    async fn get(&self, id: i64) -> RepoResult<Option<Task>> {
        let sql = "SELECT id, title, done FROM tasks WHERE id = ?";
        Ok(sqlx::query_as(sql).bind(id).fetch_optional(&self.pool).await?)
    }

    async fn list(&self, params: &ListParams) -> RepoResult<Vec<Task>> {
        let mut query: QueryBuilder<Sqlite> = QueryBuilder::new("SELECT id, title, done FROM tasks WHERE 1 = 1");
        if let Some(done) = params.done {
            query.push(" AND done = ").push_bind(done);
        }
        if let Some(text) = &params.q {
            query.push(" AND title LIKE ").push_bind(format!("%{text}%"));
        }
        query.push(" ORDER BY id LIMIT ").push_bind(params.limit.unwrap_or(20).min(100) as i64);
        query.push(" OFFSET ").push_bind(params.offset.unwrap_or(0) as i64);
        Ok(query.build_query_as().fetch_all(&self.pool).await?)
    }

    async fn update(&self, id: i64, patch: TaskPatch) -> RepoResult<Option<Task>> {
        let sql = "UPDATE tasks SET title = COALESCE(?, title), done = COALESCE(?, done)
                   WHERE id = ? RETURNING id, title, done";
        Ok(sqlx::query_as(sql).bind(patch.title).bind(patch.done).bind(id).fetch_optional(&self.pool).await?)
    }

    async fn delete(&self, id: i64) -> RepoResult<bool> {
        let result = sqlx::query("DELETE FROM tasks WHERE id = ?").bind(id).execute(&self.pool).await?;
        Ok(result.rows_affected() == 1)
    }
}

/// A repository whose database is always down, for testing error handling.
#[derive(Clone)]
struct BrokenRepo;

impl TaskRepo for BrokenRepo {
    async fn create(&self, _: &str) -> RepoResult<Task> {
        Err(StorageError(String::from("connection refused")))
    }
    async fn get(&self, _: i64) -> RepoResult<Option<Task>> {
        Err(StorageError(String::from("connection refused")))
    }
    async fn list(&self, _: &ListParams) -> RepoResult<Vec<Task>> {
        Err(StorageError(String::from("connection refused")))
    }
    async fn update(&self, _: i64, _: TaskPatch) -> RepoResult<Option<Task>> {
        Err(StorageError(String::from("connection refused")))
    }
    async fn delete(&self, _: i64) -> RepoResult<bool> {
        Err(StorageError(String::from("connection refused")))
    }
}

// ---------- handlers.rs ----------

async fn create_task<R: TaskRepo>(
    State(repo): State<R>,
    body: Result<Json<NewTask>, JsonRejection>,
) -> Result<impl IntoResponse, ApiError> {
    let Json(input) = body?;
    let task = repo.create(&validate_title(&input.title)?).await?;
    let location = format!("/tasks/{}", task.id);
    Ok((StatusCode::CREATED, [(header::LOCATION, location)], Json(task)))
}

async fn list_tasks<R: TaskRepo>(State(repo): State<R>, Query(params): Query<ListParams>) -> Result<Json<Vec<Task>>, ApiError> {
    Ok(Json(repo.list(&params).await?))
}

async fn get_task<R: TaskRepo>(State(repo): State<R>, Path(id): Path<i64>) -> Result<Json<Task>, ApiError> {
    repo.get(id).await?.map(Json).ok_or(ApiError::NotFound(id))
}

async fn update_task<R: TaskRepo>(
    State(repo): State<R>,
    Path(id): Path<i64>,
    body: Result<Json<TaskPatch>, JsonRejection>,
) -> Result<Json<Task>, ApiError> {
    let Json(mut patch) = body?;
    if let Some(title) = &patch.title {
        patch.title = Some(validate_title(title)?);
    }
    repo.update(id, patch).await?.map(Json).ok_or(ApiError::NotFound(id))
}

async fn delete_task<R: TaskRepo>(State(repo): State<R>, Path(id): Path<i64>) -> Result<StatusCode, ApiError> {
    if repo.delete(id).await? { Ok(StatusCode::NO_CONTENT) } else { Err(ApiError::NotFound(id)) }
}

fn app<R: TaskRepo>(repo: R) -> Router {
    Router::new()
        .route("/health", get(|| async { "ok" }))
        .route("/tasks", get(list_tasks::<R>).post(create_task::<R>))
        .route("/tasks/{id}", get(get_task::<R>).patch(update_task::<R>).delete(delete_task::<R>))
        .with_state(repo)
}

// ---------- main.rs ----------

async fn send(app: &Router, method: &str, uri: &str, body: &str) {
    use axum::body::Body;
    use axum::http::Request;
    use tower::ServiceExt;

    let request = Request::builder()
        .method(method)
        .uri(uri)
        .header(header::CONTENT_TYPE, "application/json")
        .body(Body::from(body.to_string()))
        .unwrap();
    let response = app.clone().oneshot(request).await.unwrap();
    let status = response.status();
    let location = response.headers().get(header::LOCATION).map(|v| format!(" (Location: {})", v.to_str().unwrap()));
    let bytes = axum::body::to_bytes(response.into_body(), usize::MAX).await.unwrap();
    println!("{method} {uri} {body}");
    println!("  -> {} {}{}", status.as_u16(), String::from_utf8_lossy(&bytes), location.unwrap_or_default());
}

#[tokio::main]
async fn main() {
    if std::env::args().nth(1).as_deref() == Some("serve") {
        let repo = SqliteRepo::connect("sqlite://tasks.db?mode=rwc").await.expect("cannot open tasks.db");
        let listener = tokio::net::TcpListener::bind("127.0.0.1:3000").await.unwrap();
        println!("listening on http://127.0.0.1:3000");
        axum::serve(listener, app(repo)).await.unwrap();
        return;
    }

    let app = app(SqliteRepo::connect("sqlite::memory:").await.unwrap());
    send(&app, "POST", "/tasks", r#"{"title": "buy milk"}"#).await;
    send(&app, "POST", "/tasks", r#"{"title": "write report"}"#).await;
    send(&app, "POST", "/tasks", r#"{"title": "   "}"#).await;
    send(&app, "POST", "/tasks", r#"{"titel": "typo"}"#).await;
    send(&app, "POST", "/tasks", r#"{"title": "#).await;
    send(&app, "PATCH", "/tasks/1", r#"{"done": true}"#).await;
    send(&app, "GET", "/tasks?done=false", "").await;
    send(&app, "GET", "/tasks/2", "").await;
    send(&app, "DELETE", "/tasks/2", "").await;
    send(&app, "GET", "/tasks/2", "").await;
    send(&app, "GET", "/tasks/abc", "").await;
    send(&app, "GET", "/tasks", "").await;

    println!("--- with the database down");
    let broken = self::app(BrokenRepo);
    send(&broken, "GET", "/tasks", "").await;
    send(&broken, "GET", "/health", "").await;
}

Output

POST /tasks {"title": "buy milk"}
  -> 201 {"id":1,"title":"buy milk","done":false} (Location: /tasks/1)
POST /tasks {"title": "write report"}
  -> 201 {"id":2,"title":"write report","done":false} (Location: /tasks/2)
POST /tasks {"title": "   "}
  -> 422 {"error":"title must not be empty"}
POST /tasks {"titel": "typo"}
  -> 422 {"error":"Failed to deserialize the JSON body into the target type: titel: unknown field `titel`, expected `title` at line 1 column 8"}
POST /tasks {"title":
  -> 422 {"error":"Failed to parse the request body as JSON: title: EOF while parsing a value at line 1 column 10"}
PATCH /tasks/1 {"done": true}
  -> 200 {"id":1,"title":"buy milk","done":true}
GET /tasks?done=false
  -> 200 [{"id":2,"title":"write report","done":false}]
GET /tasks/2
  -> 200 {"id":2,"title":"write report","done":false}
DELETE /tasks/2
  -> 204
GET /tasks/2
  -> 404 {"error":"task 2 not found"}
GET /tasks/abc
  -> 400 Invalid URL: Cannot parse `abc` to a `i64`
GET /tasks
  -> 200 [{"id":1,"title":"buy milk","done":true}]
--- with the database down
GET /tasks
  -> 500 {"error":"internal error"}
GET /health
  -> 200 ok

The one plain-text response is for GET /tasks/abc: axum's Path extractor rejected the request before the handler ran. Taking Result<Path<i64>, PathRejection>, as the handlers already do for JSON bodies, would make it JSON too.

The 500 response says only "internal error"; the cause ("connection refused") went to the log on standard error. Leaking database errors to clients is a common security finding, and here there's exactly one place to get it right.

Try the real server with curl:

Terminal

cargo run -- serve
curl -i -X POST localhost:3000/tasks -H 'content-type: application/json' -d '{"title":"buy milk"}'
curl 'localhost:3000/tasks?done=false&limit=5'

Ideas for extending it

Replace eprintln! with tracing and add tower_http::trace::TraceLayer for request logs (see Logging & Tracing).
Move the schema into migration files run by sqlx::migrate!() (see Databases with sqlx).
Switch to PostgreSQL: change the pool type and the SQL placeholders ($1 instead of ?); the handlers don't change.
Add authentication as an extractor that reads a bearer token and rejects the request with 401 before the handler runs.
Return the total count for pagination in a header, or wrap lists in {"items": [...], "next_offset": 20}.

Summary

Separate input types (NewTask, TaskPatch) from stored types (Task); deny_unknown_fields catches typos, and Option fields express partial updates.
A repository trait with -> impl Future + Send methods lets handlers be generic over storage; implementations still write async fn.
sqlx with RETURNING, COALESCE, and QueryBuilder keeps SQL explicit and every value bound as a parameter.
One ApiError with IntoResponse maps every failure to a status code and a JSON body, and hides internal details.
oneshot tests the full HTTP layer without a network, and a fake repository tests failure paths that are hard to produce for real.

Next: Concurrent Web Crawler.