Rust and SQLx in 2026: Compile-Time Checked Queries and Interview Questions

SQLx 0.9 transforms database access in Rust by catching SQL errors at compile time rather than runtime. Released in May 2026, this version introduces a new configuration system, runtime flexibility, and stricter query safety through the SqlSafeStr trait.

Compile-Time Query Verification in SQLx 0.9

The query! macro checks every SQL statement against your database schema during cargo build. This catches typos, type mismatches, and missing columns before the code ever runs.

use sqlx::{FromRow, PgPool};

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

pub async fn get_user_by_email(pool: &PgPool, email: &str) -> Result<Option<User>, sqlx::Error> {
    // Compile-time verified: column names, types, and return structure
    sqlx::query_as!(
        User,
        r#"
        SELECT id, email, created_at
        FROM users
        WHERE email = $1
        "#,
        email
    )
    .fetch_optional(pool)
    .await
}

The query_as! macro maps rows directly to the User struct. If the users table lacks a created_at column, compilation fails with a clear error pointing to the exact line.

The sqlx.toml Configuration System

SQLx 0.9 introduces a sqlx.toml file that centralizes database configuration, type overrides, and multi-database setups. This replaces scattered environment variables and simplifies CI pipelines.

# sqlx.toml
[common]
database_url_var = "DATABASE_URL"

[macros]
default_type_override.uuid = "uuid::Uuid"
default_type_override.timestamptz = "chrono::DateTime<chrono::Utc>"

[sqlite]
extensions = ["uuid", "crypto"]

Type overrides eliminate repetitive casting. Every UUID column maps to uuid::Uuid automatically, and every timestamptz to chrono::DateTime without per-query annotations.

SqlSafeStr: Preventing SQL Injection at the Type Level

SQLx 0.9 hardens query safety by requiring SqlSafeStr for string parameters. By default, only &'static str implements this trait, blocking dynamic strings that could carry injection payloads.

use sqlx::{AssertSqlSafe, PgPool};

// Static table name: compiles directly
const TABLE: &str = "products";

pub async fn search_products(
    pool: &PgPool,
    user_query: &str,
) -> Result<Vec<Product>, sqlx::Error> {
    // Safe: $1 is a parameterized value, not interpolated SQL
    sqlx::query_as!(
        Product,
        r#"SELECT * FROM products WHERE name ILIKE '%' || $1 || '%'"#,
        user_query
    )
    .fetch_all(pool)
    .await
}

// Dynamic table from config: wrap in AssertSqlSafe after validation
pub async fn count_table(pool: &PgPool, table: &str) -> Result<i64, sqlx::Error> {
    // Validate table name against allowlist before asserting safety
    let allowed = ["users", "products", "orders"];
    if !allowed.contains(&table) {
        return Err(sqlx::Error::Configuration("Invalid table".into()));
    }
    
    let query = format!("SELECT COUNT(*) as count FROM {}", table);
    let row: (i64,) = sqlx::query_as(&AssertSqlSafe(query))
        .fetch_one(pool)
        .await?;
    Ok(row.0)
}

User input goes through parameterized queries with $1 placeholders. Dynamic SQL from trusted sources requires explicit AssertSqlSafe wrapping after validation.

Database Migrations with sqlx-cli

The sqlx-cli tool manages schema migrations with versioned SQL files. Migrations run in order, and the tool tracks which have been applied.

# Install the CLI (0.9.x no longer needs --locked)
cargo install sqlx-cli --features postgres

# Create a new migration
sqlx migrate add create_users_table

# Run pending migrations
sqlx migrate run

# Check migration status
sqlx migrate info

Each migration creates a pair of files in the migrations/ directory:

-- migrations/20260912120000_create_users_table.up.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);

-- migrations/20260912120000_create_users_table.down.sql
DROP TABLE IF EXISTS users;

The up.sql runs on migrate run, and down.sql on migrate revert. Keeping both files allows rolling back failed deployments.

Offline Mode for CI Pipelines

Compile-time checks require a database connection during builds. For CI environments without database access, SQLx caches query metadata in a .sqlx/ directory.

# Generate query cache locally (requires DATABASE_URL)
cargo sqlx prepare

# Commit the .sqlx/ directory
git add .sqlx/
git commit -m "Update SQLx query cache"

In CI, builds use the cached metadata:

# .github/workflows/ci.yml
jobs:
  build:
    runs-on: ubuntu-latest
    env:
      SQLX_OFFLINE: true
    steps:
      - uses: actions/checkout@v4
      - name: Build
        run: cargo build --release

The SQLX_OFFLINE=true environment variable tells SQLx to use cached metadata instead of connecting to a database. This keeps CI fast and avoids database credentials in build environments.

Connection Pooling with PgPool

SQLx provides built-in connection pooling. PgPool manages a pool of connections, reusing them across requests instead of opening a new connection each time.

use sqlx::postgres::PgPoolOptions;
use std::time::Duration;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = PgPoolOptions::new()
        .max_connections(10)
        .min_connections(2)
        .acquire_timeout(Duration::from_secs(3))
        .idle_timeout(Duration::from_secs(600))
        .connect(&std::env::var("DATABASE_URL").expect("DATABASE_URL required"))
        .await?;
    
    // Pool is Clone and Send, safe to share across tasks
    let app_state = AppState { db: pool };
    
    // Start your web server with app_state...
    Ok(())
}

