Chapter 22

Databases & Persistence

Our todos vanish when the server restarts. Time to persist them in a real database. We'll use SQLite for simplicity and sqlx for async, compile-time-checked queries.

Connecting to SQLite with rusqlite or sqlx

Rust has two main approaches to databases:

rusqlite

Synchronous (blocking)
Thin wrapper over SQLite's C API
Simple, low overhead
SQLite only
Good for: CLI tools, simple apps, scripts

sqlx

Async-native
Compile-time query checking
Built-in connection pooling
SQLite, Postgres, MySQL
Good for: web servers, async apps

Since our server is async with Tokio, sqlx is the natural fit. We'll use SQLite as the database - no external server to install, the database is just a file.

cargo add sqlx --features "runtime-tokio,sqlite,migrate"

# Install the sqlx CLI for migrations
cargo install sqlx-cli --features sqlite

The features:

runtime-tokio - use Tokio as the async runtime
sqlite - SQLite driver
migrate - built-in migration support

A Quick Look at rusqlite

For comparison - and because understanding the synchronous version helps - here's rusqlite:

use rusqlite::{Connection, params};

fn main() -> rusqlite::Result<()> {
    let conn = Connection::open("todos.db")?;

    conn.execute(
        "CREATE TABLE IF NOT EXISTS todos (
            id    INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            completed BOOLEAN NOT NULL DEFAULT FALSE
        )",
        [],
    )?;

    // Insert
    conn.execute(
        "INSERT INTO todos (title) VALUES (?1)",
        params!["Learn Rust"],
    )?;

    // Query
    let mut stmt = conn.prepare("SELECT id, title, completed FROM todos")?;
    let todos = stmt.query_map([], |row| {
        Ok((
            row.get::<_, i64>(0)?,
            row.get::<_, String>(1)?,
            row.get::<_, bool>(2)?,
        ))
    })?;

    for todo in todos {
        let (id, title, completed) = todo?;
        println!("{}: {} ({})", id, title, if completed { "✓" } else { " " });
    }

    Ok(())
}

This works but blocks the thread on every query. In our async server, that stalls other connections. Let's move to sqlx.

Async Database Queries with sqlx

sqlx is async-native and has a killer feature: compile-time query verification. The sqlx::query! macro checks your SQL against the actual database schema at compile time. If you misspell a column name or use the wrong type, it's a compile error - not a runtime crash.

use sqlx::SqlitePool;

// Connect to (or create) a SQLite database
let pool = SqlitePool::connect("sqlite:todos.db?mode=rwc").await?;
//                                              ^^^^^^^^
//                                              rwc = read-write-create

// Simple query - no compile-time checking
let rows = sqlx::query("SELECT id, title, completed FROM todos")
    .fetch_all(&pool)
    .await?;

for row in &rows {
    let id: i64 = row.get("id");
    let title: &str = row.get("title");
    println!("{}: {}", id, title);
}

Compile-Time Checked Queries

The real power - query! and query_as! macros check SQL at compile time:

use sqlx::FromRow;
use serde::Serialize;

#[derive(Debug, FromRow, Serialize)]
struct Todo {
    id: i64,
    title: String,
    completed: bool,
}

// query_as! maps rows directly to your struct
let todos = sqlx::query_as!(
    Todo,
    "SELECT id, title, completed FROM todos ORDER BY id"
)
.fetch_all(&pool)
.await?;

// query! returns anonymous structs with named fields
let row = sqlx::query!(
    "SELECT id, title FROM todos WHERE id = ?",
    todo_id
)
.fetch_optional(&pool)
.await?;

if let Some(row) = row {
    println!("{}: {}", row.id, row.title);
}

Note

For compile-time checking to work, sqlx needs access to the database at compile time. Set the DATABASE_URL environment variable: DATABASE_URL="sqlite:todos.db" cargo build. Alternatively, use sqlx::query() (runtime-checked strings) during development and switch to query!() once the schema is stable.

