When you need a database that doesn't block your async runtime
You're writing a Rust service. You need to talk to a database. You try a synchronous driver, and your async runtime screams at you for blocking. You try writing your own connection pooling, and you realize you're reinventing a wheel that needs to handle timeouts, retries, and thread safety. You need a crate that handles the heavy lifting of database connections while playing nice with tokio or async-std. That crate is sqlx.
sqlx is an async SQL toolkit. It gives you connection pooling, query execution, and type mapping. Think of a connection pool like a library of checkout counters. Instead of building a new counter every time someone wants a book, you have a fixed set of counters. When a request comes in, a counter becomes available, handles the request, and goes back to the pool. sqlx manages these counters so you don't have to. It also ensures that the data coming back matches the types you expect in Rust.
How the pool works
A database connection is expensive. Opening a TCP connection, negotiating TLS, and authenticating takes time. If you open a new connection for every request, your latency spikes and the database gets overwhelmed. A connection pool keeps a set of open connections ready to use.
sqlx uses PgPoolOptions to configure the pool. You set limits and timeouts. The pool creates connections up to the maximum limit. When you run a query, sqlx borrows a connection from the pool, sends the query, waits for the result, and returns the connection. The connection is never dropped; it's reused.
use sqlx::postgres::{PgPool, PgPoolOptions};
use std::time::Duration;
/// Creates a configured connection pool.
/// Fails fast if the database is unreachable.
async fn create_pool() -> PgPool {
PgPoolOptions::new()
// Cap the pool size to prevent overwhelming the database.
// If more requests arrive than this, they wait for a connection.
.max_connections(10)
// If a request waits longer than this for a connection, fail.
// This prevents your service from hanging indefinitely.
.acquire_timeout(Duration::from_secs(5))
// Connect to the database.
// This opens up to max_connections immediately.
.connect("postgres://user:password@localhost/mydb")
.await
.expect("Failed to connect to database")
}
The connect call is eager. It tries to open connections right away. If the database is down, connect returns an error. You fail fast. This is better than waiting until the first query to discover the database is gone.
Running your first query
Once you have a pool, you can run queries. sqlx provides three main query functions: query, query_as, and query_scalar.
queryreturns raw rows. You extract columns manually.query_asmaps the result to a Rust struct.query_scalarreturns a single column from a single row.
Use query_scalar for simple lookups. Use query_as when you need multiple columns. Use query when you need fine-grained control over row extraction.
use sqlx::PgPool;
/// Fetches a greeting string from the database.
async fn get_greeting(pool: &PgPool) -> Result<String, sqlx::Error> {
// query_scalar runs a query and returns a single value.
// fetch_one expects exactly one row.
// If zero rows are returned, fetch_one returns an error.
sqlx::query_scalar::<_, String>("SELECT 'hello from sqlx'")
.fetch_one(pool)
.await
}
The type parameter ::<_, String> tells sqlx to decode the result as a String. The first type parameter is the row type, which sqlx infers for query_scalar. The second is the output type. If the database returns a type that doesn't match String, sqlx returns a decode error.
Mapping rows to structs
Real applications fetch complex data. You want rows as Rust structs. Derive FromRow on your struct. sqlx maps columns to fields by name.
use sqlx::{postgres::PgPool, FromRow};
/// Represents a user row from the database.
/// Field names must match column names.
#[derive(Debug, FromRow)]
struct User {
id: i64,
name: String,
email: String,
}
/// Fetches a user by ID.
/// Returns None if the user doesn't exist.
async fn get_user(pool: &PgPool, user_id: i64) -> Result<Option<User>, sqlx::Error> {
// query_as maps the result to the User struct.
// bind passes parameters safely, preventing SQL injection.
// fetch_optional returns None instead of an error if no rows are found.
sqlx::query_as!(
User,
"SELECT id, name, email FROM users WHERE id = $1",
user_id
)
.fetch_optional(pool)
.await
}
The query_as! macro does two things. It checks that the SQL syntax is valid. It checks that the columns match the struct fields. If you rename a field or change a type, the compiler catches the mismatch. This is compile-time checked SQL. It saves you from runtime errors caused by typos in your queries.
Convention: Use the macro versions (query!, query_as!, query_scalar!) whenever your SQL is static. They require a database connection at compile time to verify queries. Set the DATABASE_URL environment variable when building. If you skip this, you lose the compile-time safety net.
Handling parameters and SQL injection
Never concatenate user input into SQL strings. Use bind to pass parameters. bind sends the query and parameters separately to the database. The database treats parameters as data, not as SQL code. This prevents SQL injection.
use sqlx::PgPool;
/// Updates a user's email.
/// Returns the number of rows affected.
async fn update_email(pool: &PgPool, user_id: i64, new_email: &str) -> Result<u64, sqlx::Error> {
// bind passes parameters by position.
// $1 corresponds to the first bind, $2 to the second.
let result = sqlx::query(
"UPDATE users SET email = $1 WHERE id = $2"
)
.bind(new_email)
.bind(user_id)
.execute(pool)
.await?;
Ok(result.rows_affected())
}
bind supports named parameters too. Use :name in the SQL and bind(name, value). Named parameters make complex queries easier to read.
Transactions
Databases support transactions. A transaction groups multiple queries into a single unit of work. Either all queries succeed, or none do. sqlx supports transactions via the begin method.
use sqlx::PgPool;
/// Transfers funds between accounts.
/// Rolls back if any step fails.
async fn transfer_funds(
pool: &PgPool,
from_id: i64,
to_id: i64,
amount: i64,
) -> Result<(), sqlx::Error> {
// begin starts a transaction.
// tx implements the Connection trait, so you can use it like a pool.
let mut tx = pool.begin().await?;
// Deduct from sender.
sqlx::query("UPDATE accounts SET balance = balance - $1 WHERE id = $2")
.bind(amount)
.bind(from_id)
.execute(&mut *tx)
.await?;
// Add to receiver.
sqlx::query("UPDATE accounts SET balance = balance + $1 WHERE id = $2")
.bind(amount)
.bind(to_id)
.execute(&mut *tx)
.await?;
// commit finalizes the transaction.
// If any query above returned an error, commit is never reached.
// The transaction rolls back automatically when tx is dropped.
tx.commit().await
}
The transaction object tx holds the connection. If you drop tx without calling commit, the transaction rolls back. This is safe. You don't need to manually handle rollbacks. Just let the error propagate.
Convention: Keep transactions short. Long transactions hold locks and block other queries. Do only the database work inside the transaction. Fetch data, validate, then start the transaction.
Pitfalls and compiler errors
sqlx has a few gotchas. Knowing them saves debugging time.
If you forget to enable the runtime feature in Cargo.toml, the compiler rejects your code with E0277 (trait bound not satisfied). You need runtime-tokio-rustls or runtime-tokio-native-tls. The error message points to the missing trait. Add the feature to fix it.
If you try to use a type that doesn't implement FromRow, you get E0277 again. Derive FromRow or implement it manually.
If your pool runs out of connections, sqlx waits up to acquire_timeout. If the timeout expires, you get a PoolTimedOut error. This means your pool is too small or your queries are too slow. Increase max_connections or optimize your queries.
If you block the async runtime on a database call, your service hangs. sqlx is async. You must .await every call. If you use a sync driver instead, you block the runtime. Never do this.
Convention: Use runtime-tokio-rustls for most projects. rustls is pure Rust and easier to cross-compile. native-tls uses the system's TLS library, which can cause build issues on some platforms. Stick with rustls unless you have a specific reason not to.
Decision matrix
Choose the right tool for your database needs.
Use sqlx when you want async SQL with compile-time query checking and zero-cost abstractions. Use sqlx when you need connection pooling and type-safe query execution. Use sqlx when you want to write raw SQL but still get Rust's type safety.
Use diesel when you prefer a query builder that generates SQL at runtime and want a mature ORM-like experience. Use diesel when you need complex query composition that's hard to express in raw SQL.
Use sea-orm when you need a full ORM with entity generation and migrations built-in. Use sea-orm when you want to generate code from your database schema.
Use the postgres crate when you need maximum control over the wire protocol and don't care about query building or pooling. Use the postgres crate when you're building a database driver or tool.
Reach for sqlx macros when you want the compiler to verify your SQL syntax and column types. Reach for sqlx::query when your SQL is dynamic and constructed at runtime.