Eric Campbell 5 min read Rust

How to Use SQLx for Database Access in Rust

SQLx is an async, compile-time checked SQL toolkit for Rust that strikes the perfect balance between raw SQL flexibility and type safety. Unlike traditional ORMs that abstract SQL away, SQLx embraces...

Key Insights

  • SQLx provides compile-time SQL verification that catches errors before your code runs, eliminating an entire class of runtime database bugs while maintaining the flexibility of raw SQL
  • Unlike ORMs, SQLx embraces SQL directly with zero-cost abstractions and async/await support, making it ideal for performance-critical applications that need fine-grained query control
  • The macro-based approach (query! and query_as!) generates type-safe Rust code from your actual database schema, providing IntelliSense support and preventing type mismatches at compile time

Introduction to SQLx

SQLx is an async, compile-time checked SQL toolkit for Rust that strikes the perfect balance between raw SQL flexibility and type safety. Unlike traditional ORMs that abstract SQL away, SQLx embraces it while providing compile-time guarantees that your queries are valid and type-correct.

The primary advantage of SQLx is its compile-time verification. When you write a query, SQLx connects to your database during compilation, validates the SQL syntax, checks that referenced tables and columns exist, and generates properly typed Rust code. This means SQL typos and type mismatches become compiler errors, not runtime panics.

SQLx supports PostgreSQL, MySQL, SQLite, and MSSQL. It’s built on async/await from the ground up, making it perfect for modern web services. Compared to Diesel (Rust’s most popular ORM), SQLx gives you more control over your SQL while still maintaining type safety. Diesel uses a DSL that can feel limiting when you need complex queries; SQLx lets you write the exact SQL you want.

Add SQLx to your project with these dependencies:

[dependencies]
sqlx = { version = "0.7", features = ["runtime-tokio", "tls-rustls", "postgres"] }
tokio = { version = "1", features = ["full"] }

Replace "postgres" with "mysql", "sqlite", or "mssql" depending on your database. The runtime-tokio feature integrates with the Tokio async runtime.

Setting Up SQLx with Database Connections

Before writing queries, install the SQLx CLI for database management:

cargo install sqlx-cli --no-default-features --features postgres

SQLx uses connection pools to efficiently manage database connections. A pool maintains multiple connections that can be reused across requests, avoiding the overhead of establishing new connections repeatedly.

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

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    // Load database URL from environment
    let database_url = std::env::var("DATABASE_URL")
        .expect("DATABASE_URL must be set");
    
    // Create connection pool with custom settings
    let pool = PgPoolOptions::new()
        .max_connections(5)
        .acquire_timeout(Duration::from_secs(3))
        .connect(&database_url)
        .await?;
    
    println!("Connected to database successfully");
    
    Ok(())
}

Store your database URL in a .env file:

DATABASE_URL=postgres://username:password@localhost/mydb

The connection pool handles connection lifecycle automatically. When you execute a query, SQLx acquires a connection from the pool, runs the query, and returns the connection for reuse.

Executing Queries with Compile-Time Verification

SQLx’s killer feature is the query! macro, which validates SQL at compile time. Here’s how it works:

use sqlx::PgPool;

#[derive(Debug)]
struct User {
    id: i32,
    email: String,
    created_at: chrono::NaiveDateTime,
}

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

async fn get_all_users(pool: &PgPool) -> Result<Vec<User>, sqlx::Error> {
    let users = sqlx::query_as!(
        User,
        "SELECT id, email, created_at FROM users ORDER BY created_at DESC"
    )
    .fetch_all(pool)
    .await?;
    
    Ok(users)
}

The query_as! macro maps query results directly to your struct. During compilation, SQLx connects to your database, runs EXPLAIN on your query, and verifies that the columns match your struct fields. If you typo a column name or the types don’t match, you get a compile error.

For queries where you don’t need a struct, use query!:

async fn count_users(pool: &PgPool) -> Result<i64, sqlx::Error> {
    let result = sqlx::query!("SELECT COUNT(*) as count FROM users")
        .fetch_one(pool)
        .await?;
    
    Ok(result.count.unwrap())
}

Performing CRUD Operations

SQLx handles all CRUD operations with the same compile-time guarantees. Here’s a complete example:

use sqlx::PgPool;
use chrono::NaiveDateTime;

#[derive(Debug)]
struct User {
    id: i32,
    email: String,
    name: String,
    created_at: NaiveDateTime,
}

// CREATE
async fn create_user(pool: &PgPool, email: &str, name: &str) -> Result<User, sqlx::Error> {
    let user = sqlx::query_as!(
        User,
        "INSERT INTO users (email, name) VALUES ($1, $2) RETURNING id, email, name, created_at",
        email,
        name
    )
    .fetch_one(pool)
    .await?;
    
    Ok(user)
}

// READ
async fn get_user(pool: &PgPool, id: i32) -> Result<Option<User>, sqlx::Error> {
    let user = sqlx::query_as!(
        User,
        "SELECT id, email, name, created_at FROM users WHERE id = $1",
        id
    )
    .fetch_optional(pool)
    .await?;
    
    Ok(user)
}

