SQL with sqlx
A database driver is a translator between the kitchen and the dining room. The cook plates the food in Rust structs; the patron orders in SQL strings; the driver carries plates one way and orders the other and refuses to confuse a steak for a salad. Most drivers do this at runtime — the cook hands the plate over and hopes the SQL string was spelled right, the patron eats whatever shows up. sqlx is the unusual driver that reads the menu at compile time, asks the kitchen if every order makes sense, and refuses to start service if a single dish is named wrong.
sqlx started in 2019 when Ryan Leckey wanted a Rust SQL library that did not force the choice between hand-rolled string concatenation and a heavyweight ORM that hides every query behind a method chain. The middle ground he wanted — write SQL as SQL, but get types and compile-time checking — already existed in F#'s SQL type providers and Scala's Slick library. sqlx took the same idea and added an async runtime so a Rust web server could open a pool of connections and answer thousands of queries without blocking a single thread. The crate now sits underneath axum and actix services that handle real production traffic.
The pieces a sqlx user touches show up in every program the same way — a row struct, a pool of connections, a query string, and a typed return.
/// The shape of one row in the users table.
/// Field names and types mirror the columns the SQL query selects.
#[derive(Debug)]
struct User {
id: i64,
name: String,
email: String,
}The User struct is the cook's plate. Field names match the column names the SELECT picks, in the same order. The shape is what sqlx checks against the database when it builds. Misspell email as emial and the compiler refuses the file, with a message that names the column it could not find. The cabinet of mistakes that only show up at runtime — column drift, type mismatches, typoed names — moves to the compile log instead.
A pool is a tray of pre-warmed connections the driver hands out as queries arrive. The lesson keeps a stand-in instead of a real connection because the binary has to print the same bytes on every machine, and a real database file would drift.
/// A stand-in for a real sqlx connection pool.
/// The lesson binary cannot open a real SQLite file, so we keep the rows
/// in memory and pattern-match on the SQL text. Every other piece of the
/// API -- the query string, the typed return, the Vec of structs -- is
/// shaped exactly the way sqlx shapes it.
struct FakePool {
rows: Vec<User>,
}
impl FakePool {
fn connect() -> Self {
Self {
rows: vec![
User { id: 1, name: "ada".into(), email: "ada@lovelace.org".into() },
User { id: 2, name: "linus".into(), email: "linus@kernel.org".into() },
User { id: 3, name: "grace".into(), email: "grace@navy.mil".into() },
],
}
}
/// Runs a SQL string against the in-memory table and returns matching rows.
/// Real sqlx parses the SQL once at compile time and asks the database
/// to plan it; we cheat by matching on three known query shapes.
fn execute(&self, sql: &str) -> Vec<&User> {
let trimmed = sql.trim();
if trimmed == "SELECT id, name, email FROM users" {
return self.rows.iter().collect();
}
if trimmed == "SELECT id, name, email FROM users WHERE id = 2" {
return self.rows.iter().filter(|u| u.id == 2).collect();
}
if trimmed == "SELECT id, name, email FROM users WHERE name LIKE 'a%'" {
return self
.rows
.iter()
.filter(|u| u.name.starts_with('a'))
.collect();
}
Vec::new()
}
}The FakePool matches on three known query shapes and returns the matching rows. Every other piece of the shape — the SQL string going in, the typed Vec<User> coming out — is exactly what sqlx hands back from a real Postgres or SQLite call. The lesson runs the three queries and prints what came back.
fn main() {
let pool = FakePool::connect();
let select_all = "SELECT id, name, email FROM users";
println!("query: {select_all}");
println!("row shape: User {{ id, name, email }}");
println!("rows returned:");
for user in pool.execute(select_all) {
println!(" {user:?}");
}
println!();
let by_id = "SELECT id, name, email FROM users WHERE id = 2";
println!("query: {by_id}");
println!("rows returned:");
for user in pool.execute(by_id) {
println!(" {user:?}");
}
println!();
let like_a = "SELECT id, name, email FROM users WHERE name LIKE 'a%'";
println!("query: {like_a}");
println!("rows returned:");
for user in pool.execute(like_a) {
println!(" {user:?}");
}
}query: SELECT id, name, email FROM users
row shape: User { id, name, email }
rows returned:
User { id: 1, name: "ada", email: "ada@lovelace.org" }
User { id: 2, name: "linus", email: "linus@kernel.org" }
User { id: 3, name: "grace", email: "grace@navy.mil" }
query: SELECT id, name, email FROM users WHERE id = 2
rows returned:
User { id: 2, name: "linus", email: "linus@kernel.org" }
query: SELECT id, name, email FROM users WHERE name LIKE 'a%'
rows returned:
User { id: 1, name: "ada", email: "ada@lovelace.org" }The first query is the full table scan and prints all three rows. The second pulls one row by primary key — the index path. The third uses LIKE 'a%' to filter on a prefix, the kind of question a hash-map key-value store cannot answer and a B-tree index handles in a single seek.
The piece sqlx actually adds is the compile-time check. Real code looks like this, gated with #[cfg(any())] so the lesson binary does not need a live database to build.
// What the real sqlx code looks like, gated with #[cfg(any())] so it never
// compiles inside this lesson binary -- adding sqlx as a dependency would
// require a live database at compile time for its query! macro, which would
// break the workspace build. The reader sees the actual API here.
#[cfg(any())]
mod real {
use sqlx::{sqlite::SqlitePoolOptions, FromRow};
#[derive(Debug, FromRow)]
struct User {
id: i64,
name: String,
email: String,
}
#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
let pool = SqlitePoolOptions::new()
.max_connections(5)
.connect("sqlite://app.db")
.await?;
// The query! macro reads the SQL string at compile time, asks the
// database to plan it, and refuses to build if the column types do
// not match the struct. A typo in a column name fails the build.
let users = sqlx::query_as!(User, "SELECT id, name, email FROM users")
.fetch_all(&pool)
.await?;
for user in &users {
println!("{user:?}");
}
Ok(())
}
}The query_as! macro is the part that surprises people. It opens a connection to the database at compile time, sends the SQL string for planning, reads back the column types and names, and checks them against the struct fields. A typo in name or a type mismatch on id fails cargo build. The price is that DATABASE_URL has to point at a live database the build can reach — usually a local Postgres or a SQLite file checked into the repo. The payoff is that the class of bugs where the schema changed and a query started returning the wrong shape disappears entirely.
Transactions in sqlx wrap a batch of statements so the whole batch either commits or rolls back as one.
// A transaction in sqlx: every statement inside the block either all
// commits together or all rolls back together. Gated for the same reason
// as the block above.
#[cfg(any())]
mod tx_demo {
async fn transfer(pool: &sqlx::SqlitePool, from: i64, to: i64, cents: i64)
-> Result<(), sqlx::Error>
{
let mut tx = pool.begin().await?;
sqlx::query!("UPDATE accounts SET balance = balance - ? WHERE id = ?", cents, from)
.execute(&mut *tx)
.await?;
sqlx::query!("UPDATE accounts SET balance = balance + ? WHERE id = ?", cents, to)
.execute(&mut *tx)
.await?;
tx.commit().await?;
Ok(())
}
}pool.begin() hands back a transaction handle. Every statement that runs against &mut *tx is part of the same atomic batch. tx.commit() writes the whole thing. If the function returns an error before the commit, the handle drops and the transaction rolls back. The shape is the bank-transfer guarantee from the database lesson — debit one account, credit another, never one without the other — turned into ordinary Rust control flow.
The thing sqlx still cannot do is store the database itself. It is a driver, not a server. Standing up Postgres, picking a replication strategy, deciding which AZ runs the primary — none of that is sqlx's problem. The next lesson trades the driver for an embedded key-value store that lives inside the binary, no server to run at all.