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.

Insert a row with a bind parameter and read it straight back into a struct using RETURNING and query_as! .

📋 Quick Reference — sqlx

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.