📁 Rust Backend with Axum · Part 13 of 42

Rust Web Server with Axum 0.8: Transactions, 409 Conflict and "database is locked"

November 3, 2026

Episode 13 of the Rust Backend series, where we build the backend of a blog step by step. After episode 12 our posts have comments, linked with a foreign key. Today we meet three things every database app runs into sooner or later: a value that must be unique, two writes that must happen together, and a database that is busy. All the code below is the final, tested code from the video, ready to copy.

New to Rust? Start with the beginner series.
The full backend series playlist is here.

We continue in the same blog_api project from episode 12 (Axum 0.8, SQLx with SQLite, migrations and the query_as! macros). No new crates today.

1. A unique slug for every post

Each post gets a slug: the title in lowercase with dashes, for nice URLs ("Hello World" becomes hello-world). Two posts must never share a slug, so the database enforces it with a unique index.

bash

sqlx migrate add add_slug

sql

ALTER TABLE posts ADD COLUMN slug TEXT NOT NULL DEFAULT '';
UPDATE posts SET slug = 'post-' || id;
CREATE UNIQUE INDEX posts_slug ON posts(slug);

The UPDATE gives the posts we already have a slug each (post-1, post-2, ...), so the unique index can be created.

bash

sqlx migrate run

2. slugify

In models.rs, a small function: lowercase, keep letters and digits, and turn everything else into a single dash.

rust

pub fn slugify(title: &str) -> String {
    let mut slug = String::new();
    for c in title.trim().to_lowercase().chars() {
        if c.is_alphanumeric() {
            slug.push(c);
        } else if !slug.ends_with('-') {
            slug.push('-');
        }
    }
    slug.trim_end_matches('-').to_string()
}

Post gets a pub slug: String field, every SELECT and RETURNING lists slug, and create computes it from the title:

rust

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

3. The same title twice: 500

Run the server and create "Hello World" twice:

bash

cargo run
curl -i localhost:8080/posts -H 'content-type: application/json' -d '{"title":"Hello World","body":"First"}'
curl -i localhost:8080/posts -H 'content-type: application/json' -d '{"title":"Hello World","body":"First"}'

The first answer is 201 Created, the second 500 Internal Server Error, and the log says why:

text

ERROR request{method=POST uri=/posts version=HTTP/1.1}: blog_api::error: database error err=Database(SqliteError { code: 2067, message: "UNIQUE constraint failed: posts.slug" })

This is not a bug in our server: the client sent a duplicate. A 5xx tells the client "our fault", which is wrong here.

4. 409 Conflict

The right answer is 409 Conflict. A new variant in error.rs:

rust

#[error("{0}")]
Conflict(String),

and in into_response it maps to the status:

rust

ApiError::Conflict(_) => StatusCode::CONFLICT,

In the create_post handler we look at the database error. SQLx can tell us if it was a unique violation:

rust

let post = repo.create(new).await.map_err(|err| match err.as_database_error() {
    Some(e) if e.is_unique_violation() => ApiError::Conflict("a post with this title already exists".into()),
    _ => ApiError::Database(err),
})?;

Run again and send the same post:

text

HTTP/1.1 409 Conflict
content-type: application/json
content-length: 49

{"error":"a post with this title already exists"}

5. Tags: two more tables

A post can have tags. They live in a tags table, and post_tags connects posts and tags. A tag name may be at most 20 characters.

bash

sqlx migrate add tags

sql

CREATE TABLE tags (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE CHECK (length(name) <= 20)
);
CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

bash

sqlx migrate run

NewPost gets #[serde(default)] pub tags: Vec<String> (so old requests without tags still work), and the answer becomes a PostWithTags. #[serde(flatten)] puts the post's fields and tags side by side in one JSON object:

rust

#[derive(Serialize)]
pub struct PostWithTags {
    #[serde(flatten)]
    pub post: Post,
    pub tags: Vec<String>,
}

6. Three writes, no transaction yet

create now does three things: insert the post, insert each tag (or nothing if it exists), and link it to the post.

rust

