Connecting to PostgreSQL from Rust
You're building a CLI tool that tracks daily habits. The data needs to survive a reboot. You've got PostgreSQL running locally, humming along. Now you need Rust to talk to it. You could write raw TCP sockets and parse the wire protocol yourself. That's a fun weekend project that turns into a nightmare. Instead, you reach for the postgres crate. It handles the handshake, the encryption, the query parsing, and the result decoding. You focus on the logic.
The client as a translator
A database client is a translator. Rust speaks Rust. PostgreSQL speaks a binary wire protocol over TCP. The postgres crate sits between them. You hand it a SQL string and some parameters. It packages them up, sends them over the network, waits for the response, and turns the binary result back into Rust types you can use.
The crate is synchronous. It blocks the current thread while waiting for the database. That's usually fine for simple scripts, CLIs, or when you're behind a runtime that handles concurrency for you. The client manages a single TCP connection. Reusing the client across multiple queries avoids the overhead of reconnecting.
The client is a translator. Trust it to handle the wire protocol so you don't have to.
Minimal example
Add the crate to your dependencies. The version in this example is 0.17.3. Check the registry for updates, but the API remains stable.
[dependencies]
postgres = "0.17.3"
Write a main function that connects, queries, and prints.
use postgres::{Client, NoTls};
/// Runs a simple query to fetch users over 18.
fn main() -> Result<(), Box<dyn std::error::Error>> {
// Connect to the database.
// NoTls disables encryption. Use this only for local development.
let mut client = Client::connect("host=localhost user=postgres", NoTls)?;
// Query with a parameter placeholder $1.
// &[&18] passes the value 18. The double reference satisfies the trait bound.
let rows = client.query("SELECT name, age FROM users WHERE age > $1", &[&18])?;
for row in rows {
// Extract columns by index.
// The type parameter _ infers the source type (Row).
let name: String = row.get(0);
let age: i32 = row.get(1);
println!("{name} is {age} years old");
}
Ok(())
}
Run this against a local database and watch the rows fly by.
How the query works
When Client::connect runs, the crate opens a TCP socket to the host. It negotiates the version, sends credentials, and waits for the server to say "yes". If the server says "no", you get an error. The ? operator propagates that error up.
Once connected, client.query takes your SQL. It doesn't execute it immediately. It prepares the statement, binds the parameters, executes, and fetches results. The parameters are crucial. You never interpolate strings into SQL. You use $1, $2. This prevents SQL injection. The &[&18] syntax creates a slice of references to the parameters. The double reference is because the trait requires &dyn ToStatement.
The parameters are your shield. Never interpolate user input into SQL.
Parameters and type safety
The parameter syntax &[&18] looks weird. It's a slice of references. The query method takes &[&(dyn ToStatement + Sync)]. The outer & is the slice reference. The inner & is the reference to the value. The value implements ToStatement. This indirection allows the crate to borrow the parameters without taking ownership.
You can pass &String, &i32, &bool, &f64. The crate knows how to encode these types into the PostgreSQL binary format. If you try to pass a type that doesn't implement ToStatement, the compiler rejects you with a trait bound error. This is a good thing. It catches type mismatches at compile time.
Convention: Use &[&value] for single parameters. For multiple, use &[&a, &b, &c]. Don't construct the slice manually unless you need dynamic parameter counts. The compiler infers the types from the values.
Realistic example
Insert data, fetch it back, and map it to a struct. The postgres crate doesn't map rows to structs automatically. You write the mapping code. This gives you control but adds boilerplate.
use postgres::{Client, NoTls};
/// Represents a habit record.
struct Habit {
name: String,
completed: bool,
}
/// Inserts a habit and retrieves it to verify.
fn main() -> Result<(), Box<dyn std::error::Error>> {
let mut client = Client::connect("host=localhost user=postgres", NoTls)?;
// Use execute for inserts. It returns the row count, not the data.
// This is slightly more efficient when you don't need the result set.
let count = client.execute(
"INSERT INTO habits (name, completed) VALUES ($1, $2)",
&[&"Read Rust FAQ", &true],
)?;
println!("Inserted {count} row(s)");
// Fetch the single row back.
// query_one panics if the result has zero or more than one row.
let row = client.query_one(
"SELECT name, completed FROM habits WHERE name = $1",
&[&"Read Rust FAQ"],
)?;
// Map the row to a struct manually.
// The postgres crate does not auto-map structs like some ORMs do.
let habit = Habit {
name: row.get(0),
completed: row.get(1),
};
println!("Found: {} (done: {})", habit.name, habit.completed);
Ok(())
}
Insert, fetch, map. The loop is tight.
Transactions
Databases shine when you need atomicity. A transaction groups multiple operations into a single unit. Either all operations succeed, or none do. The postgres crate supports transactions via client.transaction().
use postgres::{Client, NoTls};
/// Transfers points between two habits atomically.
fn transfer_points(
client: &mut Client,
from: &str,
to: &str,
amount: i32,
) -> Result<(), Box<dyn std::error::Error>> {
// Start a transaction.
// All operations inside the transaction block share the same isolation level.
let mut tx = client.transaction()?;
tx.execute(
"UPDATE habits SET points = points - $1 WHERE name = $2",
&[&amount, &from],
)?;
tx.execute(
"UPDATE habits SET points = points + $1 WHERE name = $2",
&[&amount, &to],
)?;
// Commit the transaction.
// If any previous step failed, this is never reached and the changes roll back.
tx.commit()?;
Ok(())
}
Atomicity is the point. Either both updates happen, or neither does.
Pitfalls and errors
Database errors come in two flavors. Connection errors happen when the server is down or credentials are wrong. Query errors happen when SQL is invalid or constraints are violated. The postgres::Error type wraps both. You can inspect the SqlState code to distinguish them. For example, a unique constraint violation returns 23505. Handling these errors gracefully prevents your app from crashing on bad data.
The crate buffers results. It doesn't stream by default. For massive result sets, use query_raw or cursor-based fetching. Streaming keeps memory usage low. If you fetch a million rows into a Vec, you'll spike memory usage.
Convention: In main, Box<dyn std::error::Error> is convenient. In library code, define a custom error type or use thiserror to expose specific failure modes. Don't leak database internals into your public API.
Treat the connection string like a secret. Never commit it to git.
Decision: when to use this vs alternatives
Use postgres when you need a simple, synchronous client for scripts, CLIs, or when you're managing concurrency manually. Use tokio-postgres when you're building an async application with Tokio and need non-blocking database calls. Use sqlx when you want compile-time checked queries and a unified API across multiple database backends. Use diesel when you prefer an ORM-style query builder that generates SQL from Rust code rather than writing raw SQL strings.
Pick the tool that matches your runtime. Mixing sync and async without care leads to deadlocks.