📁 Rust Backend with Axum · Part 10 of 42

Rust Web Server with Axum 0.8: SQLx Migrations and Compile-Time Checked Queries

November 3, 2026

Episode 10 of the Rust Backend series, where we build the backend of a blog step by step. After episode 9 our posts live in SQLite, but the table is created by a line of SQL in main. What happens when we want a new column next month? Today: migrations, and queries that the compiler checks for us. We create a migration with sqlx migrate add, run it from the program with sqlx::migrate!(), switch to the query_as! macro that asks the real database at compile time, catch a typo inside a SQL string, and make the project build without a database using cargo sqlx prepare. 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 9 (axum 0.8.9, tokio, sqlx 0.9 with SQLite). You also need the sqlx command line tool; it is installed once:

bash

cargo install sqlx-cli --no-default-features --features sqlite,rustls

1. The problem

main creates the table with CREATE TABLE IF NOT EXISTS. That works once, but it cannot change a table that already exists.

2. What is a migration

A migration is a numbered SQL file. SQLx runs each file once, in order, and remembers which ones already ran.

3. sqlx-cli

bash

sqlx --version

text

sqlx-cli 0.9.0

4. sqlx migrate add

bash

sqlx migrate add create_posts
ls migrations

text

Creating migrations/20261030003651_create_posts.sql
20261030003651_create_posts.sql

A new folder, migrations, with one file. The number is the time it was created.

5. The SQL

Move the create table into the file, and add a column: created_at, with the current time as default.

sql

-- Add migration script here
CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

(-- Add migration script here is the line sqlx writes for you.)

6. migrate!() in main

In main, the CREATE TABLE line goes away. The migrate! macro puts the migrations into our program, and run applies the new ones at start:

rust

sqlx::migrate!().run(&pool).await.unwrap();

7. Start fresh

Our old blog.db already has a posts table, without migrations. Delete it and start fresh:

bash

rm blog.db*
ls

(blog.db* also removes blog.db-wal and blog.db-shm.)

8. Run

bash

cargo run

The migration runs, and the server starts.

9. query_as! checks at compile time

query_as! (with an exclamation mark) is a macro. At compile time it asks the real database: does this table exist, do these columns exist, which types do they have? The macro finds the database through DATABASE_URL, which it also reads from .env.

10. A typo on purpose

Change list_posts and make a typo: titel.

rust

let posts = sqlx::query_as!(Post, "SELECT id, titel, body, created_at FROM posts ORDER BY id")
    .fetch_all(&db)
    .await?;

bash

cargo build

11. Caught

text

error: error returned from database: (code: 1) no such column: titel
  --> src/handlers.rs:40:17
   |
40 |     let posts = sqlx::query_as!(Post, "SELECT id, titel, body, created_at FROM posts ORDER BY id")
   |                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The compiler found a typo inside a string. Without the macro, this would fail only when a user calls the route.

12. Fix it, and add created_at to Post

Fix the name. The macro maps each column to a field of the struct, so Post gets created_at:

rust

use serde::{Deserialize, Serialize};

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

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

pub type Db = sqlx::SqlitePool;

13. get_post and create_post

They become macros too. The values go straight into the macro, no bind calls. The types are checked as well: these columns are NOT NULL, so Rust gets plain types. If a column could be NULL we would get an Option, and a bang after the name, like id as "id!", tells SQLx: trust me, never null.

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 id, title, body, created_at",
        new.title,
        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 id, title, body, created_at FROM posts WHERE id = ?", 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 id, title, body, created_at FROM posts ORDER BY id")
        .fetch_all(&db)
        .await?;
    Ok(Json(posts))
}

Run it, create a post, and get it back with its time:

bash

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

text

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

{"id":1,"title":"Hello","body":"First post","created_at":"2026-10-30 00:39:55"}

14. One catch: the database at compile time

The macros need the database while compiling. On a build server there is no blog.db. Let's pretend: move .env away, and touch a file so it compiles again.

bash

mv .env /tmp/
touch src/handlers.rs
cargo build

text

error: set `DATABASE_URL` to use query macros online, or run `cargo sqlx prepare` to update the query cache
  --> src/handlers.rs:21:16

The error even tells us the fix.

15. cargo sqlx prepare

Bring .env back and run cargo sqlx prepare. It saves what it learned into a .sqlx folder:

bash

mv /tmp/.env .
cargo sqlx prepare

text

query data written to .sqlx in the current directory; please check this into version control

16. Offline mode

Now offline mode builds without the database:

bash

mv .env /tmp/
touch src/handlers.rs
SQLX_OFFLINE=true cargo build
mv /tmp/.env .

text

Finished `dev` profile [unoptimized + debuginfo] target(s) in 1.48s

The .sqlx folder goes into git, and we run cargo sqlx prepare again after every query change.

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::migrate!().run(&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();
}

Recap

a migration is a numbered SQL file: sqlx migrate add, then sqlx::migrate!().run(&pool) at start
query_as! asks the database at compile time: a typo in a column name is a compile error
NOT NULL columns become plain types; a bang ("id!") overrides a nullable guess
without a database the build fails; cargo sqlx prepare saves a query cache in .sqlx/, and SQLX_OFFLINE=true builds from it

Next episode: update and delete come back on the database, behind a Repository trait. Watch the series on YouTube.