Rust Web Server with Axum 0.8: A Real Database with SQLx and SQLite

November 3, 2026

Episode 9 of the Rust Backend series, where we build the backend of a blog step by step. After episode 8 our server logs every request, but every time it stops, all posts are gone: they live in memory. Today we fix that with a real database: SQLite, through SQLx. We add a connection pool, meet the "unable to open database file" panic, create the file and the table, turn rows into our Post struct with FromRow, rewrite the handlers with query_as, and teach ApiError about database errors. 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 8. One new crate today: sqlx (0.9) with SQLite. For the last step you need the sqlite3 command line tool (sudo apt install sqlite3 on Ubuntu).

1. The problem

Run the server, create a post, stop the server, start it again and list the posts: the list is empty. Arc<Mutex<Store>> lives only as long as the process.

2. Why SQLite

SQLite keeps the whole database in one file. There is no server to install, and it is fast. Later in the series we can switch to PostgreSQL.

3. The crate

bash

cargo add sqlx --features runtime-tokio,sqlite

4. The database URL

The URL goes into .env (and .env.example):

text

DATABASE_URL=sqlite://blog.db

and into our Config, in src/config.rs:

rust

pub struct Config {
    pub host: String,
    pub port: u16,
    pub database_url: String,
}

// in from_env()
let database_url = std::env::var("DATABASE_URL").unwrap_or("sqlite://blog.db".to_string());
Config { host, port, database_url }

5. A pool

A pool keeps a few open connections and lends them to handlers. We connect it at the start of main:

rust

use sqlx::sqlite::SqlitePoolOptions;

let pool = SqlitePoolOptions::new()
    .max_connections(5)
    .connect(&cfg.database_url)
    .await
    .unwrap();

(At this point the handlers still use the old store, so pool is unused: a warning, nothing more.) Run it:

text

thread 'main' (40182) panicked at src/main.rs:23:10:
called `Result::unwrap()` on an `Err` value: Database(SqliteError { code: 14, message: "unable to open database file" })

SQLite does not create the file unless we ask.

6. Connect options: create_if_missing and WAL

rust

use sqlx::sqlite::{SqliteConnectOptions, SqliteJournalMode, SqlitePoolOptions};
use std::str::FromStr;

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();

create_if_missing(true) creates blog.db. WAL mode lets readers work while a writer writes.

7. A table (for today)

rust

sqlx::query("CREATE TABLE IF NOT EXISTS posts (id INTEGER PRIMARY KEY, title TEXT NOT NULL, body TEXT NOT NULL)")
    .execute(&pool)
    .await
    .unwrap();

Next episode, migrations do this properly.

8. The pool replaces the Store

An SqlitePool is already cheap to clone, so it needs no Arc and no Mutex. In src/models.rs the Store is gone and Db is the pool:

rust

pub type Db = sqlx::SqlitePool;

In main, Db::default() goes away and the router gets .with_state(pool).

9. FromRow and i64

Post derives sqlx::FromRow, so SQLx can turn a row into a Post. The id becomes i64, because SQLite integers are 64 bit (ApiError::NotFound takes an i64 now too):

rust

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

10. The handlers

list_posts: query_as, fetch_all and await. create_post: ? placeholders, bind for each value, and RETURNING * gives us the new row back. get_post: fetch_optional gives None when there is no row, and ok_or gives us our NotFound. Never put values into the SQL string with format!: use ? and bind.

rust

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

use crate::error::ApiError;
use crate::models::{Db, NewPost, Post};

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

#[tracing::instrument(skip(db, payload))]
pub async fn create_post(
    State(db): State<Db>,
    payload: Result<Json<NewPost>, JsonRejection>,
) -> Result<(StatusCode, Json<Post>), ApiError> {
    let Json(new) = payload?;
    check(&new)?;
    let post = sqlx::query_as::<_, Post>("INSERT INTO posts (title, body) VALUES (?, ?) RETURNING *")
        .bind(&new.title)
        .bind(&new.body)
        .fetch_one(&db)
        .await?;
    tracing::debug!(id = post.id, "created");
    Ok((StatusCode::CREATED, Json(post)))
}