async fn create(&self, new: NewPost) -> sqlx::Result<PostWithTags> {
    let slug = slugify(&new.title);
    let post = sqlx::query_as!(
        Post,
        r#"INSERT INTO posts (title, body, slug) VALUES (?, ?, ?) RETURNING id, title, body, slug, created_at"#,
        new.title,
        new.body,
        slug
    )
    .fetch_one(&self.pool)
    .await?;
    for tag in &new.tags {
        sqlx::query!("INSERT INTO tags (name) VALUES (?) ON CONFLICT (name) DO NOTHING", tag)
            .execute(&self.pool)
            .await?;
        sqlx::query!(
            "INSERT INTO post_tags (post_id, tag_id) SELECT ?, id FROM tags WHERE name = ?",
            post.id,
            tag
        )
        .execute(&self.pool)
        .await?;
    }
    Ok(PostWithTags { post, tags: new.tags })
}

Now create a post with one tag that is far too long:

bash

curl -i localhost:8080/posts -H 'content-type: application/json' -d '{"title":"Second","body":"b","tags":["rust","this-tag-is-much-too-long-for-us"]}'

text

HTTP/1.1 500 Internal Server Error
ERROR ... blog_api::error: database error err=Database(SqliteError { code: 275, message: "CHECK constraint failed: length(name) <= 20" })

An error, fine. But look at the list:

bash

curl -s localhost:8080/posts

The post "Second" is there. Its tags are not. Half of the work was saved, and the client was told it failed.

7. A transaction: all or nothing

A transaction groups the writes. pool.begin() starts one, and every query runs on the transaction (&mut *tx) instead of the pool. At the end, commit.

rust

let mut tx = self.pool.begin().await?;
let post = sqlx::query_as!(/* same INSERT as before */)
    .fetch_one(&mut *tx)
    .await?;
for tag in &new.tags {
    // both queries: .execute(&mut *tx)
}
tx.commit().await?;

The important part is what happens when we don't reach commit: if a query fails, ? returns early, the transaction is dropped without a commit, and SQLite rolls everything back.

Delete the half post, restart, and send the same request:

bash

curl -i -X DELETE localhost:8080/posts/2
cargo run
curl -i localhost:8080/posts -H 'content-type: application/json' -d '{"title":"Second","body":"b","tags":["rust","this-tag-is-much-too-long-for-us"]}'
curl -s localhost:8080/posts

Still a 500 with the CHECK error, but now the list has no half post: nothing was saved. With good tags it works, and the tags come back in the answer:

bash

curl -i localhost:8080/posts -H 'content-type: application/json' -d '{"title":"Second","body":"b","tags":["rust","axum"]}'

text

HTTP/1.1 201 Created

8. "database is locked"

Third: a busy database. SQLite allows one writer at a time. Let's hold the write lock from another process for 25 seconds:

bash

(echo "BEGIN IMMEDIATE;"; sleep 25; echo "COMMIT;") | sqlite3 blog.db &

Now create a post from another terminal:

bash

time curl -i localhost:8080/posts -H 'content-type: application/json' -d '{"title":"Third","body":"b"}'

It waits... about five seconds... then:

text

HTTP/1.1 500 Internal Server Error

real	0m5.046s
ERROR ... blog_api::error: database error err=Database(SqliteError { code: 5, message: "database is locked" })

SQLx waited for SQLite's busy timeout (5 seconds by default) and gave up. When the lock is released, the same request works at once with 201 Created.

So, the rules: keep transactions short, never wait for something slow (a network call, an email) inside one, and remember that with WAL mode (episode 9) readers never wait for a writer at all.

The final code

migrations/0003_add_slug.sql

sql

-- Add migration script here
ALTER TABLE posts ADD COLUMN slug TEXT NOT NULL DEFAULT '';
UPDATE posts SET slug = 'post-' || id;
CREATE UNIQUE INDEX posts_slug ON posts(slug);

migrations/0004_tags.sql

sql

-- Add migration script here
CREATE TABLE tags (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE CHECK (length(name) <= 20)
);
CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

src/error.rs

rust

use axum::extract::rejection::JsonRejection;
use axum::http::StatusCode;
use axum::response::{IntoResponse, Response};
use axum::Json;
use serde_json::json;
use thiserror::Error;

