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


