📁 Rust Backend with Axum · Part 15 of 42

Rust Web Server with Axum 0.8: Full-Text Search with FTS5, and Soft Delete

November 3, 2026

Episode 15 of the Rust Backend series, where we build the backend of a blog step by step. After episode 14 we can page, filter and sort our posts. Now our readers want to search: which posts talk about borrowing? Today: full-text search with SQLite's FTS5, and a trash bin for deleted posts. 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 14. No new crates: FTS5 is built into the SQLite that SQLx bundles.

1. Why not LIKE?

The simple way is:

sql

SELECT * FROM posts WHERE body LIKE '%borrow%';

It reads every row, it cannot rank the results, and it does not know that "borrow" and "borrowing" belong together. FTS5 builds an index of words instead, like the index at the back of a book: borrow → posts 3, 7, 12.

2. The FTS5 table and its triggers

bash

sqlx migrate add add_search

sql

CREATE VIRTUAL TABLE posts_fts USING fts5(
    title, body,
    content='posts', content_rowid='id',
    tokenize='porter unicode61'
);

CREATE TRIGGER posts_ai AFTER INSERT ON posts BEGIN
    INSERT INTO posts_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;
CREATE TRIGGER posts_ad AFTER DELETE ON posts BEGIN
    INSERT INTO posts_fts(posts_fts, rowid, title, body) VALUES ('delete', old.id, old.title, old.body);
END;
CREATE TRIGGER posts_au AFTER UPDATE ON posts BEGIN
    INSERT INTO posts_fts(posts_fts, rowid, title, body) VALUES ('delete', old.id, old.title, old.body);
    INSERT INTO posts_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

INSERT INTO posts_fts(posts_fts) VALUES ('rebuild');

posts_fts is a virtual table over title and body. content='posts' means it reads the text from our posts table instead of storing a second copy.
The porter tokenizer matches word forms: "borrowed", "borrowing" and "borrow" are one word.
Three triggers keep the index in sync after every insert, delete and update on posts.
The last line, rebuild, fills the index with the posts we already have.

bash

sqlx migrate run

3. search in the repository

A Hit is what a search returns, and SearchParams reads ?q= from the URL:

rust

#[derive(Serialize)]
pub struct Hit {
    pub id: i64,
    pub title: String,
    pub snippet: String,
}

#[derive(Deserialize)]
pub struct SearchParams {
    pub q: String,
}

In repo.rs, add fn search(&self, q: &str) -> impl Future<Output = sqlx::Result<Vec<Hit>>> + Send; to the trait, and implement it:

rust

async fn search(&self, q: &str) -> sqlx::Result<Vec<Hit>> {
    sqlx::query_as!(
        Hit,
        r#"SELECT p.id as "id!", p.title, snippet(posts_fts, 1, '[', ']', '...', 8) as "snippet!: String"
           FROM posts_fts JOIN posts p ON p.id = posts_fts.rowid
           WHERE posts_fts MATCH ?
           ORDER BY bm25(posts_fts) LIMIT 20"#,
        q
    )
    .fetch_all(&self.pool)
    .await
}

MATCH finds the rows.
bm25() ranks them; a lower score is a better match, so ORDER BY puts the best first.
snippet() returns a short piece of column 1 (the body) with the match marked in [ ], at most 8 words.
"id!" and "snippet!: String" tell the query_as! macro that these columns are never NULL.

4. The /search route

rust

pub async fn search_posts<R: PostRepo>(
    State(repo): State<R>,
    Query(p): Query<SearchParams>,
) -> Result<Json<Vec<Hit>>, ApiError> {
    Ok(Json(repo.search(&p.q).await?))
}

rust

.route("/search", get(handlers::search_posts::<R>))

5. Search

Run, and create three posts about Rust:

bash

cargo run
curl -s localhost:8080/posts -H 'content-type: application/json' -d '{"title":"The borrow checker","body":"Borrowing lets you use a value without taking ownership. The borrow checker enforces the rules."}' > /dev/null
curl -s localhost:8080/posts -H 'content-type: application/json' -d '{"title":"Lifetimes explained","body":"A lifetime tells the compiler how long a reference lives."}' > /dev/null
curl -s localhost:8080/posts -H 'content-type: application/json' -d '{"title":"Async in Rust","body":"Async functions return futures that run on an executor."}' > /dev/null

bash

curl -s 'localhost:8080/search?q=borrowing' | jq

json

[{"id":1,"title":"The borrow checker","snippet":"...The [borrow] checker enforces the rules."}]

The borrow checker post, with the match marked in brackets. Now borrowed:

bash

curl -s 'localhost:8080/search?q=borrowed' | jq

The same post: the porter tokenizer knows these are one word.

6. A quote breaks the search

Now a user types a quotation mark:

bash

curl -i 'localhost:8080/search?q=rust%22'

text

HTTP/1.1 500 Internal Server Error
ERROR ... blog_api::error: database error err=Database(SqliteError { code: 1, message: "unterminated string" })