Fetch Methods

Method	Returns	Use when
.fetch_all()	Vec<Row>	Listing all rows
.fetch_one()	Row	Exactly one row expected (errors if not)
.fetch_optional()	Option<Row>	Zero or one row (lookups by ID)
.execute()	SqliteQueryResult	INSERT, UPDATE, DELETE (no rows returned)

Connection Pooling

Opening a new database connection per request is slow. A connection pool pre-opens a set of connections and hands them out to requests. sqlx has this built in:

use sqlx::sqlite::{SqlitePool, SqlitePoolOptions};

let pool = SqlitePoolOptions::new()
    .max_connections(10)           // up to 10 concurrent connections
    .min_connections(2)            // keep at least 2 warm
    .acquire_timeout(std::time::Duration::from_secs(5))
    .idle_timeout(std::time::Duration::from_secs(600))
    .connect("sqlite:todos.db?mode=rwc")
    .await?;

// The pool is Clone + Send + Sync - share it with Arc or clone it directly
// (SqlitePool is already internally reference-counted)

// Use the pool directly in queries - sqlx borrows a connection automatically
let todos = sqlx::query_as::<_, Todo>(
    "SELECT id, title, completed FROM todos"
)
.fetch_all(&pool)
.await?;

SqlitePool is already reference-counted internally - you can clone it cheaply. No need for Arc<SqlitePool>; just clone the pool and pass it to each task.

Tip

For SQLite specifically, the pool size is less critical than for Postgres or MySQL because SQLite is an in-process library, not a network server. But the pool still helps by reusing prepared statements and avoiding open/close overhead.

Migrations and Schema Management

Migrations are versioned SQL scripts that evolve your database schema over time. sqlx has built-in migration support. Create a migrations/ directory at your project root:

# Create the migrations directory and first migration
sqlx migrate add create_todos

This creates a timestamped file like migrations/20260410120000_create_todos.sql. Edit it:

migrations/20260410120000_create_todos.sql