// UPDATE
async fn update_user_name(pool: &PgPool, id: i32, name: &str) -> Result<bool, sqlx::Error> {
    let result = sqlx::query!(
        "UPDATE users SET name = $1 WHERE id = $2",
        name,
        id
    )
    .execute(pool)
    .await?;
    
    Ok(result.rows_affected() > 0)
}

// DELETE
async fn delete_user(pool: &PgPool, id: i32) -> Result<bool, sqlx::Error> {
    let result = sqlx::query!("DELETE FROM users WHERE id = $1", id)
        .execute(pool)
        .await?;
    
    Ok(result.rows_affected() > 0)
}

Parameter binding with $1, $2, etc. prevents SQL injection automatically. SQLx uses prepared statements under the hood, so your parameters are never interpolated into the SQL string.

Database Migrations

SQLx includes a migration system for version-controlling your schema. Create a migrations directory:

sqlx migrate add create_users_table

This creates a file like migrations/20240101000000_create_users_table.sql:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);

Run migrations with the CLI:

sqlx migrate run

Or programmatically in your application:

use sqlx::migrate::MigrateDatabase;
use sqlx::postgres::PgPool;

async fn run_migrations(pool: &PgPool) -> Result<(), sqlx::Error> {
    sqlx::migrate!("./migrations")
        .run(pool)
        .await?;
    
    println!("Migrations completed successfully");
    Ok(())
}

The migrate! macro embeds migrations into your binary at compile time. SQLx tracks applied migrations in a _sqlx_migrations table, so it only runs new migrations.

Working with Transactions

Transactions ensure that multiple operations succeed or fail together. SQLx makes transactions straightforward:

use sqlx::{PgPool, Postgres, Transaction};

async fn transfer_funds(
    pool: &PgPool,
    from_user_id: i32,
    to_user_id: i32,
    amount: i32
) -> Result<(), sqlx::Error> {
    let mut tx: Transaction<Postgres> = pool.begin().await?;
    
    // Deduct from sender
    let result = sqlx::query!(
        "UPDATE accounts SET balance = balance - $1 WHERE user_id = $2 AND balance >= $1",
        amount,
        from_user_id
    )
    .execute(&mut *tx)
    .await?;
    
    if result.rows_affected() == 0 {
        // Insufficient funds - rollback happens automatically on drop
        return Err(sqlx::Error::RowNotFound);
    }
    
    // Add to recipient
    sqlx::query!(
        "UPDATE accounts SET balance = balance + $1 WHERE user_id = $2",
        amount,
        to_user_id
    )
    .execute(&mut *tx)
    .await?;
    
    // Commit transaction
    tx.commit().await?;
    
    Ok(())
}

If any query fails or you return early, the transaction rolls back automatically when tx is dropped. Only calling commit() makes changes permanent.

Best Practices and Error Handling

Proper error handling makes your database code robust. Create custom error types that wrap SQLx errors:

use sqlx::Error as SqlxError;
use thiserror::Error;

#[derive(Error, Debug)]
pub enum DatabaseError {
    #[error("User not found")]
    NotFound,
    
    #[error("Email already exists")]
    DuplicateEmail,
    
    #[error("Database error: {0}")]
    SqlxError(#[from] SqlxError),
}

pub type DbResult<T> = Result<T, DatabaseError>;

async fn create_user_safe(pool: &PgPool, email: &str, name: &str) -> DbResult<User> {
    let user = sqlx::query_as!(
        User,
        "INSERT INTO users (email, name) VALUES ($1, $2) RETURNING id, email, name, created_at",
        email,
        name
    )
    .fetch_one(pool)
    .await
    .map_err(|e| match e {
        SqlxError::Database(db_err) if db_err.is_unique_violation() => {
            DatabaseError::DuplicateEmail
        }
        _ => DatabaseError::SqlxError(e),
    })?;
    
    Ok(user)
}

Configure your connection pool based on your workload. For web services, a good starting point is 2-5 connections per CPU core. Monitor your database’s connection limits and adjust accordingly.

For testing, use SQLite in-memory databases:

#[cfg(test)]
mod tests {
    use super::*;
    
    async fn setup_test_db() -> PgPool {
        let pool = PgPoolOptions::new()
            .connect("sqlite::memory:")
            .await
            .unwrap();
        
        sqlx::migrate!("./migrations")
            .run(&pool)
            .await
            .unwrap();
        
        pool
    }
    
    #[tokio::test]
    async fn test_create_user() {
        let pool = setup_test_db().await;
        let user = create_user(&pool, "test@example.com", "Test User")
            .await
            .unwrap();
        
        assert_eq!(user.email, "test@example.com");
    }
}

SQLx’s compile-time checking requires a database connection during builds. Set DATABASE_URL in your CI environment or use offline mode with cargo sqlx prepare to generate cached query metadata.

The combination of compile-time verification, async support, and raw SQL control makes SQLx the best choice for Rust applications that need reliable, performant database access without sacrificing type safety.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.