Database Access with sqlx
sqlx is an async, pure-Rust SQL toolkit whose standout feature is compile-time checked queries — your SQL is verified against a real database before your program ever runs.
Learn Database Access with sqlx in our free Rust course — a beginner-friendly interactive lesson with worked examples, a practice exercise and a quick…
Part of the free Rust course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.
In this lesson you'll create a connection pool, run checked queries, map rows into structs with FromRow, group work in transactions, and apply migrations.
What You'll Learn in This Lesson
1️⃣ A Pool and a Checked Query
Everything starts with a PgPool — a set of reusable connections shared across your app. The sqlx::query! macro verifies your SQL against the real database at compile time, and $1 bind parameters keep user input out of the SQL text.
Because values travel as bind parameters, not string concatenation, sqlx is injection-safe by construction — there is no string to escape.
2️⃣ Mapping Rows to Structs
Derive FromRow on a struct and query_as! will produce rows as that type — checked at compile time. fetch_one returns exactly one row, fetch_all a Vec , and fetch_optional an Option .
Rename a column without updating User and you get a clear compile error — the mapping is type-checked, not hopeful.
3️⃣ Transactions and Migrations
A transaction makes several statements atomic. pool.begin() starts one; commit() makes it permanent, and dropping it without committing rolls back. Migrations are versioned SQL files applied with sqlx::migrate! .
If either UPDATE fails, the whole transfer is undone — you never lose money to a half-applied transfer.
Mini-Challenge: Insert and Return
Insert a row with a bind parameter and read it straight back into a struct using RETURNING and query_as! .
Common Errors (and the fix)
- "set DATABASE_URL to use query macros" — The query! macros need a database at build time, or run cargo sqlx prepare for offline mode.
- "no column found for name" — Your struct fields must match the selected column names and types.
- fetch_one on zero rows — fetch_one errors if no row; use fetch_optional for zero-or-one.
- Forgetting to commit — A transaction dropped without commit() rolls back; call it explicitly.
Pro Tips
- 💡 Build one pool at startup: store it in your Axum State and clone it per request.
- 💡 Use cargo sqlx prepare: cache query metadata so CI builds without a live database.
- 💡 Always use bind parameters: $1 , never string formatting, keeps you injection-safe.
📋 Quick Reference — sqlx
Goal
Rust Syntax
Create pool
PgPoolOptions::new().connect(url)
Checked query
sqlx::query!("... $1", v)
Into struct
sqlx::query_as!(User, "...")
One row
.fetch_one(&pool).await
Transaction
let mut tx = pool.begin().await?;
Migrate
sqlx::migrate!().run(&pool)
Frequently Asked Questions
🎉 Lesson Complete!
- ✅ A PgPool shares reusable connections across your app
- ✅ query! and query_as! check SQL at compile time
- ✅ FromRow maps rows into typed structs
- ✅ Transactions and sqlx::migrate! keep your data consistent
- ✅ Next lesson: FFI — calling C from Rust and Rust from C
Practice quiz
What is special about the sqlx::query! macro?
- It checks your SQL against the real database at compile time
- It avoids using SQL
- It only works with SQLite
- It runs queries faster
Answer: It checks your SQL against the real database at compile time. sqlx::query! connects to your database at compile time and verifies the SQL and types.
What is a PgPool?
- A single connection
- A thread pool
- A pool of reusable Postgres connections
- A query cache
Answer: A pool of reusable Postgres connections. PgPool manages a pool of database connections that handlers can borrow and return.
Which trait lets a struct be built from a database row?
- Future
- FromRow
- Deserialize
- IntoResponse
Answer: FromRow. Deriving FromRow lets sqlx map column values into a struct's fields.
How does sqlx prevent SQL injection?
- It escapes strings manually
- It blocks the word DROP
- It hashes inputs
- It sends values as bound parameters, never string concatenation
Answer: It sends values as bound parameters, never string concatenation. Values are passed as bind parameters, so user input is never interpolated into SQL text.
Which macro maps a query directly into a typed struct at compile time?
- query_as!
- query_map!
- exec!
- select!
Answer: query_as!. query_as! checks the SQL and produces rows as your named struct type.
What does .fetch_one(&pool).await return?
- A Vec of all rows
- Exactly one row, erroring if zero or many
- A connection
- A stream
Answer: Exactly one row, erroring if zero or many. fetch_one returns a single row and errors if the query returns zero or more than one.
How are sqlx migrations usually applied at startup?
- They run automatically with no code
- Through the query! macro
- By hand in psql only
- With sqlx::migrate!().run(&pool)
Answer: With sqlx::migrate!().run(&pool). The sqlx::migrate! macro embeds the migrations folder and run() applies pending ones.
Why does query! need a database available at build time?
- To generate random data
- To run the program
- To verify the SQL and infer column types
- To download the driver
Answer: To verify the SQL and infer column types. Compile-time checking connects to the DB (or uses cached metadata) to validate the query.
How do you run several statements atomically in sqlx?
- Run them on separate threads
- Begin a transaction, then commit or rollback
- Use a second pool
- Wrap them in a Mutex
Answer: Begin a transaction, then commit or rollback. pool.begin() starts a transaction; commit() applies it, drop/rollback() discards it.
Which placeholder does sqlx use for bind parameters with Postgres?
- $1, $2, ...
- ? marks
- @name
- %s
Answer: $1, $2, .... Postgres uses numbered placeholders like $1 and $2 for bound parameters.
Continue this course
- Previous: Web Framework with Axum
- Next: FFI & C Interop