pub async fn get_post(State(db): State<Db>, Path(id): Path<i64>) -> Result<Json<Post>, ApiError> {
    let post = sqlx::query_as::<_, Post>("SELECT * FROM posts WHERE id = ?")
        .bind(id)
        .fetch_optional(&db)
        .await?
        .ok_or(ApiError::NotFound(id))?;
    Ok(Json(post))
}

pub async fn list_posts(State(db): State<Db>) -> Result<Json<Vec<Post>>, ApiError> {
    let posts = sqlx::query_as::<_, Post>("SELECT * FROM posts ORDER BY id")
        .fetch_all(&db)
        .await?;
    Ok(Json(posts))
}

update_post and delete_post still used the old store, so for now they are taken out of the router; next episode they come back, on the database:

rust

.route(
    "/posts/{id}",
    get(handlers::get_post),
)

11. The ? error

Run it and the compiler says: the ? could not convert sqlx::Error to ApiError. Every query can fail, and we have no variant for that.

text

error[E0277]: `?` couldn't convert the error to `ApiError`
  --> src/handlers.rs:25:15
   |
...
   |                this can't be annotated with `?` because it has type `Result<_, sqlx::Error>`
   |
note: `ApiError` needs to implement `From<sqlx::Error>`
  --> src/error.rs:9:1

12. A Database variant

A new variant: Database, from sqlx::Error, with status 500. The real error goes to the log and the client only sees "database error":

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

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

13. Use it, restart, look inside

bash

cargo run

bash

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

text

HTTP/1.1 201 Created
content-type: application/json
content-length: 44

{"id":1,"title":"Hello","body":"First post"}

Stop the server, start it again and list: the post is still there. And here is the file, blog.db. With the sqlite3 tool we can look inside:

bash

ls
sqlite3 blog.db 'select * from posts;'

text

1|Hello|First post

SQLite in WAL mode also writes blog.db-wal and blog.db-shm, so .gitignore gets one more line:

text

/target
.env
blog.db*

The final main.rs

rust

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

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

#[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::query("CREATE TABLE IF NOT EXISTS posts (id INTEGER PRIMARY KEY, title TEXT NOT NULL, body TEXT NOT NULL)")
        .execute(&pool)
        .await
        .unwrap();
    let app = Router::new()
        .route("/posts", get(handlers::list_posts).post(handlers::create_post))
        .route(
            "/posts/{id}",
            get(handlers::get_post),
        )
        .with_state(pool)
        .layer(TraceLayer::new_for_http());

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

The final config.rs

rust

use std::sync::OnceLock;

pub struct Config {
    pub host: String,
    pub port: u16,
    pub database_url: String,
}

impl Config {
    fn from_env() -> Config {
        dotenvy::dotenv().ok();
        let host = std::env::var("HOST").unwrap_or("0.0.0.0".to_string());
        let port = std::env::var("PORT").unwrap_or("3000".to_string());
        let port = port
            .parse()
            .unwrap_or_else(|e| panic!("PORT {port:?} is not a port number: {e}"));
        let database_url = std::env::var("DATABASE_URL").unwrap_or("sqlite://blog.db".to_string());
        Config { host, port, database_url }
    }
}

static CONFIG: OnceLock<Config> = OnceLock::new();

pub fn config() -> &'static Config {
    CONFIG.get_or_init(Config::from_env)
}

Recap

SQLx with SQLite: a pool of connections, shared with State
create_if_missing(true) and WAL mode through SqliteConnectOptions
FromRow turns a row into our struct; SQLite integers are i64
query_as with ? and bind; fetch_all, fetch_one, fetch_optional
a Database variant for ApiError: 500 to the client, the details in the log

Next episode: migrations, and queries checked by the compiler. With one missing letter, it will not even compile. Watch the series on YouTube.