MATCH has its own query language (AND, OR, quotes, *), and a lone " breaks it. The value is still bound safely with ?, so this is not SQL injection, but it is a 500 for a normal user typing.

7. Quote every word

We wrap every word in double quotes and double any quote inside it. Now user text is always a plain word, never FTS syntax:

rust

fn fts_query(input: &str) -> String {
    input
        .split_whitespace()
        .map(|word| format!("\"{}\"", word.replace('"', "\"\"")))
        .collect::<Vec<_>>()
        .join(" ")
}

and the first line of search becomes let q = fts_query(q);. Run the same request again:

bash

cargo run
curl -i 'localhost:8080/search?q=rust%22'

text

HTTP/1.1 200 OK
content-type: application/json

[{"id":3,"title":"Async in Rust","snippet":"Async functions return futures that run on an..."}]

8. Soft delete

Second topic. Today DELETE removes a post for good. Many apps move it to a trash first: a soft delete, with a deleted_at column.

bash

sqlx migrate add soft_delete

sql

ALTER TABLE posts ADD COLUMN deleted_at TEXT;

bash

sqlx migrate run

delete now sets deleted_at instead of removing the row, and a new restore sets it back:

rust

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

async fn restore(&self, id: i64) -> sqlx::Result<bool> {
    let result = sqlx::query!("UPDATE posts SET deleted_at = NULL WHERE id = ? AND deleted_at IS NOT NULL", id)
        .execute(&self.pool)
        .await?;
    Ok(result.rows_affected() > 0)
}

Every read adds deleted_at IS NULL: get, update, the search, and both queries in list. In list the tag filter moves into a subquery, because the query already has a WHERE:

rust

let mut q = QueryBuilder::<Sqlite>::new("SELECT p.id, p.title, p.body, p.slug, p.created_at FROM posts p WHERE p.deleted_at IS NULL");
if let Some(tag) = &f.tag {
    q.push(" AND p.id IN (SELECT pt.post_id FROM post_tags pt JOIN tags t ON t.id = pt.tag_id WHERE t.name = ")
        .push_bind(tag.clone())
        .push(")");
}

The restore handler and its route:

rust

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

rust

.route("/posts/{id}/restore", post(handlers::restore_post::<R>))

9. Delete, search, restore

bash

cargo run
curl -i -X DELETE localhost:8080/posts/1
curl -s 'localhost:8080/search?q=borrowing'
curl -i localhost:8080/posts/1

The delete answers 204 No Content, the search returns [], and GET /posts/1 is a 404 Not Found. But the row is still in the database:

bash

sqlite3 blog.db 'select id, deleted_at from posts;'

text

1|2026-10-30 02:17:01
2|
3|

Restore it, and the search finds it again:

bash

curl -i -X POST localhost:8080/posts/1/restore

text

HTTP/1.1 200 OK

The final code

migrations/0005_add_search.sql

sql

-- Add migration script here
CREATE VIRTUAL TABLE posts_fts USING fts5(
    title, body,
    content='posts', content_rowid='id',
    tokenize='porter unicode61'
);

CREATE TRIGGER posts_ai AFTER INSERT ON posts BEGIN
    INSERT INTO posts_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;
CREATE TRIGGER posts_ad AFTER DELETE ON posts BEGIN
    INSERT INTO posts_fts(posts_fts, rowid, title, body) VALUES ('delete', old.id, old.title, old.body);
END;
CREATE TRIGGER posts_au AFTER UPDATE ON posts BEGIN
    INSERT INTO posts_fts(posts_fts, rowid, title, body) VALUES ('delete', old.id, old.title, old.body);
    INSERT INTO posts_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

INSERT INTO posts_fts(posts_fts) VALUES ('rebuild');

migrations/0006_soft_delete.sql

sql

-- Add migration script here
ALTER TABLE posts ADD COLUMN deleted_at TEXT;

src/handlers.rs

rust

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

use crate::error::ApiError;
use crate::models::{Comment, Hit, ListFilter, ListParams, NewComment, NewPost, Page, Post, PostWithTags, SearchParams};
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>,
    params: Result<Query<ListParams>, QueryRejection>,
) -> Result<Json<Page<Post>>, ApiError> {
    let Query(p) = params?;
    let page = p.page.unwrap_or(1).max(1);
    let per_page = p.per_page.unwrap_or(10).clamp(1, 100);
    let (items, total) = repo.list(ListFilter { page, per_page, tag: p.tag, sort: p.sort }).await?;
    Ok(Json(Page { items, page, per_page, total }))
}

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)))
}

pub async fn search_posts<R: PostRepo>(
    State(repo): State<R>,
    Query(p): Query<SearchParams>,
) -> Result<Json<Vec<Hit>>, ApiError> {
    Ok(Json(repo.search(&p.q).await?))
}

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

src/main.rs

rust

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

