Rust Tutorial #23: Database with SQLx

In the previous tutorial, we built a REST API with Axum using in-memory storage. Now we add a real database with SQLx.

SQLx is an async database library for Rust. It supports PostgreSQL, MySQL, and SQLite. Unlike ORMs, SQLx lets you write plain SQL while still being type-safe. It can even check your queries at compile time against a real database.

In this tutorial, we use SQLite because it needs no server setup. Everything you learn applies to PostgreSQL and MySQL too — just change the connection string and SQL dialect.

Setting Up

Add SQLx to your Cargo.toml:

[dependencies]
tokio = { version = "1", features = ["full"] }
sqlx = { version = "0.8", features = ["runtime-tokio", "sqlite"] }
serde = { version = "1", features = ["derive"] }
serde_json = "1"

The features:

runtime-tokio — use Tokio as the async runtime
sqlite — enable SQLite support

For PostgreSQL, use "postgres" instead of "sqlite".

Important: If you use SQLx’s compile-time checked macros (query!, query_as!), you must set the DATABASE_URL environment variable. This is the number one reason cargo build fails for SQLx beginners. Create a .env file in your project root:

# .env
DATABASE_URL=sqlite:app.db

For this tutorial, we use the runtime-checked functions (query, query_as without !), so DATABASE_URL is not required until later.

Data Models

Define your Rust structs with #[derive(FromRow)] to map database rows to structs:

use serde::{Deserialize, Serialize};
use sqlx::FromRow;

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

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

FromRow tells SQLx how to convert a database row into your struct. Column names must match field names.

Connection Pool

Always use a connection pool in production. It reuses connections instead of creating new ones for each query:

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

async fn create_pool(database_url: &str) -> Result<SqlitePool, sqlx::Error> {
    let pool = SqlitePoolOptions::new()
        .max_connections(5)
        .connect(database_url)
        .await?;
    Ok(pool)
}

Connection strings for SQLite:

"sqlite::memory:" — in-memory database (lost when program exits)
"sqlite:app.db" — file-based database
"sqlite:app.db?mode=rwc" — create the file if it does not exist

Creating Tables

Run migrations to create your tables:

async fn run_migrations(pool: &SqlitePool) -> Result<(), sqlx::Error> {
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS todos (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            completed BOOLEAN NOT NULL DEFAULT FALSE
        )",
    )
    .execute(pool)
    .await?;
    Ok(())
}

sqlx::query() creates a query. .execute(pool) runs it and returns the number of affected rows. For table creation, we do not need the result.

Using SQLx Migrations

For real projects, use SQLx’s migration system:

# Install the sqlx CLI
cargo install sqlx-cli

# Create a migration file
sqlx migrate add create_todos

# This creates migrations/YYYYMMDDHHMMSS_create_todos.sql
# Write your SQL there, then run:
sqlx migrate run --database-url sqlite:app.db

In your code, run migrations at startup:

sqlx::migrate!("./migrations")
    .run(&pool)
    .await?;

Insert — Creating Records

async fn insert_todo(pool: &SqlitePool, title: &str) -> Result<Todo, sqlx::Error> {
    let result = sqlx::query("INSERT INTO todos (title, completed) VALUES (?, FALSE)")
        .bind(title)
        .execute(pool)
        .await?;

    let id = result.last_insert_rowid();

    let todo = sqlx::query_as::<_, Todo>(
        "SELECT id, title, completed FROM todos WHERE id = ?"
    )
    .bind(id)
    .fetch_one(pool)
    .await?;

    Ok(todo)
}

Key points:

Use ? as parameter placeholders for SQLite and MySQL. PostgreSQL uses $1, $2, etc.
.bind(value) binds a value to the next ? placeholder
result.last_insert_rowid() gets the auto-generated ID
sqlx::query_as maps the result to your struct using FromRow

Parameter Binding

Always use .bind() for user input. Never build SQL by concatenating strings:

// BAD: SQL injection risk
let query = format!("SELECT * FROM todos WHERE title = '{}'", user_input);

// GOOD: parameterized query — safe from injection
sqlx::query("SELECT * FROM todos WHERE title = ?")
    .bind(user_input)
    .fetch_all(pool)
    .await?;

