How to run a query and read the rows
Short answer: sqlx::query("SELECT ...").fetch_all(&pool).await sends the SQL to Postgres and
gives back every row; pull a column out of a row with row.get("name"), naming the Rust type you
expect. Postgres checks the SQL at run time — the compiler checks only the Rust around it.
A table to read
Section titled “A table to read”The previous page ends with a pool connected to
a fresh database — one that has no tables in it. A query needs something to return, so the first
step is a small table with three rows. psql -c runs the statements in order and prints one line
per statement:
PGPASSWORD=dev psql -h 127.0.0.1 -U postgres -d postgres -c "CREATE TABLE IF NOT EXISTS dishes ( id INT PRIMARY KEY, name TEXT NOT NULL, price_cents INT NOT NULL);INSERT INTO dishes (id, name, price_cents) VALUES (1, 'edamame', 350), (2, 'onigiri', 480), (3, 'ramen', 1450)ON CONFLICT (id) DO NOTHING;"CREATE TABLEINSERT 0 3The command is safe to run a second time — IF NOT EXISTS skips the table and
ON CONFLICT (id) DO NOTHING skips the inserts:
NOTICE: relation "dishes" already exists, skippingCREATE TABLEINSERT 0 0The connection here goes to the same server the URL on this page points at: user postgres,
password dev, database postgres. If Postgres runs in the container from the previous page and
the host has no psql, the same two statements work through
docker exec hwr-pg psql -U postgres -c "...".
The query
Section titled “The query”The dependencies are the same as on the previous page — sqlx with the postgres feature, and tokio
to run the futures:
[dependencies]sqlx = { version = "0.9", features = ["runtime-tokio", "tls-rustls", "postgres"] }tokio = { version = "1", features = ["macros", "rt-multi-thread"] }A query is three lines, plus the pool from the previous page:
use sqlx::postgres::PgPoolOptions;use sqlx::Row;
#[tokio::main]async fn main() { let pool = PgPoolOptions::new() .connect("postgres://postgres:dev@localhost:5432/postgres") .await .expect("failed to connect to Postgres");
let rows = sqlx::query("SELECT id, name, price_cents FROM dishes ORDER BY id") .fetch_all(&pool) .await .expect("query failed");
println!("{} rows", rows.len());
for row in rows { let id: i32 = row.get("id"); let name: String = row.get("name"); let price_cents: i32 = row.get("price_cents"); println!("{id} {name} {price_cents}"); }}3 rows1 edamame 3502 onigiri 4803 ramen 1450Nothing has been sent when sqlx::query(...) returns — it builds a query object, and the SQL is a
plain string as far as Rust is concerned. .fetch_all(&pool) is where the work happens: it borrows
a connection from the pool, sends the SQL, waits for every row to arrive, and returns a
Vec<PgRow>. The .expect on the end is not decoration: a query fails at run time the way a
network call does — server down, table missing, SQL that does not parse — and fetch_all hands
back a Result.
Reading a column is row.get("id"), with the Rust type coming from the let binding: i32 for
Postgres INT, String for TEXT. The type is the contract here — sqlx converts the wire value
into whatever you name, and if the column holds something else, that mismatch panics at run time.
use sqlx::Row; is easy to forget, and the error it produces names a struct, not the missing
import:
error[E0599]: no method named `get` found for struct `PgRow` in the current scope --> src/main.rs:16:27 |16 | let id: i32 = row.get("id"); | ^^^ method not found in `PgRow` |help: trait `Row` which provides `get` is implemented but not in scope; perhaps you want to import it | 1 + use sqlx::Row; |
For more information about this error, try `rustc --explain E0599`.(The full diagnostic also points at the get definition inside sqlx-core; the help line is the
part that matters.) get is a trait method, and trait methods only exist when the trait is in
scope — the same rule that makes fmt::Write or Read importable separately from their structs.
Mapping into a struct
Section titled “Mapping into a struct”fetch_all gives rows, not structs, and rows keep being a stringly-typed lookup even after the
values are out. The mapping into a type you can pass around is written by hand: one get per
column, one field per get.
use sqlx::postgres::PgPoolOptions;use sqlx::Row;
#[derive(Debug)]struct Dish { id: i32, name: String, price_cents: i32,}
#[tokio::main]async fn main() { let pool = PgPoolOptions::new() .connect("postgres://postgres:dev@localhost:5432/postgres") .await .expect("failed to connect to Postgres");
let rows = sqlx::query("SELECT id, name, price_cents FROM dishes ORDER BY id") .fetch_all(&pool) .await .expect("query failed");
let dishes: Vec<Dish> = rows .iter() .map(|row| Dish { id: row.get("id"), name: row.get("name"), price_cents: row.get("price_cents"), }) .collect();
for dish in &dishes { println!("{dish:?}"); }}Dish { id: 1, name: "edamame", price_cents: 350 }Dish { id: 2, name: "onigiri", price_cents: 480 }Dish { id: 3, name: "ramen", price_cents: 1450 }The compiler checks the struct side of this — the fields, the types, the Debug derive — but it
has no idea the database exists. Change the SQL to stop selecting a column, and the failure moves
to run time, on the first get that names something the query did not return:
use sqlx::postgres::PgPoolOptions;use sqlx::Row;
#[tokio::main]async fn main() { let pool = PgPoolOptions::new() .connect("postgres://postgres:dev@localhost:5432/postgres") .await .expect("failed to connect to Postgres");
let row = sqlx::query("SELECT id FROM dishes WHERE id = 1") .fetch_one(&pool) .await .expect("query failed");
let name: String = row.get("name"); println!("{name}");}thread 'main' (10710371) panicked at src/main.rs:16:28:called `Result::unwrap()` on an `Err` value: ColumnNotFound("name")note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace(The thread id and the line number are yours; the message is the point.) The name in
ColumnNotFound("name") is the column you asked for — which can make it read backwards at first,
because the query selected id and returned it fine. get is try_get plus an unwrap:
row.try_get("name") returns a Result<T, sqlx::Error>, for the cases where a missing column is
data to handle rather than a bug to panic on.
Writing one get per field is tedious, and sqlx can generate it — a derive on the struct plus
query_as. That is its own page later in this chapter; the hand-written loop is what the derive
does under the hood, and it is worth doing once.
fetch_one, fetch_optional, and the row that is not there
Section titled “fetch_one, fetch_optional, and the row that is not there”fetch_all is the right shape for a list. For a single row there are two more methods, and the
difference between them is what happens when the answer is empty:
use sqlx::postgres::PgPoolOptions;use sqlx::Row;
#[tokio::main]async fn main() { let pool = PgPoolOptions::new() .connect("postgres://postgres:dev@localhost:5432/postgres") .await .expect("failed to connect to Postgres");
let row = sqlx::query("SELECT name FROM dishes ORDER BY id") .fetch_one(&pool) .await; match row { Ok(row) => println!("three rows matched, fetch_one gave: {}", row.get::<String, _>("name")), Err(e) => println!("three rows matched, error: {e}"), }
let found = sqlx::query("SELECT name FROM dishes WHERE id = 2") .fetch_optional(&pool) .await .expect("query failed"); match found { Some(row) => println!("id 2: {}", row.get::<String, _>("name")), None => println!("id 2: nothing came back"), }
let missing = sqlx::query("SELECT name FROM dishes WHERE id = 999") .fetch_optional(&pool) .await .expect("query failed"); match missing { Some(row) => println!("id 999: {}", row.get::<String, _>("name")), None => println!("id 999: nothing came back"), }}three rows matched, fetch_one gave: edamameid 2: onigiriid 999: nothing came backThe first query matched three rows, and fetch_one did not complain — it returned the first one.
fetch_one promises the row, but it only checks one direction: zero rows is an Err, more than
one is not an error, just the first row. If “there were two” is a bug in your data, fetch_one
will not catch it.
fetch_optional returns an Option<PgRow> instead: Some with the first row, None when the
query matched nothing. That is the right method whenever an empty result is a normal answer — a
lookup by something the user typed, an id that may have been deleted.
fetch_one on a query that matches nothing dies here, at the .expect:
use sqlx::postgres::PgPoolOptions;
#[tokio::main]async fn main() { let pool = PgPoolOptions::new() .connect("postgres://postgres:dev@localhost:5432/postgres") .await .expect("failed to connect to Postgres");
sqlx::query("SELECT name FROM dishes WHERE id = 999") .fetch_one(&pool) .await .expect("query failed");}thread 'main' (10704016) panicked at src/main.rs:13:10:query failed: RowNotFoundnote: run with `RUST_BACKTRACE=1` environment variable to display a backtraceAgain, the thread id and the line number are yours. RowNotFound is the error fetch_one returns
when the result is empty — the .expect turns it into a panic, which is what you want in a
program where that id is guaranteed to exist and what you want to avoid everywhere else.
Reading rows is now the whole loop: a query in, structs out, and a choice among fetch_all,
fetch_one, and fetch_optional based on how many rows the answer deserves. What is still
implicit is the schema itself — the CREATE TABLE at the top of this page ran by hand, and the
next page is about keeping it in a file that travels with the code.