Databases with SQLx: what every Rust developer should know

Introduction

Production databases in Rust demand more than just any SQL library—they demand correctness. SQLx has become the go-to choice for teams building serious applications because it catches SQL errors at compile time, not at 3 AM in production. Databases with SQLx: what every Rust developer should know covers the core patterns that protect your queries, streamline your migrations, and let your CI pipelines run without needing a live database.

We'll walk through setting up connection pools, leveraging compile-time query validation, managing schema changes safely, and handling offline builds. These are the patterns we use across trading platforms, e-commerce backends, and SaaS systems that need predictable, verifiable database interactions.

Connection Pools with PgPool in Application State

Every production Rust application needs a database connection pool. Opening a fresh TCP connection for each query would cripple throughput; instead, we maintain a pool of reusable connections and share it across request handlers.

In frameworks like Axum or Actix, the pool lives in application state and is cloned (cheaply) into each route handler. The PgPool type from SQLx is designed for this pattern—it's an Arc-wrapped pool that can be shared across threads without friction.

use axum::{
    extract::State,
    routing::get,
    Router,
};
use sqlx::PgPool;
use std::net::SocketAddr;

#[derive(Clone)]
pub struct AppState {
    pub db: PgPool,
}

pub async fn get_user(
    State(state): State<AppState>,
    axum::extract::Path(id): axum::extract::Path<i32>,
) -> Result<String, String> {
    let user = sqlx::query!("SELECT id, email FROM users WHERE id = $1", id)
        .fetch_one(&state.db)
        .await
        .map_err(|e| e.to_string())?;

    Ok(format!("User: {}", user.email))
}

#[tokio::main]
async fn main() {
    let database_url = "postgres://user:password@localhost/myapp";
    let pool = PgPool::connect(database_url)
        .await
        .expect("Failed to create pool");

    let state = AppState { db: pool };

    let app = Router::new()
        .route("/users/:id", get(get_user))
        .with_state(state);

    let addr = SocketAddr::from(([127, 0, 0, 1], 3000));
    axum::Server::bind(&addr)
        .serve(app.into_make_service_with_connect_info::<SocketAddr>())
        .await
        .unwrap();
}

The pool automatically handles connection lifecycle. It opens connections on demand, reuses them, and closes them when they're no longer needed or when the pool is dropped. Configure pool size based on your workload: too small and you'll hit contention; too large and you waste resources.

let pool = sqlx::postgres::PgPoolOptions::new()
    .max_connections(20)
    .min_connections(5)
    .acquire_timeout(std::time::Duration::from_secs(5))
    .connect(database_url)
    .await?;

This setup—pool in state, cloned into handlers—is the backbone of every Rust web service we build. It's simple, thread-safe, and efficient.

Compile-Time Query Validation with query_as!

SQLx's killer feature is the query_as! macro. Unlike hand-written SQL strings that fail at runtime, this macro checks your query against the actual database at compile time, then generates strongly-typed structs for the results.

Consider a user query:

#[derive(Debug, sqlx::FromRow)]
pub struct User {
    pub id: i32,
    pub email: String,
    pub created_at: chrono::DateTime<chrono::Utc>,
}

pub async fn fetch_user(pool: &PgPool, user_id: i32) -> Result<User, sqlx::Error> {
    let user = sqlx::query_as!(
        User,
        "SELECT id, email, created_at FROM users WHERE id = $1",
        user_id
    )
    .fetch_one(pool)
    .await?;

    Ok(user)
}

During compilation, the macro:

1. Connects to your database (or reads cached metadata in offline mode).
2. Parses and validates the SQL syntax.
3. Verifies that every column in the SELECT exists and matches the struct field types.
4. Checks that parameter counts and types align with your Rust code.
5. Generates optimized code to deserialize rows into User instances.

This means typos in column names, mismatched types, or missing parameters are caught before the binary is shipped. If you rename a column in production and forget to update Rust code, the build fails—not a user's request.

For queries that return unstructured data or where you want more flexibility, use query! instead of query_as!:

let rows = sqlx::query!(
    "SELECT id, email, COUNT(*) as message_count FROM users GROUP BY id, email"
)
.fetch_all(pool)
.await?;

for row in rows {
    println!("User {}: {} messages", row.id, row.message_count);
}