Select — Reading Records

Fetch One Record

async fn get_todo_by_id(pool: &SqlitePool, id: i64) -> Result<Option<Todo>, sqlx::Error> {
    let todo = sqlx::query_as::<_, Todo>(
        "SELECT id, title, completed FROM todos WHERE id = ?"
    )
    .bind(id)
    .fetch_optional(pool)
    .await?;
    Ok(todo)
}

Fetch methods:

fetch_one — returns exactly one row. Errors if zero or multiple rows.
fetch_optional — returns Option<T>. None if no rows match.
fetch_all — returns Vec<T> with all matching rows.

Fetch All Records

async fn get_all_todos(pool: &SqlitePool) -> Result<Vec<Todo>, sqlx::Error> {
    let todos = sqlx::query_as::<_, Todo>(
        "SELECT id, title, completed FROM todos ORDER BY id ASC"
    )
    .fetch_all(pool)
    .await?;
    Ok(todos)
}

Pagination

async fn get_todos_paginated(
    pool: &SqlitePool,
    limit: i64,
    offset: i64,
) -> Result<Vec<Todo>, sqlx::Error> {
    let todos = sqlx::query_as::<_, Todo>(
        "SELECT id, title, completed FROM todos ORDER BY id ASC LIMIT ? OFFSET ?"
    )
    .bind(limit)
    .bind(offset)
    .fetch_all(pool)
    .await?;
    Ok(todos)
}

Counting Records

use sqlx::Row;

async fn count_todos(pool: &SqlitePool) -> Result<i64, sqlx::Error> {
    let row = sqlx::query("SELECT COUNT(*) as count FROM todos")
        .fetch_one(pool)
        .await?;
    let count: i64 = row.get("count");
    Ok(count)
}

For simple values like counts, use sqlx::query (without _as) and access columns with row.get("column_name").

Search with LIKE

async fn search_todos(pool: &SqlitePool, query: &str) -> Result<Vec<Todo>, sqlx::Error> {
    let pattern = format!("%{}%", query);
    let todos = sqlx::query_as::<_, Todo>(
        "SELECT id, title, completed FROM todos WHERE title LIKE ? ORDER BY id ASC"
    )
    .bind(pattern)
    .fetch_all(pool)
    .await?;
    Ok(todos)
}

Build the LIKE pattern in Rust and bind it. The % wildcard matches any number of characters.

Update — Modifying Records

async fn update_todo_title(
    pool: &SqlitePool,
    id: i64,
    title: &str,
) -> Result<Option<Todo>, sqlx::Error> {
    let result = sqlx::query("UPDATE todos SET title = ? WHERE id = ?")
        .bind(title)
        .bind(id)
        .execute(pool)
        .await?;

    if result.rows_affected() == 0 {
        return Ok(None);  // Todo not found
    }

    get_todo_by_id(pool, id).await
}

Check rows_affected() to know if the update actually changed anything. If it is zero, the record does not exist.

Toggle Completed

async fn toggle_completed(
    pool: &SqlitePool,
    id: i64,
) -> Result<Option<Todo>, sqlx::Error> {
    let result = sqlx::query("UPDATE todos SET completed = NOT completed WHERE id = ?")
        .bind(id)
        .execute(pool)
        .await?;

    if result.rows_affected() == 0 {
        return Ok(None);
    }

    get_todo_by_id(pool, id).await
}

NOT completed flips the boolean in SQL. No need to read the current value first.

Delete — Removing Records

async fn delete_todo(pool: &SqlitePool, id: i64) -> Result<bool, sqlx::Error> {
    let result = sqlx::query("DELETE FROM todos WHERE id = ?")
        .bind(id)
        .execute(pool)
        .await?;
    Ok(result.rows_affected() > 0)
}

Returns true if a row was actually deleted, false if the ID did not exist.

Bulk Delete

async fn delete_completed(pool: &SqlitePool) -> Result<u64, sqlx::Error> {
    let result = sqlx::query("DELETE FROM todos WHERE completed = TRUE")
        .execute(pool)
        .await?;
    Ok(result.rows_affected())
}

Transactions

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

