Rust’s sqlx crate lets you write async database queries, but the real magic is how it bakes compile-time query validation and automatic migration management into your workflow.
Let’s see sqlx in action with a simple async web service that fetches user data.
use axum::{
extract::{Path, State},
http::StatusCode,
response::Json,
routing::get,
Router,
};
use serde::Serialize;
use sqlx::{postgres::PgPoolOptions, PgPool};
use std::{env, net::SocketAddr};
#[derive(Serialize)]
struct User {
id: i32,
username: String,
}
// This query is validated at compile time by sqlx-cli
// If the SQL is invalid or the columns don't match the struct, it won't compile.
#[sqlx::query_as(struct_name = "User")]
async fn get_user_by_id(pool: &PgPool, user_id: i32) -> Result<User, sqlx::Error> {
sqlx_query!(
"SELECT id, username FROM users WHERE id = $1",
user_id
)
}
async fn user_handler(
State(pool): State<PgPool>,
Path(user_id): Path<i32>,
) -> Result<Json<User>, StatusCode> {
match get_user_by_id(&pool, user_id).await {
Ok(user) => Ok(Json(user)),
Err(_) => Err(StatusCode::INTERNAL_SERVER_ERROR),
}
}
#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
let database_url = env::var("DATABASE_URL").expect("DATABASE_URL must be set");
let pool = PgPoolOptions::new()
.max_connections(5)
.connect(&database_url)
.await?;
// Apply migrations before starting the server
sqlx::migrate!("./migrations").run(&pool).await?;
let app = Router::new()
.route("/users/:user_id", get(user_handler))
.with_state(pool);
let addr = SocketAddr::from(([127, 0, 0, 1], 3000));
println!("Listening on {}", addr);
axum::Server::bind(&addr)
.serve(app.into_make_service())
.await?;
Ok(())
}
The problem sqlx solves is the brittle nature of dynamic SQL in many ORMs and raw query builders. You write a query, it looks right, and only at runtime do you discover a typo in a column name, a missing JOIN, or a mismatch between your SQL result set and your Rust struct. This leads to frustrating debugging cycles. sqlx shifts this validation to compile time.
Internally, sqlx uses a compile-time check. When you use #[sqlx::query(...)] or #[sqlx::query_as(...)], the sqlx-cli tool (which you run with cargo build or cargo sqlx prepare) parses your SQL. It connects to your database (using the DATABASE_URL environment variable) and inspects the schema. It then verifies that the SQL syntax is correct, that the tables and columns referenced exist, and that the types returned by the query can be mapped to the fields of your specified Rust struct. If any of these checks fail, cargo build will fail with a specific error message pointing to the problematic query.
The sqlx-cli also manages database migrations. You define your schema evolution in SQL files within a migrations directory. Each file is timestamped (e.g., 20230101000000_create_users_table.sql). sqlx keeps track of which migrations have been applied to a specific database instance in a special _sqlx_migrations table. When you run sqlx::migrate!("./migrations").run(&pool).await?, sqlx checks this table, identifies pending migrations, and executes them in order. The run function returns Ok only if all migrations applied successfully.
The #[sqlx::query_as(struct_name = "User")] macro is key here. It tells sqlx to expect a query that returns rows compatible with the User struct. sqlx then generates an async fn that takes a PgPool (or other database pool type) and any parameters needed for the query. Inside this generated function, sqlx uses its query or query_as methods, passing the SQL string and parameters. The compile-time check ensures that the SELECT id, username FROM users WHERE id = $1 statement will produce columns named id and username with types compatible with i32 and String respectively, which are then mapped to the User struct’s fields.
The sqlx_query! macro is a convenience for embedding the SQL directly within the generated function. It’s syntactic sugar over sqlx::query_as!(User, "SELECT ..."). When sqlx-cli runs, it parses the SQL within sqlx_query! during the cargo sqlx prepare step (or implicitly during cargo build).
The PgPoolOptions::new().max_connections(5).connect(&database_url).await? part sets up a connection pool. Instead of establishing a new connection for every query, which is expensive, sqlx maintains a pool of open connections. When a query needs a connection, it borrows one from the pool. When the query finishes, the connection is returned to the pool, ready for reuse. This significantly improves performance for applications with many concurrent database requests. The max_connections parameter controls the maximum number of concurrent connections the pool will maintain.
The sqlx::migrate!("./migrations") macro tells sqlx to look for migration files in the ./migrations directory relative to your crate root. It will automatically generate code that applies any pending migrations when this macro is invoked. This ensures your database schema is always up-to-date before your application logic attempts to interact with it.
One common point of confusion is how sqlx-cli gets its database credentials for schema validation. It uses the DATABASE_URL environment variable. This means that for cargo build to perform its compile-time checks, you must have a DATABASE_URL set in your environment that points to a database with the correct schema. If you’re working on a project with multiple developers, ensuring everyone has a consistent DATABASE_URL (or using a .env file with dotenv crate) is crucial for a smooth development experience.
The next step is often integrating more complex query patterns, like transactions or handling large result sets efficiently.