Set max_connections based on your database's connection limit and the number of application instances. A typical rule: database_max_connections / number_of_instances.

Interview Questions: SQLx and Database Access in Rust

Technical interviews for Rust backend roles often probe database handling. These questions test understanding of SQLx's compile-time guarantees and async patterns.

Q: How does SQLx verify queries at compile time?

The query! macros connect to a running database during cargo build. They parse the SQL, send it to the database for planning (without execution), and verify that column names exist, types match, and the query is syntactically valid. The database's own query planner does the validation, so edge cases specific to Postgres, MySQL, or SQLite get caught.

Q: What happens if the database schema changes after compilation?

The compiled binary contains the query plan from build time. If the schema changes (column renamed, type altered), queries fail at runtime with a decode error or column-not-found error. Teams mitigate this by running migrations before deployment and rebuilding after schema changes. In production, blue-green deployments ensure the new binary and new schema go live together.

Q: When would you choose SQLx over Diesel or SeaORM?

SQLx fits projects where developers want full control over SQL without learning a DSL. Diesel generates SQL from Rust code and catches schema drift at compile time through its schema.rs file. SeaORM provides an ActiveRecord-style API with migrations. SQLx works best when the team writes complex SQL (CTEs, window functions, database-specific features) and wants compile-time checking without abstraction.

Q: Explain the tradeoff between query! and query_as.

The query! macro returns an anonymous record type with fields matching the SELECT columns. The query_as! macro maps results to a named struct implementing FromRow. Use query! for one-off queries where defining a struct adds overhead. Use query_as! when the result type appears in multiple places or needs to implement traits.

// query! returns anonymous record
let row = sqlx::query!("SELECT id, name FROM users WHERE id = $1", user_id)
    .fetch_one(pool)
    .await?;
let name: String = row.name; // Access by field name

// query_as! maps to struct
let user: User = sqlx::query_as!(User, "SELECT id, name FROM users WHERE id = $1", user_id)
    .fetch_one(pool)
    .await?;

Q: How do you handle transactions in SQLx?

Start a transaction with pool.begin(), execute queries on the transaction object, then call commit() or let it drop to rollback.

pub async fn create_order_with_items(
    pool: &PgPool,
    order: NewOrder,
    items: Vec<OrderItem>,
) -> Result<i32, sqlx::Error> {
    let mut tx = pool.begin().await?;
    
    let order_id: (i32,) = sqlx::query_as(
        "INSERT INTO orders (user_id, total) VALUES ($1, $2) RETURNING id"
    )
    .bind(order.user_id)
    .bind(order.total)
    .fetch_one(&mut *tx)
    .await?;
    
    for item in items {
        sqlx::query(
            "INSERT INTO order_items (order_id, product_id, quantity) VALUES ($1, $2, $3)"
        )
        .bind(order_id.0)
        .bind(item.product_id)
        .bind(item.quantity)
        .execute(&mut *tx)
        .await?;
    }
    
    tx.commit().await?;
    Ok(order_id.0)
}

If any query fails, the transaction rolls back automatically when tx drops. Explicit rollback is rarely needed.

Production Patterns for SQLx 0.9

These patterns address common production scenarios: handling nullable columns, custom types, and batch operations.

Nullable Columns and Option Types

#[derive(FromRow)]
pub struct UserProfile {
    pub id: i32,
    pub email: String,
    pub avatar_url: Option<String>, // Nullable column maps to Option
    pub bio: Option<String>,
}

pub async fn update_profile(
    pool: &PgPool,
    user_id: i32,
    avatar_url: Option<&str>,
    bio: Option<&str>,
) -> Result<(), sqlx::Error> {
    sqlx::query!(
        r#"
        UPDATE users 
        SET avatar_url = COALESCE($2, avatar_url),
            bio = COALESCE($3, bio),
            updated_at = NOW()
        WHERE id = $1
        "#,
        user_id,
        avatar_url,
        bio
    )
    .execute(pool)
    .await?;
    Ok(())
}

Batch Inserts with UNNEST

For bulk inserts, UNNEST avoids multiple round trips:

pub async fn insert_tags(pool: &PgPool, tags: &[String]) -> Result<Vec<i32>, sqlx::Error> {
    let ids: Vec<(i32,)> = sqlx::query_as(
        r#"
        INSERT INTO tags (name)
        SELECT * FROM UNNEST($1::text[])
        ON CONFLICT (name) DO UPDATE SET name = EXCLUDED.name
        RETURNING id
        "#
    )
    .bind(tags)
    .fetch_all(pool)
    .await?;
    
    Ok(ids.into_iter().map(|(id,)| id).collect())
}

What to Remember About SQLx in Rust

• The query! macros verify SQL at compile time against a live database, catching typos and type mismatches before runtime
• SQLx 0.9 introduces sqlx.toml for type overrides, multi-database configs, and SQLite extension loading
• SqlSafeStr prevents SQL injection by restricting query strings to static values unless explicitly wrapped in AssertSqlSafe
• Use cargo sqlx prepare to cache query metadata for offline builds in CI
• PgPool handles connection pooling with configurable limits, timeouts, and idle settings
• Migrations live in SQL files managed by sqlx-cli, with up/down pairs for rollback support
• For Rust interview prep, practice explaining compile-time verification, the query! vs query_as! tradeoff, and transaction handling patterns

For more on Rust database patterns, see the error handling guide which covers Result types in async contexts, and the traits and generics tutorial for implementing FromRow on custom types.