#[derive(Debug, Error)]
pub enum ApiError {
    #[error("post {0} not found")]
    NotFound(i64),
    #[error("title must not be empty")]
    EmptyTitle,
    #[error(transparent)]
    BadJson(#[from] JsonRejection),
    #[error("database error")]
    Database(#[from] sqlx::Error),
    #[error("{0}")]
    Conflict(String),
}

impl IntoResponse for ApiError {
    fn into_response(self) -> Response {
        tracing::warn!(error = %self, "request failed");
        let status = match &self {
            ApiError::NotFound(_) => StatusCode::NOT_FOUND,
            ApiError::Conflict(_) => StatusCode::CONFLICT,
            ApiError::EmptyTitle => StatusCode::BAD_REQUEST,
            ApiError::BadJson(rejection) => rejection.status(),
            ApiError::Database(err) => {
                tracing::error!(?err, "database error");
                StatusCode::INTERNAL_SERVER_ERROR
            }
        };
        let message = match &self {
            ApiError::BadJson(rejection) => rejection.body_text(),
            other => other.to_string(),
        };
        (status, Json(json!({ "error": message }))).into_response()
    }
}

src/handlers.rs

rust

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

use crate::error::ApiError;
use crate::models::{Comment, NewComment, NewPost, Post, PostWithTags};
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<PostWithTags>), ApiError> {
    let Json(new) = payload?;
    check(&new)?;
    let post = repo.create(new).await.map_err(|err| match err.as_database_error() {
        Some(e) if e.is_unique_violation() => ApiError::Conflict("a post with this title already exists".into()),
        _ => ApiError::Database(err),
    })?;
    tracing::debug!(id = post.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)))
}

src/models.rs

rust

use serde::{Deserialize, Serialize};

#[derive(Serialize, Clone, sqlx::FromRow)]
pub struct Post {
    pub id: i64,
    pub title: String,
    pub body: String,
    pub slug: String,
    pub created_at: String,
}

#[derive(Deserialize)]
pub struct NewPost {
    pub title: String,
    pub body: String,
    #[serde(default)]
    pub tags: Vec<String>,
}

#[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,
}

pub fn slugify(title: &str) -> String {
    let mut slug = String::new();
    for c in title.trim().to_lowercase().chars() {
        if c.is_alphanumeric() {
            slug.push(c);
        } else if !slug.ends_with('-') {
            slug.push('-');
        }
    }
    slug.trim_end_matches('-').to_string()
}

#[derive(Serialize)]
pub struct PostWithTags {
    #[serde(flatten)]
    pub post: Post,
    pub tags: Vec<String>,
}

src/repo.rs

rust

use std::future::Future;

use sqlx::SqlitePool;

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

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<PostWithTags>> + 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, slug, 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, slug, created_at FROM posts WHERE id = ?", id)
            .fetch_optional(&self.pool)
            .await
    }

    async fn create(&self, new: NewPost) -> sqlx::Result<PostWithTags> {
        let slug = slugify(&new.title);
        let mut tx = self.pool.begin().await?;
        let post = sqlx::query_as!(
            Post,
            r#"INSERT INTO posts (title, body, slug) VALUES (?, ?, ?) RETURNING id, title, body, slug, created_at"#,
            new.title,
            new.body,
            slug
        )
        .fetch_one(&mut *tx)
        .await?;
        for tag in &new.tags {
            sqlx::query!("INSERT INTO tags (name) VALUES (?) ON CONFLICT (name) DO NOTHING", tag)
                .execute(&mut *tx)
                .await?;
            sqlx::query!(
                "INSERT INTO post_tags (post_id, tag_id) SELECT ?, id FROM tags WHERE name = ?",
                post.id,
                tag
            )
            .execute(&mut *tx)
            .await?;
        }
        tx.commit().await?;
        Ok(PostWithTags { post, tags: new.tags })
    }

    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, title, body, slug, 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
    }
}

Recap

A unique index plus is_unique_violation() turns a duplicate into 409 Conflict instead of 500
pool.begin() starts a transaction; run every query on &mut *tx, then tx.commit()
Leaving early with ? drops the transaction without a commit: SQLite rolls back, nothing half-saved
SQLite has one writer at a time; a request waits up to the busy timeout (5 s), then fails with "database is locked"
Keep transactions short

Next episode: many posts. Pagination, filters and sorting with query parameters. Watch the series on YouTube.