I've always found the database layer in Rust to be a bit of a crossroads. You either want a heavyweight ORM that manages your types for you, or you want to write raw SQL but still feel that "if it compiles, it works" safety we love in Rust. For this session, I want to show you how I handle this. We're going to build a simple inventory tracker for a rare book shop—something with a few columns like title, author, and price.

The First Connection Attempt

I'll start with sqlx. I prefer it these days because it's async and it doesn't force me to learn a proprietary query language; it just uses SQL. First, I'll throw sqlx and tokio into my Cargo.toml with the sqlite and runtime-tokio-rustls features.

#[tokio::main]
async fn main() -> Result <(), sqlx::Error> {
    let pool = sqlx::SqlitePool::connect("sqlite:books.db").await?;
    println!("Connected!");
    Ok(())
}

I run this, and... it works. But it's boring. The real magic of sqlx isn't just connecting; it's the compile-time verification. I want the compiler to tell me if my SQL is wrong before I even run the binary. To do that, I need a .env file with a DATABASE_URL, and I need to actually have the database file existing on my disk.

Where did the table go?

Now I'll try to fetch a book. I'm going to use the query! macro. This is where things usually get interesting. I'll write this:

let book = sqlx::query!("SELECT title, author FROM books WHERE id = ?", 1)
    .fetch_one(&pool)
    .await?;

I hit cargo build and immediately get a wall of red text. The compiler is complaining that it can't find the books table. This is a "failure" that I actually love. Most languages wouldn't tell me this until the app crashed in production. sqlx is literally reaching out to my local development database during compilation to check if that column author actually exists.

I'll jump into my terminal, run a quick CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author TEXT, price REAL);, and try building again. Now it compiles. The macro has mapped the SQLite TEXT type directly to a Rust String. No manual mapping, no boilerplate structs for the initial fetch.

The Friction of Type Mapping

But wait. What happens when I want to put this data into a struct? I can't just use query! forever if I'm passing this data around my app. I'll try creating a Book struct and using query_as!.

struct Book {
    title: String,
    author: String,
}

let book = sqlx::query_as!(Book, "SELECT title, author FROM books WHERE id = ?", 1)
    .fetch_one(&pool)
    .await?;

This works, but here is a quirk I've run into a dozen times: SQLite columns can be nullable. If my table definition allows NULL for author, sqlx will insist that my struct field be Option<String>. If I try to use a plain String, the compiler will scream at me. I have two choices: change my database schema to NOT NULL or wrap my Rust fields in Option. I'll go with NOT NULL because a book without an author is just a mystery I'm not in the mood to solve today.

Choosing Between sqlx and Diesel

You might be wondering, "Why not Diesel?" I'll be honest: Diesel is a beast. It's an actual ORM. If you're coming from ActiveRecord in Ruby or Hibernate in Java, Diesel will feel more familiar. It generates a huge amount of code based on your schema, and it's incredibly fast because it's mostly synchronous (though async support is evolving).

The trade-off is the learning curve. With sqlx, if you know SQL, you're 90% there. With Diesel, you have to learn "the Diesel way" of writing queries in Rust syntax. If you have a massive project with hundreds of tables and you want a strict, type-safe DSL to manage them, go with Diesel. But for most of my projects, I prefer the transparency of raw SQL checked at compile time. It keeps the "magic" to a minimum.

📋 Practical Task

Build a Persistent Rare Book Catalog

Your task is to extend the book shop example into a working CLI tool. You will need to set up a local SQLite database and implement the following functionality using sqlx:

• The Schema: Create a table named books with columns for id (primary key), title (text, not null), author (text, not null), and price (real, not null).
• The Insertion: Write a function add_book(pool: &SqlitePool, title: &str, author: &str, price: f64) that inserts a new record into the database.
• The Retrieval: Write a function get_expensive_books(pool: &SqlitePool, min_price: f64) that returns a Vec of books that cost more than the specified price.
• The Compile Check: Ensure you are using the query! or query_as! macros so that the project fails to compile if you intentionally misspell a column name in your SQL strings.

Test your implementation by adding three books and printing only those that cost over $50.00.