M Logo
Hire Me
📁 Rust Backend with Axum · Part 12 of 42

Rust Web Server with Axum 0.8: Comments, Foreign Keys and ON DELETE CASCADE

November 3, 2026

Episode 12 of the Rust Backend series, where we build the backend of a blog step by step. After episode 11 all of CRUD for posts runs on SQLite behind a PostRepo trait. A blog needs comments, and each comment belongs to one post. Today: a second table, a relation, and a foreign key that keeps the data honest. We add the comments table with ON DELETE CASCADE, two repository methods and two handlers, meet a nullable RETURNING id error, see what a foreign key violation looks like (a 500!), and turn it into a clean 404. All the code below is the final, tested code from the video, ready to copy.

We continue in the same blog_api project from episode 11 (axum 0.8.9, sqlx 0.9 with SQLite, migrations, the PostRepo trait). No new crates today.

1. The relation

text
posts 1 ── * comments

One post has many comments; each comment has one post_id.

2. A new migration

bash
sqlx migrate add create_comments
ls migrations

3. The comments table

sql
-- Add migration script here
CREATE TABLE comments (
    id INTEGER PRIMARY KEY,
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    body TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX comments_post_id ON comments(post_id);

post_id INTEGER NOT NULL REFERENCES posts(id) is the foreign key. ON DELETE CASCADE: when a post is deleted, its comments go with it. The index makes "all comments of a post" fast.

4. Models

A Comment struct, and NewComment with just the body. The post id comes from the URL. Add this to src/models.rs:

rust
#[derive(Serialize, Clone)]
pub struct Comment {
    pub id: i64,
    pub post_id: i64,
    pub body: String,
    pub created_at: String,
}

#[derive(Deserialize)]
pub struct NewComment {
    pub body: String,
}

5. Two repository methods

list_comments for a post, and add_comment. Add them to the PostRepo trait and to SqlitePostRepo in src/repo.rs:

rust
fn list_comments(&self, post_id: i64) -> impl Future<Output = sqlx::Result<Vec<Comment>>> + Send;
fn add_comment(&self, post_id: i64, new: NewComment) -> impl Future<Output = sqlx::Result<Comment>> + Send;

6. Two handlers

list_comments first checks that the post exists, so an unknown post gives 404 and not an empty list. add_comment just inserts (we improve it in step 11). Add to src/handlers.rs:

rust
pub async fn list_comments<R: PostRepo>(
    State(repo): State<R>,
    Path(id): Path<i64>,
) -> Result<Json<Vec<Comment>>, ApiError> {
    repo.get(id).await?.ok_or(ApiError::NotFound(id))?;
    Ok(Json(repo.list_comments(id).await?))
}

7. The route

rust
.route("/posts/{id}/comments", get(handlers::list_comments::<R>).post(handlers::add_comment::<R>))

8. migrate run, build, and an error

The macros check the real database, so first apply the migration with the CLI:

bash
sqlx migrate run
cargo build
text
error[E0277]: the trait bound `i64: From<std::option::Option<i64>>` is not satisfied
  --> src/repo.rs:76:9
   |
76 | /         sqlx::query_as!(
77 | |             Comment,
78 | |             r#"INSERT INTO comments (post_id, body) VALUES (?, ?) RETURNING id, post_id, body, created_at"#,

As far as SQLite can tell, RETURNING id could be null. We tell SQLx "never null" with a bang: id as "id!":

rust
r#"INSERT INTO comments (post_id, body) VALUES (?, ?) RETURNING id as "id!", post_id, body, created_at"#,

Then cargo sqlx prepare and cargo run. The final src/repo.rs:

rust
use std::future::Future;

use sqlx::SqlitePool;

use crate::models::{Comment, NewComment, NewPost, Post};

pub trait PostRepo: Clone + Send + Sync + 'static {
    fn list(&self) -> impl Future<Output = sqlx::Result<Vec<Post>>> + Send;
    fn get(&self, id: i64) -> impl Future<Output = sqlx::Result<Option<Post>>> + Send;
    fn create(&self, new: NewPost) -> impl Future<Output = sqlx::Result<Post>> + Send;
    fn update(&self, id: i64, new: NewPost) -> impl Future<Output = sqlx::Result<Option<Post>>> + Send;
    fn delete(&self, id: i64) -> impl Future<Output = sqlx::Result<bool>> + Send;
    fn list_comments(&self, post_id: i64) -> impl Future<Output = sqlx::Result<Vec<Comment>>> + Send;
    fn add_comment(&self, post_id: i64, new: NewComment) -> impl Future<Output = sqlx::Result<Comment>> + Send;
}

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

impl PostRepo for SqlitePostRepo {
    async fn list(&self) -> sqlx::Result<Vec<Post>> {
        sqlx::query_as!(Post, "SELECT id, title, body, created_at FROM posts ORDER BY id")
            .fetch_all(&self.pool)
            .await
    }

    async fn get(&self, id: i64) -> sqlx::Result<Option<Post>> {
        sqlx::query_as!(Post, "SELECT id, title, body, created_at FROM posts WHERE id = ?", id)
            .fetch_optional(&self.pool)
            .await
    }

    async fn create(&self, new: NewPost) -> sqlx::Result<Post> {
        sqlx::query_as!(
            Post,
            r#"INSERT INTO posts (title, body) VALUES (?, ?) RETURNING id as "id!", title, body, created_at"#,
            new.title,
            new.body
        )
        .fetch_one(&self.pool)
        .await
    }

    async fn update(&self, id: i64, new: NewPost) -> sqlx::Result<Option<Post>> {
        sqlx::query_as!(
            Post,
            r#"UPDATE posts SET title = ?, body = ? WHERE id = ? RETURNING id as "id!", title, body, created_at"#,
            new.title,
            new.body,
            id
        )
        .fetch_optional(&self.pool)
        .await
    }

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

    async fn list_comments(&self, post_id: i64) -> sqlx::Result<Vec<Comment>> {
        sqlx::query_as!(
            Comment,
            "SELECT id, post_id, body, created_at FROM comments WHERE post_id = ? ORDER BY id",
            post_id
        )
        .fetch_all(&self.pool)
        .await
    }

    async fn add_comment(&self, post_id: i64, new: NewComment) -> sqlx::Result<Comment> {
        sqlx::query_as!(
            Comment,
            r#"INSERT INTO comments (post_id, body) VALUES (?, ?) RETURNING id as "id!", post_id, body, created_at"#,
            post_id,
            new.body
        )
        .fetch_one(&self.pool)
        .await
    }
}

9. Use it

bash
curl -i localhost:8080/posts -H 'content-type: application/json' -d '{"title":"Hello","body":"First post"}'
curl -i localhost:8080/posts/1/comments -H 'content-type: application/json' -d '{"body":"Nice post"}'
curl -i localhost:8080/posts/1/comments -H 'content-type: application/json' -d '{"body":"Thanks"}'
curl -i localhost:8080/posts/1/comments

Two 201 Created, then the list with both comments.

10. A comment on a post that does not exist

bash
curl -i localhost:8080/posts/99/comments -H 'content-type: application/json' -d '{"body":"Hi"}'
text
HTTP/1.1 500 Internal Server Error
content-type: application/json

{"error":"database error"}

The log shows why: FOREIGN KEY constraint failed. The database refused a comment that points at nothing. That is good, but 500 says the server is broken, and it is not.

11. Map the foreign key violation to 404

We look at the error. If it is a foreign key violation, the post was missing: that is our 404. Anything else stays a database error. The final add_comment:

rust
use axum::extract::{rejection::JsonRejection, Path, State};
use axum::{http::StatusCode, Json};

use crate::error::ApiError;
use crate::models::{Comment, NewComment, NewPost, Post};
use crate::repo::PostRepo;

fn check(new: &NewPost) -> Result<(), ApiError> {
    if new.title.trim().is_empty() {
        return Err(ApiError::EmptyTitle);
    }
    Ok(())
}

#[tracing::instrument(skip(repo, payload))]
pub async fn create_post<R: PostRepo>(
    State(repo): State<R>,
    payload: Result<Json<NewPost>, JsonRejection>,
) -> Result<(StatusCode, Json<Post>), ApiError> {
    let Json(new) = payload?;
    check(&new)?;
    let post = repo.create(new).await?;
    tracing::debug!(id = post.id, "created");
    Ok((StatusCode::CREATED, Json(post)))
}

pub async fn get_post<R: PostRepo>(State(repo): State<R>, Path(id): Path<i64>) -> Result<Json<Post>, ApiError> {
    let post = repo.get(id).await?.ok_or(ApiError::NotFound(id))?;
    Ok(Json(post))
}

pub async fn list_posts<R: PostRepo>(State(repo): State<R>) -> Result<Json<Vec<Post>>, ApiError> {
    let posts = repo.list().await?;
    Ok(Json(posts))
}

pub async fn update_post<R: PostRepo>(
    State(repo): State<R>,
    Path(id): Path<i64>,
    payload: Result<Json<NewPost>, JsonRejection>,
) -> Result<Json<Post>, ApiError> {
    let Json(new) = payload?;
    check(&new)?;
    let post = repo.update(id, new).await?.ok_or(ApiError::NotFound(id))?;
    Ok(Json(post))
}

pub async fn delete_post<R: PostRepo>(State(repo): State<R>, Path(id): Path<i64>) -> Result<StatusCode, ApiError> {
    if !repo.delete(id).await? {
        return Err(ApiError::NotFound(id));
    }
    Ok(StatusCode::NO_CONTENT)
}

pub async fn list_comments<R: PostRepo>(
    State(repo): State<R>,
    Path(id): Path<i64>,
) -> Result<Json<Vec<Comment>>, ApiError> {
    repo.get(id).await?.ok_or(ApiError::NotFound(id))?;
    Ok(Json(repo.list_comments(id).await?))
}

pub async fn add_comment<R: PostRepo>(
    State(repo): State<R>,
    Path(id): Path<i64>,
    payload: Result<Json<NewComment>, JsonRejection>,
) -> Result<(StatusCode, Json<Comment>), ApiError> {
    let Json(new) = payload?;
    let comment = repo.add_comment(id, new).await.map_err(|err| {
        match err.as_database_error() {
            Some(db_err) if db_err.is_foreign_key_violation() => ApiError::NotFound(id),
            _ => ApiError::Database(err),
        }
    })?;
    Ok((StatusCode::CREATED, Json(comment)))
}

Run again with the same request:

text
HTTP/1.1 404 Not Found
content-type: application/json

{"error":"post 99 not found"}

12. Cascade

Delete post 1, and ask the database how many comments are left:

bash
curl -i -X DELETE localhost:8080/posts/1
sqlite3 blog.db 'select count(*) from comments;'
text
0

Zero: cascade removed them.

13. One warning: the sqlite3 tool

The sqlite3 command line tool turns foreign keys off by default. Insert a comment for post 99 there, and it works!

bash
sqlite3 blog.db "insert into comments (post_id, body) values (99, 'x');"
sqlite3 blog.db 'select * from comments;'
text
1|99|x|2026-10-30 00:55:50

SQLx turns foreign keys on for us; other tools may not. Remove the bad row:

bash
sqlite3 blog.db 'delete from comments where post_id = 99;'

The final main.rs

rust
mod config;
mod error;
mod handlers;
mod models;
mod repo;

use axum::{routing::get, Router};
use repo::{PostRepo, SqlitePostRepo};
use sqlx::sqlite::{SqliteConnectOptions, SqliteJournalMode, SqlitePoolOptions};
use std::str::FromStr;
use tower_http::trace::TraceLayer;
use tracing_subscriber::EnvFilter;

pub fn app<R: PostRepo>(repo: R) -> Router {
    Router::new()
        .route("/posts", get(handlers::list_posts::<R>).post(handlers::create_post::<R>))
        .route(
            "/posts/{id}",
            get(handlers::get_post::<R>).put(handlers::update_post::<R>).delete(handlers::delete_post::<R>),
        )
        .route("/posts/{id}/comments", get(handlers::list_comments::<R>).post(handlers::add_comment::<R>))
        .with_state(repo)
        .layer(TraceLayer::new_for_http())
}

#[tokio::main]
async fn main() {
    let cfg = config::config();
    tracing_subscriber::fmt()
        .with_env_filter(EnvFilter::try_from_default_env().unwrap_or_else(|_| EnvFilter::new("info")))
        .init();
    let addr = format!("{}:{}", cfg.host, cfg.port);
    let options = SqliteConnectOptions::from_str(&cfg.database_url)
        .unwrap()
        .create_if_missing(true)
        .journal_mode(SqliteJournalMode::Wal);
    let pool = SqlitePoolOptions::new()
        .max_connections(5)
        .connect_with(options)
        .await
        .unwrap();
    sqlx::migrate!().run(&pool).await.unwrap();
    let app = app(SqlitePostRepo { pool });

    let listener = tokio::net::TcpListener::bind(&addr).await.unwrap();
    tracing::info!(%addr, "listening");
    axum::serve(listener, app).await.unwrap();
}

Recap

  • a comments table with REFERENCES posts(id) (a foreign key) and ON DELETE CASCADE
  • nested routes: /posts/{id}/comments
  • RETURNING id can look nullable: id as "id!"
  • a foreign key violation is not a server bug: is_foreign_key_violation() turns it into a clean 404
  • the sqlite3 tool does not enforce foreign keys unless you turn them on

Next episode: transactions, unique titles with 409 Conflict, and the famous "database is locked". Watch the series on YouTube.

© 2026 All Rights Reserved By Mahmudul Alam