use axum::{routing::{get, post}, 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>))
        .route("/search", get(handlers::search_posts::<R>))
        .route("/posts/{id}/restore", post(handlers::restore_post::<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();
}

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

#[derive(Deserialize)]
pub struct ListParams {
    pub page: Option<u32>,
    pub per_page: Option<u32>,
    pub tag: Option<String>,
    #[serde(default)]
    pub sort: Sort,
}

#[derive(Deserialize, Clone, Copy, Default)]
#[serde(rename_all = "lowercase")]
pub enum Sort {
    #[default]
    Newest,
    Oldest,
    Title,
}

#[derive(Serialize)]
pub struct Page<T> {
    pub items: Vec<T>,
    pub page: u32,
    pub per_page: u32,
    pub total: i64,
}

pub struct ListFilter {
    pub page: u32,
    pub per_page: u32,
    pub tag: Option<String>,
    pub sort: Sort,
}

#[derive(Serialize)]
pub struct Hit {
    pub id: i64,
    pub title: String,
    pub snippet: String,
}

#[derive(Deserialize)]
pub struct SearchParams {
    pub q: String,
}

src/repo.rs

rust

use std::future::Future;

use sqlx::{QueryBuilder, Sqlite, SqlitePool};

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

pub trait PostRepo: Clone + Send + Sync + 'static {
    fn list(&self, f: ListFilter) -> impl Future<Output = sqlx::Result<(Vec<Post>, i64)>> + 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 restore(&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;
    fn search(&self, q: &str) -> impl Future<Output = sqlx::Result<Vec<Hit>>> + Send;
}

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

impl PostRepo for SqlitePostRepo {
    async fn list(&self, f: ListFilter) -> sqlx::Result<(Vec<Post>, i64)> {
        let mut q = QueryBuilder::<Sqlite>::new("SELECT p.id, p.title, p.body, p.slug, p.created_at FROM posts p WHERE p.deleted_at IS NULL");
        if let Some(tag) = &f.tag {
            q.push(" AND p.id IN (SELECT pt.post_id FROM post_tags pt JOIN tags t ON t.id = pt.tag_id WHERE t.name = ")
                .push_bind(tag.clone())
                .push(")");
        }
        q.push(match f.sort {
            Sort::Newest => " ORDER BY p.id DESC",
            Sort::Oldest => " ORDER BY p.id ASC",
            Sort::Title => " ORDER BY p.title ASC",
        });
        q.push(" LIMIT ").push_bind(f.per_page as i64);
        q.push(" OFFSET ").push_bind(((f.page - 1) * f.per_page) as i64);
        let posts = q.build_query_as::<Post>().fetch_all(&self.pool).await?;

        let mut c = QueryBuilder::<Sqlite>::new("SELECT COUNT(*) FROM posts p WHERE p.deleted_at IS NULL");
        if let Some(tag) = &f.tag {
            c.push(" AND p.id IN (SELECT pt.post_id FROM post_tags pt JOIN tags t ON t.id = pt.tag_id WHERE t.name = ")
                .push_bind(tag.clone())
                .push(")");
        }
        let total: i64 = c.build_query_scalar().fetch_one(&self.pool).await?;
        Ok((posts, total))
    }

    async fn search(&self, q: &str) -> sqlx::Result<Vec<Hit>> {
        let q = fts_query(q);
        sqlx::query_as!(
            Hit,
            r#"SELECT p.id as "id!", p.title, snippet(posts_fts, 1, '[', ']', '...', 8) as "snippet!: String"
               FROM posts_fts JOIN posts p ON p.id = posts_fts.rowid
               WHERE posts_fts MATCH ? AND p.deleted_at IS NULL
               ORDER BY bm25(posts_fts) LIMIT 20"#,
            q
        )
        .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 = ? AND deleted_at IS NULL", 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 = ? AND deleted_at IS NULL 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!("UPDATE posts SET deleted_at = CURRENT_TIMESTAMP WHERE id = ? AND deleted_at IS NULL", id)
            .execute(&self.pool)
            .await?;
        Ok(result.rows_affected() > 0)
    }

    async fn restore(&self, id: i64) -> sqlx::Result<bool> {
        let result = sqlx::query!("UPDATE posts SET deleted_at = NULL WHERE id = ? AND deleted_at IS NOT NULL", 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
    }
}

fn fts_query(input: &str) -> String {
    input
        .split_whitespace()
        .map(|word| format!("\"{}\"", word.replace('"', "\"\"")))
        .collect::<Vec<_>>()
        .join(" ")
}

Recap

An FTS5 virtual table with content='posts' indexes our posts; three triggers keep it in sync
MATCH finds, bm25() ranks, snippet() shows the match in context; porter matches word forms
Quote every word of user input, so FTS syntax in a search box cannot cause a 500
Soft delete: deleted_at instead of DELETE, deleted_at IS NULL in every read, and a restore route

Next episode: that ends the database part with SQLite. Next: validation, with clear 422 errors for every bad field. Watch the series on YouTube.