CREATE TABLE IF NOT EXISTS todos (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    title       TEXT NOT NULL,
    completed   BOOLEAN NOT NULL DEFAULT FALSE,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Add another migration later as requirements change:

sqlx migrate add add_description

migrations/20260411090000_add_description.sql

ALTER TABLE todos ADD COLUMN description TEXT;

Run migrations in two ways:

# From the CLI
DATABASE_URL="sqlite:todos.db" sqlx migrate run

# Check migration status
DATABASE_URL="sqlite:todos.db" sqlx migrate info

// Or at application startup (recommended)
sqlx::migrate!("./migrations")
    .run(&pool)
    .await?;

// This runs any pending migrations automatically.
// Already-applied migrations are skipped.
// The migration state is tracked in a _sqlx_migrations table.

Note

Running migrations at startup is the standard approach for applications. It ensures the schema is always up to date, whether you're deploying to production or a teammate clones the repo. The migrate!() macro embeds the SQL files into the binary at compile time.

Persisting Your REST API Resources

Now let's replace the in-memory Store from Chapter 14 with a database-backed version. The handler signatures stay the same - only the store implementation changes:

src/db.rs

use sqlx::SqlitePool;
use crate::error::ServerError;

#[derive(Debug, Clone, sqlx::FromRow, serde::Serialize)]
pub struct Todo {
    pub id: i64,
    pub title: String,
    pub completed: bool,
    pub created_at: String,
}

#[derive(Debug, serde::Deserialize)]
pub struct CreateTodo {
    pub title: String,
}

#[derive(Debug, serde::Deserialize)]
pub struct UpdateTodo {
    pub title: Option<String>,
    pub completed: Option<bool>,
}

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

impl Db {
    pub async fn connect(url: &str) -> Result<Self, ServerError> {
        let pool = SqlitePool::connect(url).await.map_err(|e| {
            ServerError::Io(std::io::Error::new(
                std::io::ErrorKind::ConnectionRefused,
                format!("database connection failed: {}", e),
            ))
        })?;

        // Run migrations
        sqlx::migrate!("./migrations")
            .run(&pool)
            .await
            .map_err(|e| {
                ServerError::Io(std::io::Error::new(
                    std::io::ErrorKind::Other,
                    format!("migration failed: {}", e),
                ))
            })?;

        Ok(Db { pool })
    }

    pub async fn list_todos(&self) -> Result<Vec<Todo>, ServerError> {
        let todos = sqlx::query_as::<_, Todo>(
            "SELECT id, title, completed, created_at FROM todos ORDER BY id"
        )
        .fetch_all(&self.pool)
        .await
        .map_err(db_error)?;

        Ok(todos)
    }

    pub async fn get_todo(&self, id: i64) -> Result<Option<Todo>, ServerError> {
        let todo = sqlx::query_as::<_, Todo>(
            "SELECT id, title, completed, created_at FROM todos WHERE id = ?"
        )
        .bind(id)
        .fetch_optional(&self.pool)
        .await
        .map_err(db_error)?;

        Ok(todo)
    }

    pub async fn create_todo(&self, input: CreateTodo) -> Result<Todo, ServerError> {
        let result = sqlx::query(
            "INSERT INTO todos (title) VALUES (?) RETURNING id, title, completed, created_at"
        )
        .bind(&input.title)
        .fetch_one(&self.pool)
        .await
        .map_err(db_error)?;

        Ok(Todo {
            id: result.get("id"),
            title: result.get("title"),
            completed: result.get("completed"),
            created_at: result.get("created_at"),
        })
    }

    pub async fn update_todo(
        &self,
        id: i64,
        input: UpdateTodo,
    ) -> Result<Option<Todo>, ServerError> {
        // Build the update dynamically based on which fields are present
        let existing = match self.get_todo(id).await? {
            Some(t) => t,
            None => return Ok(None),
        };

        let title = input.title.unwrap_or(existing.title);
        let completed = input.completed.unwrap_or(existing.completed);

        sqlx::query(
            "UPDATE todos SET title = ?, completed = ? WHERE id = ?"
        )
        .bind(&title)
        .bind(completed)
        .bind(id)
        .execute(&self.pool)
        .await
        .map_err(db_error)?;

        self.get_todo(id).await
    }

    pub async fn delete_todo(&self, id: i64) -> Result<bool, ServerError> {
        let result = sqlx::query("DELETE FROM todos WHERE id = ?")
            .bind(id)
            .execute(&self.pool)
            .await
            .map_err(db_error)?;

        Ok(result.rows_affected() > 0)
    }
}

fn db_error(e: sqlx::Error) -> ServerError {
    ServerError::Io(std::io::Error::new(
        std::io::ErrorKind::Other,
        format!("database error: {}", e),
    ))
}

Warning

Always use parameterized queries (.bind()) - never interpolate user input into SQL strings. sqlx::query("... WHERE id = ?").bind(id) is safe from SQL injection. format!("... WHERE id = ", id) is not.

Wire it into the server:

src/main.rs

use db::Db;

#[tokio::main]
async fn main() {
    tracing_subscriber::fmt().init();

    // Connect to database and run migrations
    let db = Db::connect("sqlite:todos.db?mode=rwc")
        .await
        .expect("failed to connect to database");

    tracing::info!("Database connected, migrations applied");

    // Build router with database
    let app = build_app(db.clone());

    let addr = "127.0.0.1:8080";
    let listener = tokio::net::TcpListener::bind(addr).await.unwrap();
    tracing::info!(addr, "Listening");

    loop {
        if let Ok((stream, _)) = listener.accept().await {
            let app = app.clone();
            let db = db.clone();
            tokio::spawn(async move {
                // handle connection with db access...
            });
        }
    }
}

Test it - data persists across restarts:

# Start the server
cargo run

# Create some todos
curl -s -X POST http://localhost:8080/api/todos \
  -H "Content-Type: application/json" \
  -d '{"title":"Survive a restart"}' | jq

# Stop the server (Ctrl+C), restart it
cargo run

# The todo is still there!
curl -s http://localhost:8080/api/todos | jq
# [{"id":1,"title":"Survive a restart","completed":false,"created_at":"..."}]

Database Patterns

Transactions

When multiple queries must succeed or fail together, use a transaction:

pub async fn complete_all(&self) -> Result<u64, ServerError> {
    let mut tx = self.pool.begin().await.map_err(db_error)?;

    let result = sqlx::query("UPDATE todos SET completed = TRUE WHERE completed = FALSE")
        .execute(&mut *tx)
        .await
        .map_err(db_error)?;

    // If anything fails before commit, all changes are rolled back
    tx.commit().await.map_err(db_error)?;

    Ok(result.rows_affected())
}

Error Mapping

A cleaner approach - add a Database variant to your error type and implement From:

#[derive(Debug)]
pub enum ServerError {
    Io(std::io::Error),
    ParseError(String),
    NotFound(String),
    Database(String),  // new variant
}

impl From<sqlx::Error> for ServerError {
    fn from(e: sqlx::Error) -> Self {
        ServerError::Database(e.to_string())
    }
}

// Now ? works directly on sqlx calls:
pub async fn list_todos(&self) -> Result<Vec<Todo>, ServerError> {
    let todos = sqlx::query_as::<_, Todo>(
        "SELECT id, title, completed, created_at FROM todos ORDER BY id"
    )
    .fetch_all(&self.pool)
    .await?;  // sqlx::Error → ServerError::Database via From

    Ok(todos)
}

Testing with In-Memory SQLite

#[cfg(test)]
mod tests {
    use super::*;

    async fn test_db() -> Db {
        // :memory: creates a fresh in-memory database for each test
        Db::connect("sqlite::memory:").await.unwrap()
    }

    #[tokio::test]
    async fn create_and_list() {
        let db = test_db().await;

        let todo = db.create_todo(CreateTodo {
            title: "Test".into(),
        }).await.unwrap();
        assert_eq!(todo.title, "Test");
        assert!(!todo.completed);

        let todos = db.list_todos().await.unwrap();
        assert_eq!(todos.len(), 1);
    }

    #[tokio::test]
    async fn delete_nonexistent() {
        let db = test_db().await;
        let deleted = db.delete_todo(999).await.unwrap();
        assert!(!deleted);
    }
}

Tip

sqlite::memory: creates a database that lives only in RAM and is destroyed when the connection closes. Each test gets a completely isolated database - no cleanup needed, no test interference, very fast.

Exercise

Add a description column. Write a migration with sqlx migrate add, update the Todo struct, and make it optional in CreateTodo. Verify existing todos still work after the migration.
Add pagination to list_todos: accept limit and offset query parameters. Use SELECT ... LIMIT ? OFFSET ?.
Add a search endpoint: GET /api/todos?q=rust uses WHERE title LIKE ? with .bind(format!("%%", query)).
Switch from SQLite to Postgres. Change the feature flag to postgres, update the connection URL, and adjust any SQLite-specific SQL (AUTOINCREMENT → GENERATED ALWAYS AS IDENTITY). How much code actually changes?
Write a load test that creates 1,000 todos concurrently using tokio::spawn. Verify all 1,000 are created (no duplicates, no lost writes). Does the pool handle the concurrency correctly?

What's Next

Data persists across restarts. The database layer is clean: async queries, connection pooling, parameterized queries for safety, and migrations for schema evolution. Tests use in-memory SQLite for speed and isolation.

In the next chapter, we focus on security and hardening - input validation, rate limiting, TLS with rustls, security headers, and the vulnerabilities Rust helps prevent (and the ones it doesn't).

Chapter 21: Middleware Chapter 23: Security