The macro-generated struct has fields matching the SELECT columns, with types inferred from the database schema.

Managing Schema Changes with SQLx Migrations

Schema evolves. Tables are added, columns are renamed, indexes are created. SQLx provides a built-in migration system that versioning and applies SQL files in order, tracking which have already run.

Create a migrations/ directory at your project root. Each migration is a numbered SQL file:

// Directory structure:
// migrations/
//   20240101000001_create_users_table.sql
//   20240101000002_add_email_index.sql
//   20240101000003_create_orders_table.sql

A migration file looks like this:

-- migrations/20240101000001_create_users_table.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Run migrations programmatically in your application startup:

use sqlx::postgres::PgPoolOptions;

#[tokio::main]
async fn main() {
    let database_url = "postgres://user:password@localhost/myapp";
    
    let pool = PgPoolOptions::new()
        .max_connections(10)
        .connect(&database_url)
        .await
        .expect("Failed to connect");

    // Run all pending migrations
    sqlx::migrate!("./migrations")
        .run(&pool)
        .await
        .expect("Failed to run migrations");

    // Application runs with schema guaranteed to be up to date
    println!("Database migrated successfully");
}

Or use the CLI to manage migrations manually:

// Create a new migration
sqlx migrate add -r create_posts_table

// Run pending migrations
sqlx migrate run --database-url "postgres://..."

// Revert the last migration (if reversible)
sqlx migrate revert --database-url "postgres://..."

Migrations are idempotent—running them multiple times is safe because SQLx tracks which have already been applied in a _sqlx_migrations table. This makes deployments predictable: apply migrations, then start the app.

Offline Mode: Building Without a Live Database

One complication: compile-time query checking requires connecting to a real database during compilation. In CI environments or when developers don't have a database locally, this breaks the build.

SQLx's offline mode solves this. During development or in CI, you prepare query metadata once and commit it to version control. Subsequent builds use this cached data instead of connecting to the database.

Enable offline mode by creating a sqlx-data.json file. First, prepare your queries:

// In your CI or on a developer machine with database access:
cargo sqlx prepare --database-url "postgres://..." -- --lib

This command runs your code, executes all query! and query_as! macros, and stores metadata in .sqlx/:

// .sqlx/query-[hash].json stores column names, types, and parameter info
// Example entry:
{
  "db": "PostgreSQL",
  "query": "SELECT id, email FROM users WHERE id = $1",
  "columns": [
    { "name": "id", "type": "int4" },
    { "name": "email", "type": "varchar" }
  ],
  "parameters": { "Left": ["int4"] }
}

Commit these files to git. Now any CI build can compile without a database:

// In CI (GitHub Actions example):
- name: Build with offline mode
  env:
    SQLX_OFFLINE: true
  run: cargo build --release

When SQLX_OFFLINE=true, the macros use the cached metadata instead of connecting. Your CI runs faster, doesn't need database credentials, and still validates queries.

Common Mistakes

• Forgetting to commit .sqlx/ directory: CI builds fail if offline metadata isn't in version control. Add it to your repo, or automate the cargo sqlx prepare step in CI before building.
• Mismatched nullable handling: If a column is nullable in the database but you define it as non-nullable in your struct, deserialization panics. Use Option<T> for nullable columns: pub email: Option<String>.
• Pool exhaustion under load: Too many concurrent requests with too few pool connections causes timeouts. Monitor connection usage and tune pool size based on your workload.
• Holding connections across awaits: If you acquire a connection manually with pool.acquire(), hold it only for the actual database operation. Holding it while calling external APIs wastes pool resources.
• Skipping migration reversibility: Write migrations with rollback logic (DOWN in reversible migrations) so you can safely revert if needed. This is critical for production deployments.

Key Takeaways

• Use PgPool in application state and clone it into handlers. Configure pool size based on expected concurrency.
• Leverage query_as! macros to catch SQL errors at compile time. Typos and schema mismatches fail the build, not production.
• Manage schema changes with SQLx migrations. They're versioned, ordered, and tracked automatically—no manual synchronization.
• Enable offline mode in CI by running cargo sqlx prepare and committing .sqlx/ metadata. Builds no longer need a database during compilation.
• Handle nullability explicitly with Option<T>. The type system enforces safety.