async fn create_multiple_todos(
    pool: &SqlitePool,
    titles: &[&str],
) -> Result<Vec<Todo>, sqlx::Error> {
    let mut tx = pool.begin().await?;
    let mut todos = vec![];

    for title in titles {
        let result = sqlx::query("INSERT INTO todos (title, completed) VALUES (?, FALSE)")
            .bind(title)
            .execute(&mut *tx)
            .await?;

        let id = result.last_insert_rowid();
        let todo = sqlx::query_as::<_, Todo>(
            "SELECT id, title, completed FROM todos WHERE id = ?"
        )
        .bind(id)
        .fetch_one(&mut *tx)
        .await?;
        todos.push(todo);
    }

    tx.commit().await?;
    Ok(todos)
}

Key points:

pool.begin() starts a transaction
Pass &mut *tx instead of pool to queries inside the transaction
tx.commit() saves all changes
If the function returns an error before commit, the transaction is rolled back automatically when tx is dropped

When to Use Transactions

Multiple inserts that must all succeed or all fail
Read-then-update operations (to prevent race conditions)
Moving data between tables atomically

query vs query_as

sqlx::query — Dynamic Results

Returns generic rows you access by column name:

let row = sqlx::query("SELECT COUNT(*) as total FROM todos")
    .fetch_one(pool)
    .await?;
let total: i64 = row.get("total");

sqlx::query_as — Typed Results

Maps results directly to a struct:

let todo = sqlx::query_as::<_, Todo>(
    "SELECT id, title, completed FROM todos WHERE id = ?"
)
.bind(id)
.fetch_one(pool)
.await?;

Use query_as when you have a struct with FromRow. Use query for simple values or ad-hoc queries.

Compile-Time Checked Queries

SQLx can check your SQL at compile time. This catches typos, wrong column names, and type mismatches before you run the program:

// Note the ! — this is a macro, not a function
let todo = sqlx::query_as!(
    Todo,
    "SELECT id, title, completed FROM todos WHERE id = ?",
    id
)
.fetch_one(pool)
.await?;

The query_as! macro checks the query against a real database at compile time. Set the DATABASE_URL environment variable:

export DATABASE_URL="sqlite:app.db"
cargo build

This is powerful but requires more setup. Start with the runtime-checked versions and switch to macros when your project matures.

Putting It All Together

Here is a complete program that demonstrates all CRUD operations:

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

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = SqlitePoolOptions::new()
        .max_connections(5)
        .connect("sqlite::memory:")
        .await?;

    run_migrations(&pool).await?;

    // Create
    let todo = insert_todo(&pool, "Learn SQLx").await?;
    println!("Created: {:?}", todo);

    // Read
    let all = get_all_todos(&pool).await?;
    println!("All todos: {:?}", all);

    // Update
    let updated = update_todo_title(&pool, todo.id, "Master SQLx").await?;
    println!("Updated: {:?}", updated);

    // Toggle
    let toggled = toggle_completed(&pool, todo.id).await?;
    println!("Toggled: {:?}", toggled);

    // Search
    let found = search_todos(&pool, "Master").await?;
    println!("Found: {:?}", found);

    // Count
    let count = count_todos(&pool).await?;
    println!("Total: {}", count);

    // Delete
    let deleted = delete_todo(&pool, todo.id).await?;
    println!("Deleted: {}", deleted);

    Ok(())
}

Testing with In-Memory Databases

SQLx with SQLite is easy to test. Create an in-memory database for each test:

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

    async fn setup_test_db() -> SqlitePool {
        let pool = create_pool("sqlite::memory:").await.unwrap();
        run_migrations(&pool).await.unwrap();
        pool
    }

    #[tokio::test]
    async fn test_create_and_get() {
        let pool = setup_test_db().await;

        let todo = insert_todo(&pool, "Test todo").await.unwrap();
        assert_eq!(todo.title, "Test todo");
        assert!(!todo.completed);

        let found = get_todo_by_id(&pool, todo.id).await.unwrap();
        assert_eq!(found, Some(todo));
    }

    #[tokio::test]
    async fn test_search() {
        let pool = setup_test_db().await;

        insert_todo(&pool, "Buy groceries").await.unwrap();
        insert_todo(&pool, "Write Rust code").await.unwrap();
        insert_todo(&pool, "Buy milk").await.unwrap();

        let results = search_todos(&pool, "Buy").await.unwrap();
        assert_eq!(results.len(), 2);
    }

    #[tokio::test]
    async fn test_transaction() {
        let pool = setup_test_db().await;

        let todos = create_multiple_todos(&pool, &["A", "B", "C"]).await.unwrap();
        assert_eq!(todos.len(), 3);

        let count = count_todos(&pool).await.unwrap();
        assert_eq!(count, 3);
    }

    #[tokio::test]
    async fn test_delete() {
        let pool = setup_test_db().await;

        let todo = insert_todo(&pool, "To delete").await.unwrap();
        assert!(delete_todo(&pool, todo.id).await.unwrap());
        assert!(!delete_todo(&pool, todo.id).await.unwrap());  // Already deleted
    }
}

