liveBook · Manning

SQLx

As explained in Rust Web Development

SQLx is a Rust library for asynchronous interaction with SQL databases, with compile-time checked queries and support for multiple database engines.

SQLx is an asynchronous Rust library for working with SQL databases. It lets an application write SQL directly rather than relying on an ORM to translate queries. It supports PostgreSQL, MySQL, and SQLite, along with multiple async runtimes. section 7.3 section 7.8

SQLx can check queries at compile time through macros that connect to a development database. This check is a tradeoff: a database connection during compilation may not suit every workflow. The sqlx::query example below uses a function rather than a compile-time checking macro. section 7.3 section 7.4.1

For a PostgreSQL web service, add SQLx to Cargo.toml with features for the runtime, migrations, and PostgreSQL. Create the questions and answers tables before querying them; migrations can later manage table changes. section 7.3.1 section 7.8

Listing 7.7 Adding the SQLx dependencies to our Cargo.toml
[package]
name = "practical-rust-book"
version = "0.1.0"
edition = "2021"
 
[dependencies]
...
// Formatted for print purposes; this has to be on one line
// or it won't compile
sqlx = { 
    version = "0.5", 
    features = [ "runtime-tokio-rustls", "migrate", "postgres" ] 
}                                                                #1
#1 We add tokio, migrate, and postgres features to the crate, so we can run on top of our runtime and later run migrations within the codebase.

The dependency configuration selects SQLx 0.5, the Tokio runtime, migrations, and PostgreSQL. section 7.3.1

Fetch questions with pagination

A store can hold a PostgreSQL connection pool and expose an async get_questions method. The query uses $1 and $2 placeholders; bind supplies the limit and offset. Map each PgRow into a Question, then call fetch_all with the pool and await the result. Return either the collected questions or sqlx::Error. section 7.4.1

pub async fn get_questions(
    &self, 
    limit: Option<i32>, 
    offset: i32
) -> Result<Vec<Question>, sqlx::Error> {
        match sqlx::query("SELECT * from questions LIMIT $1 OFFSET $2")
            .bind(limit)
            .bind(offset)
            .map(|row: PgRow| Question {
                id: QuestionId(row.get("id")),
                title: row.get("title"),
                content: row.get("content"),
                tags: row.get("tags"),
                })
            .fetch_all(&self.connection)
            .await {
                Ok(questions) => Ok(questions),
                Err(e) => Err(e),
            }
}

This implementation combines bound pagination values, row mapping, and asynchronous execution. section 7.4.1

Use fetch, fetch_one, or fetch_all when a query returns data, and execute when it does not. The application also needs to handle SQLx errors and trace database interactions. section 7.8 section 7.4.1

Keep database operations behind store functions so route handlers need not change when the database changes. Switching from PostgreSQL to SQLite still requires a new SQLx feature, pool and row types, and database URL. SQL and models may also need changes: SQLite does not support the PostgreSQL array used for question tags. section 7.7

How Rust Web Development covers it

How deep it goes
Walks through dependency setup, pooled queries, row mapping, error handling, and a database switch without serving as an exhaustive reference.
Examples
Uses a questions-and-answers web service to demonstrate paginated retrieval and a PostgreSQL-to-SQLite switch.
What you can do after
Apply it via SQLx to configure a database connection, execute and map queries, and adapt a store to another supported database.
How it connects
Connects SQLx to asynchronous runtimes, connection pools, pagination, migrations, and ORM tradeoffs.

Also explained in

Compare how 3 books explain SQLx →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Rust Web Development. The section links point to the parts of the book it draws on.