# Query
There are two sets of query APIs: Simple Query Protocol and Extended Query Protocol.
## Simple Query Protocol
Simple Protocol supports multiple statements (separated by `;`), but does not support parameter binding.
Use Extended Protocol if you need to send parameters or read typed binary results.
```rust,ignore
impl Conn {
fn query<H: SimpleHandler>(&mut self, sql: &str, handler: &mut H) -> Result<()>;
fn query_drop(&mut self, sql: &str) -> Result<Option<u64>>;
fn query_first<Row>(&mut self, sql: &str) -> Result<Option<Row>>;
fn query_collect<Row>(&mut self, sql: &str) -> Result<Vec<Row>>;
fn query_foreach<Row, F>(&mut self, sql: &str, f: F) -> Result<()>;
}
```
- `query`: execute SQL and process results with a handler
- `query_drop`: execute SQL and discard results, returning rows affected
- `query_first`: execute and return `Option<Row>` for the first row
- `query_collect`: execute and collect all rows into a Vec
- `query_foreach`: execute and call a closure for each row
### Example
```rust,ignore
// Execute and discard results
conn.query_drop("INSERT INTO users (name) VALUES ('Alice')")?;
// Collect all rows
let users: Vec<(i32, String)> = conn.query_collect("SELECT id, name FROM users")?;
// Get first row only
let user: Option<(i32, String)> = conn.query_first("SELECT id, name FROM users LIMIT 1")?;
// Process rows one by one
conn.query_foreach("SELECT id, name FROM users", |row: (i32, String)| {
println!("{}: {}", row.0, row.1);
})?;
```
## Extended Query Protocol
Extended Protocol uses a prepared statement and parameter binding. Use `$1`, `$2`, ..`$N` as placeholders.
Postgres does not support multiple statements in a prepared statement.
```rust,ignore
impl Conn {
fn prepare(&mut self, sql: &str) -> Result<PreparedStatement>;
fn prepare_typed(&mut self, sql: &str, param_oids: &[u32]) -> Result<PreparedStatement>;
fn prepare_batch(&mut self, queries: &[&str]) -> Result<Vec<PreparedStatement>>;
fn exec<S, P, H>(&mut self, statement: S, params: P, handler: &mut H) -> Result<()>;
fn exec_drop<S, P>(&mut self, statement: S, params: P) -> Result<Option<u64>>;
fn exec_first<Row, S, P>(&mut self, statement: S, params: P) -> Result<Option<Row>>;
fn exec_collect<Row, S, P>(&mut self, statement: S, params: P) -> Result<Vec<Row>>;
fn exec_foreach<Row, S, P, F>(&mut self, statement: S, params: P, f: F) -> Result<()>;
fn exec_batch<S, P>(&mut self, statement: S, params_list: &[P]) -> Result<()>;
fn exec_portal<S, P, F, T>(&mut self, statement: S, params: P, f: F) -> Result<T>;
fn close_statement(&mut self, stmt: &PreparedStatement) -> Result<()>;
}
impl Transaction<'_> {
fn exec_portal_named<S, P>(&self, conn: &mut Conn, statement: S, params: P) -> Result<NamedPortal<'_>>;
}
```
- `prepare`: prepare a statement for execution
- `prepare_typed`: prepare with explicit parameter OIDs
- `prepare_batch`: prepare multiple statements in one round-trip
- `exec`: execute with a custom handler
- `exec_drop`: execute and discard results, returning rows affected
- `exec_first`: execute and return `Option<Row>` for the first row
- `exec_collect`: execute and collect all rows into a Vec
- `exec_foreach`: execute and call a closure for each row
- `exec_batch`: execute with multiple parameter sets efficiently
- `exec_portal`: execute to create an unnamed portal that incrementally fetch rows
- `close_statement`: close a prepared statement
- `tx.exec_portal_named`: create a named portal within a transaction for incremental fetching
The `statement` parameter can be either:
- A `&PreparedStatement` returned from `prepare()`
- A raw SQL `&str` for one-shot execution (parsed once per call)
### Example: Basic
```rust,ignore
// Using prepared statement
let stmt = conn.prepare("SELECT * FROM users WHERE id = $1")?;
let user: Option<(i32, String)> = conn.exec_first(&stmt, (42,))?;
// Using raw SQL
let user: Option<(i32, String)> = conn.exec_first(
"SELECT * FROM users WHERE id = $1",
(42,)
)?;
// Process rows one by one
conn.exec_foreach(&stmt, (42,), |row: (i32, String)| {
println!("{}: {}", row.0, row.1);
Ok(())
})?;
```
### Example: Batch Execution
`exec_batch` sends all parameter sets without waiting for the response for the already-sent parameter sets:
```rust,ignore
let stmt = conn.prepare("INSERT INTO users (name, age) VALUES ($1, $2)")?;
conn.exec_batch(&stmt, &[
("Alice", 30),
("Bob", 25),
("Charlie", 35),
])?;
```
### Example: Portal-based Iteration
For large result sets, use `exec_portal` to fetch rows incrementally with a handler.
```rust,ignore
let stmt = conn.prepare("SELECT * FROM large_table")?;
conn.exec_portal(&stmt, (), |portal| {
let mut handler = RowCollector::new();
while portal.exec(100, &mut handler)? {
// process handler.rows and clear for next batch
}
Ok(())
})?;
```
You can also use `exec_foreach` on the portal to process rows with a closure:
```rust,ignore
conn.exec_portal(&stmt, (), |portal| {
while portal.exec_foreach(100, |row: (i32, String)| {
println!("{}: {}", row.0, row.1);
Ok(())
})? {
// continues until all rows processed
}
Ok(())
})?;
```
### Example: Interleaving Two Row Streams
Use `tx.exec_portal_named` to create named portals that can be interleaved within a transaction:
```rust,ignore
let stmt1 = conn.prepare("SELECT * FROM table1")?;
let stmt2 = conn.prepare("SELECT * FROM table2")?;
let mut portal2 = tx.exec_portal_named(conn, &stmt2, ())?;
loop {
let rows1: Vec<(i32,)> = portal1.exec_collect(conn, 100)?;
let rows2: Vec<(i32,)> = portal2.exec_collect(conn, 100)?;
process(rows1, rows2);
if portal1.is_complete() && portal2.is_complete() {
break;
}
}
portal1.close(conn)?;
portal2.close(conn)?;
tx.commit(conn)
})?;
```
Named portals also support `exec_foreach` for processing rows with a closure:
```rust,ignore
while !portal.is_complete() {
portal.exec_foreach(conn, 100, |row: (i32, String)| {
println!("{}: {}", row.0, row.1);
Ok(())
})?;
}
portal.close(conn)?;
tx.commit(conn)
})?;
```
## Parameters
A parameter set (`Params`) is a tuple of primitives.
```rust,ignore
exec_drop(sql, ()) // no parameter
exec_drop("SELECT $1, $2, $3", (1, 2.0, "String")) // bind 3 parameters
```
## Struct Mapping
There are two ways to map database rows to Rust structs.
### Using `#[derive(FromRow)]`
The `FromRow` derive macro automatically maps columns to struct fields by name.
```rust,ignore
use zero_postgres::r#macro::FromRow;
#[derive(FromRow)]
struct User {
id: i32,
name: String,
email: Option<String>,
}
let stmt = conn.prepare("SELECT name, id, email_address as email FROM users")?;
// Collect all rows
let users: Vec<User> = conn.exec_collect(&stmt, ())?;
// Get first row only
let user: Option<User> = conn.exec_first(&stmt, ())?;
// Process rows one by one
conn.exec_foreach(&stmt, (), |user: User| {
println!("{}: {}", user.id, user.name);
Ok(())
})?;
```
Use `#[from_row(strict)]` to error on unknown columns:
```rust,ignore
#[derive(FromRow)]
#[from_row(strict)]
struct User {
id: i32,
name: String,
}
// Returns Error if query because `email` column is unknown
let user: Option<User> = conn.exec_first("SELECT id, name, email FROM users");
```
### Manual Construction with `exec_foreach`
This has an advantage of reusing `Vec` and being slightly faster because it does not match the column names against struct field names.
```rust,ignore
struct User {
id: i32,
name: String,
display_name: String, // computed field
}
let stmt = conn.prepare("SELECT id, name FROM users")?;
let mut users = Vec::new();
conn.exec_foreach(&stmt, (), |row: (i32, String)| {
users.push(User {
id: row.0,
display_name: format!("User: {}", row.1),
name: row.1,
});
Ok(())
})?;
```