Each test gets its own in-memory database. Tests run in parallel without interference.

Common Mistakes

Mistake 1: Not Using a Connection Pool

// BAD: creates a new pool for every query
async fn bad_query() {
    let pool = SqlitePool::connect("sqlite:app.db").await.unwrap();
    // This is wasteful
}

// GOOD: create pool once, pass it around
let pool = create_pool("sqlite:app.db").await?;
insert_todo(&pool, "My todo").await?;
get_all_todos(&pool).await?;

Mistake 2: String Concatenation in SQL

// BAD: SQL injection vulnerability
let query = format!("SELECT * FROM todos WHERE title = '{}'", user_input);

// GOOD: parameterized queries
sqlx::query("SELECT * FROM todos WHERE title = ?")
    .bind(user_input)
    .fetch_all(pool)
    .await?;

Mistake 3: Using fetch_one When Row Might Not Exist

// BAD: returns error if ID does not exist
let todo = sqlx::query_as::<_, Todo>("SELECT * FROM todos WHERE id = ?")
    .bind(id)
    .fetch_one(pool)
    .await?;

// GOOD: returns None if not found
let todo = sqlx::query_as::<_, Todo>("SELECT * FROM todos WHERE id = ?")
    .bind(id)
    .fetch_optional(pool)
    .await?;

Summary

Concept	Purpose
`SqlitePool`	Connection pool for SQLite
`sqlx::query()`	Run a SQL query (dynamic results)
`sqlx::query_as()`	Run a query and map to a struct
`.bind(value)`	Bind a parameter to `?` placeholder
`.execute(pool)`	Run query, return affected rows
`.fetch_one(pool)`	Get exactly one row
`.fetch_optional(pool)`	Get zero or one row
`.fetch_all(pool)`	Get all matching rows
`pool.begin()`	Start a transaction
`tx.commit()`	Commit a transaction
`#[derive(FromRow)]`	Map database rows to structs
`query_as!` macro	Compile-time checked queries

Source Code

Find the complete code on GitHub: tutorial-23-sqlx

What’s Next?

In this tutorial, we learned database operations with SQLx. We covered CRUD operations, connection pools, transactions, searching, and testing with in-memory databases.

In the next tutorial, we will build CLI tools with Clap — parsing arguments, subcommands, flags, and building a complete command-line application.

Next: Rust Tutorial #24: CLI Tools with Clap

Rust Tutorial #22: Web API with Axum – previous tutorial
Rust Tutorial #20: Serde and JSON – data serialization
Rust Tutorial Series – all tutorials

Setting Up#

Data Models#

Connection Pool#

Creating Tables#

Using SQLx Migrations#

Insert — Creating Records#

Parameter Binding#

Select — Reading Records#

Fetch One Record#

Fetch All Records#

Pagination#

Counting Records#

Search with LIKE#

Update — Modifying Records#

Toggle Completed#

Delete — Removing Records#

Bulk Delete#

Transactions#

When to Use Transactions#

query vs query_as#

sqlx::query — Dynamic Results#

sqlx::query_as — Typed Results#

Compile-Time Checked Queries#

Putting It All Together#

Testing with In-Memory Databases#

Common Mistakes#

Mistake 1: Not Using a Connection Pool#

Mistake 2: String Concatenation in SQL#

Mistake 3: Using fetch_one When Row Might Not Exist#

Summary#

Source Code#

What’s Next?#

Related Articles#