Library

SQLx

Overview

SQLx is "The Rust SQL Toolkit" developed as an async, pure Rust SQL crate featuring compile-time checked queries without a DSL. Supporting PostgreSQL, MySQL, and SQLite with the concept of "Rust-like safety and performance," it achieves both type safety and SQL's native expressiveness. Providing comprehensive features necessary for full-scale web application development including compile-time query verification, connection pooling, migrations, and SQL injection prevention, it's a modern database library.

Details

SQLx 2025 edition has established a solid position as the standard database operations library in the Rust ecosystem. Through its unique macro system (query!, query_as!), it verifies SQL queries at compile time, ensuring type safety while leveraging SQL's complete expressiveness. Supporting Tokio/async-std async runtimes, TLS encrypted communication (rustls, native-tls), and continuous connection pooling, it's equipped with features required for production environments. Rich features including offline build mode, prepared statements, and transaction management enable safe and high-performance database access.

Key Features

• Compile-time Query Verification: Validates SQL syntax, types, and schema at build time
• Async First: Complete Tokio/async-std support for true asynchronous processing
• Multi-DB Support: Unified API for PostgreSQL, MySQL, SQLite
• Type Safety: Complete integration of Rust's type system with SQL schema
• Production Ready: Connection pooling, TLS, migrations, and audit functionality
• DSL-Free: Maintains raw SQL while ensuring safety

Pros and Cons

Pros

• Compile-time query verification significantly reduces runtime errors
• Complete integration of Rust's type system with SQL improves development efficiency
• Scalable high-performance database access through asynchronous processing
• Can maintain raw SQL allowing free description of complex queries
• Enterprise-grade features with connection pooling and TLS support
• Active development community and rich documentation

Cons

• Requires database connection at build time (can be avoided with offline mode)
• Deep SQL knowledge required, high learning cost for ORM beginners
• Compile time slightly increases due to query verification
• May require extensive code modifications when schema changes
• Complex ORM features (relationship management, etc.) require custom implementation
• IDE support may be limited due to macro-based approach

Reference Pages

• SQLx GitHub Repository
• SQLx Official Documentation
• SQLx CLI Tool

Code Examples

Installation and Basic Setup

# Cargo.toml
[dependencies]
sqlx = { version = "0.8", features = [
    "runtime-tokio-rustls",  # Tokio runtime + Rustls TLS
    "postgres",              # PostgreSQL support
    "mysql",                 # MySQL support  
    "sqlite",                # SQLite support
    "macros",                # Compile-time macros
    "migrate"                # Migration support
] }
tokio = { version = "1.0", features = ["full"] }

# Environment variables (for compile-time verification)
# .env
DATABASE_URL=postgres://user:password@localhost/my_database

# Alternative configurations
# DATABASE_URL=mysql://user:password@localhost/my_database
# DATABASE_URL=sqlite:database.db

// Basic imports
use sqlx::{PgPool, Row};
use sqlx::postgres::PgPoolOptions;
// For MySQL: use sqlx::mysql::{MySqlPool, MySqlPoolOptions};
// For SQLite: use sqlx::sqlite::{SqlitePool, SqlitePoolOptions};

// Basic connection setup
#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    // Create connection pool
    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect("postgres://user:password@localhost/database")
        .await?;

    // Execute basic query
    let row: (i64,) = sqlx::query_as("SELECT $1")
        .bind(150_i64)
        .fetch_one(&pool)
        .await?;

    assert_eq!(row.0, 150);
    Ok(())
}

Basic CRUD Operations

use sqlx::{PgPool, FromRow};
use serde::{Deserialize, Serialize};

// User struct
#[derive(Debug, FromRow, Serialize, Deserialize)]
struct User {
    id: i32,
    name: String,
    email: String,
    age: Option<i32>,
    created_at: chrono::DateTime<chrono::Utc>,
}

// User creation struct
#[derive(Debug, Deserialize)]
struct CreateUser {
    name: String,
    email: String,
    age: Option<i32>,
}

// Database operations struct
struct UserRepository {
    pool: PgPool,
}

impl UserRepository {
    pub fn new(pool: PgPool) -> Self {
        Self { pool }
    }

    // Create user
    pub async fn create_user(&self, user: CreateUser) -> Result<User, sqlx::Error> {
        let user = sqlx::query_as!(
            User,
            r#"
            INSERT INTO users (name, email, age)
            VALUES ($1, $2, $3)
            RETURNING id, name, email, age, created_at
            "#,
            user.name,
            user.email,
            user.age
        )
        .fetch_one(&self.pool)
        .await?;

        Ok(user)
    }

    // Get user by ID
    pub async fn get_user_by_id(&self, id: i32) -> Result<Option<User>, sqlx::Error> {
        let user = sqlx::query_as!(
            User,
            "SELECT id, name, email, age, created_at FROM users WHERE id = $1",
            id
        )
        .fetch_optional(&self.pool)
        .await?;

        Ok(user)
    }

    // Get all users
    pub async fn get_all_users(&self) -> Result<Vec<User>, sqlx::Error> {
        let users = sqlx::query_as!(
            User,
            "SELECT id, name, email, age, created_at FROM users ORDER BY created_at DESC"
        )
        .fetch_all(&self.pool)
        .await?;

        Ok(users)
    }

    // Update user
    pub async fn update_user(
        &self,
        id: i32,
        name: Option<String>,
        email: Option<String>,
        age: Option<i32>,
    ) -> Result<Option<User>, sqlx::Error> {
        let user = sqlx::query_as!(
            User,
            r#"
            UPDATE users 
            SET name = COALESCE($2, name),
                email = COALESCE($3, email),
                age = COALESCE($4, age)
            WHERE id = $1
            RETURNING id, name, email, age, created_at
            "#,
            id,
            name,
            email,
            age
        )
        .fetch_optional(&self.pool)
        .await?;

        Ok(user)
    }

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

        Ok(result.rows_affected() > 0)
    }
}

// Usage example
#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let pool = PgPoolOptions::new()
        .max_connections(10)
        .connect(&std::env::var("DATABASE_URL")?)
        .await?;

    let repo = UserRepository::new(pool);

    // Create user
    let new_user = CreateUser {
        name: "John Doe".to_string(),
        email: "[email protected]".to_string(),
        age: Some(30),
    };

    let user = repo.create_user(new_user).await?;
    println!("Created user: {:?}", user);

    // Get user
    if let Some(found_user) = repo.get_user_by_id(user.id).await? {
        println!("Found user: {:?}", found_user);
    }

    // Get all users
    let all_users = repo.get_all_users().await?;
    println!("All users: {:?}", all_users);

    Ok(())
}

Advanced Queries and Transactions

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

struct AdvancedQueries {
    pool: PgPool,
}

impl AdvancedQueries {
    pub fn new(pool: PgPool) -> Self {
        Self { pool }
    }

    // Complex conditional search
    pub async fn search_users(
        &self,
        name_pattern: Option<&str>,
        min_age: Option<i32>,
        max_age: Option<i32>,
        limit: Option<i64>,
        offset: Option<i64>,
    ) -> Result<Vec<User>, sqlx::Error> {
        let mut query = String::from(
            "SELECT id, name, email, age, created_at FROM users WHERE 1=1"
        );
        let mut bind_count = 0;

        if name_pattern.is_some() {
            bind_count += 1;
            query.push_str(&format!(" AND name ILIKE ${}", bind_count));
        }

        if min_age.is_some() {
            bind_count += 1;
            query.push_str(&format!(" AND age >= ${}", bind_count));
        }

        if max_age.is_some() {
            bind_count += 1;
            query.push_str(&format!(" AND age <= ${}", bind_count));
        }

        query.push_str(" ORDER BY created_at DESC");

        if limit.is_some() {
            bind_count += 1;
            query.push_str(&format!(" LIMIT ${}", bind_count));
        }

        if offset.is_some() {
            bind_count += 1;
            query.push_str(&format!(" OFFSET ${}", bind_count));
        }

        let mut query_builder = sqlx::query_as::<_, User>(&query);

        if let Some(pattern) = name_pattern {
            query_builder = query_builder.bind(format!("%{}%", pattern));
        }
        if let Some(min) = min_age {
            query_builder = query_builder.bind(min);
        }
        if let Some(max) = max_age {
            query_builder = query_builder.bind(max);
        }
        if let Some(lim) = limit {
            query_builder = query_builder.bind(lim);
        }
        if let Some(off) = offset {
            query_builder = query_builder.bind(off);
        }

        let users = query_builder.fetch_all(&self.pool).await?;
        Ok(users)
    }

    // Bulk operations using PostgreSQL arrays
    pub async fn bulk_insert_users(
        &self,
        names: Vec<String>,
        emails: Vec<String>,
    ) -> Result<Vec<User>, sqlx::Error> {
        let users = sqlx::query_as!(
            User,
            r#"
            INSERT INTO users (name, email)
            SELECT * FROM UNNEST($1::text[], $2::text[])
            RETURNING id, name, email, age, created_at
            "#,
            &names[..],
            &emails[..]
        )
        .fetch_all(&self.pool)
        .await?;

        Ok(users)
    }

    // Aggregate queries
    pub async fn get_user_statistics(&self) -> Result<UserStats, sqlx::Error> {
        let stats = sqlx::query_as!(
            UserStats,
            r#"
            SELECT 
                COUNT(*) as total_users,
                AVG(age) as average_age,
                MIN(age) as min_age,
                MAX(age) as max_age,
                COUNT(*) FILTER (WHERE age >= 18) as adult_users
            FROM users
            "#
        )
        .fetch_one(&self.pool)
        .await?;

        Ok(stats)
    }

    // Transaction processing
    pub async fn transfer_user_data(
        &self,
        from_user_id: i32,
        to_user_id: i32,
        amount: i32,
    ) -> Result<(), sqlx::Error> {
        let mut tx = self.pool.begin().await?;

        // Subtract from source user
        sqlx::query!(
            "UPDATE user_balances SET balance = balance - $1 WHERE user_id = $2",
            amount,
            from_user_id
        )
        .execute(&mut *tx)
        .await?;

        // Add to destination user
        sqlx::query!(
            "UPDATE user_balances SET balance = balance + $1 WHERE user_id = $2",
            amount,
            to_user_id
        )
        .execute(&mut *tx)
        .await?;

        // Balance check
        let balance: (i32,) = sqlx::query_as(
            "SELECT balance FROM user_balances WHERE user_id = $1"
        )
        .bind(from_user_id)
        .fetch_one(&mut *tx)
        .await?;

        if balance.0 < 0 {
            return Err(sqlx::Error::RowNotFound);
        }

        tx.commit().await?;
        Ok(())
    }
}

#[derive(Debug, sqlx::FromRow)]
struct UserStats {
    total_users: Option<i64>,
    average_age: Option<f64>,
    min_age: Option<i32>,
    max_age: Option<i32>,
    adult_users: Option<i64>,
}

Migrations and Schema Management

// migration.rs
use sqlx::migrate::MigrateDatabase;
use sqlx::PgPool;

pub struct MigrationManager {
    database_url: String,
}

impl MigrationManager {
    pub fn new(database_url: String) -> Self {
        Self { database_url }
    }

    // Create database
    pub async fn create_database(&self) -> Result<(), sqlx::Error> {
        if !sqlx::Postgres::database_exists(&self.database_url).await? {
            sqlx::Postgres::create_database(&self.database_url).await?;
            println!("Database created successfully");
        } else {
            println!("Database already exists");
        }
        Ok(())
    }

    // Drop database
    pub async fn drop_database(&self) -> Result<(), sqlx::Error> {
        if sqlx::Postgres::database_exists(&self.database_url).await? {
            sqlx::Postgres::drop_database(&self.database_url).await?;
            println!("Database dropped successfully");
        }
        Ok(())
    }

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

// Migration file example
// migrations/001_create_users_table.sql
/*
-- Up migration
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INTEGER,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_age ON users(age);

-- Down migration (in separate .down.sql file)
DROP TABLE IF EXISTS users;
*/

Connection Pooling and Configuration

use sqlx::postgres::{PgConnectOptions, PgPool, PgPoolOptions, PgSslMode};
use std::time::Duration;

pub struct DatabaseConfig {
    pub host: String,
    pub port: u16,
    pub username: String,
    pub password: String,
    pub database: String,
    pub max_connections: u32,
    pub min_connections: u32,
    pub connect_timeout: Duration,
    pub idle_timeout: Duration,
    pub ssl_mode: PgSslMode,
}

impl DatabaseConfig {
    pub async fn create_pool(&self) -> Result<PgPool, sqlx::Error> {
        let connect_options = PgConnectOptions::new()
            .host(&self.host)
            .port(self.port)
            .username(&self.username)
            .password(&self.password)
            .database(&self.database)
            .ssl_mode(self.ssl_mode)
            .log_statements(log::LevelFilter::Debug);

        let pool = PgPoolOptions::new()
            .max_connections(self.max_connections)
            .min_connections(self.min_connections)
            .acquire_timeout(self.connect_timeout)
            .idle_timeout(self.idle_timeout)
            .after_connect(|conn, _| Box::pin(async move {
                // Post-connection configuration
                sqlx::query("SET application_name = 'my_app'")
                    .execute(conn)
                    .await?;
                Ok(())
            }))
            .connect_with(connect_options)
            .await?;

        Ok(pool)
    }
}

// Usage example
impl Default for DatabaseConfig {
    fn default() -> Self {
        Self {
            host: "localhost".to_string(),
            port: 5432,
            username: "postgres".to_string(),
            password: "password".to_string(),
            database: "my_app".to_string(),
            max_connections: 10,
            min_connections: 2,
            connect_timeout: Duration::from_secs(30),
            idle_timeout: Duration::from_secs(600),
            ssl_mode: PgSslMode::Prefer,
        }
    }
}

Error Handling and Logging

use sqlx::{Error as SqlxError, PgPool};
use thiserror::Error;

#[derive(Error, Debug)]
pub enum DatabaseError {
    #[error("Database connection error: {0}")]
    Connection(#[from] SqlxError),
    
    #[error("User not found with id: {id}")]
    UserNotFound { id: i32 },
    
    #[error("Email already exists: {email}")]
    EmailAlreadyExists { email: String },
    
    #[error("Validation error: {message}")]
    Validation { message: String },
}

pub struct DatabaseService {
    pool: PgPool,
}

impl DatabaseService {
    pub fn new(pool: PgPool) -> Self {
        Self { pool }
    }

    pub async fn create_user_safe(
        &self,
        name: String,
        email: String,
        age: Option<i32>,
    ) -> Result<User, DatabaseError> {
        // Validation
        if name.is_empty() {
            return Err(DatabaseError::Validation {
                message: "Name cannot be empty".to_string(),
            });
        }

        if !email.contains('@') {
            return Err(DatabaseError::Validation {
                message: "Invalid email format".to_string(),
            });
        }

        // Duplicate check
        let existing_user = sqlx::query!(
            "SELECT id FROM users WHERE email = $1",
            email
        )
        .fetch_optional(&self.pool)
        .await?;

        if existing_user.is_some() {
            return Err(DatabaseError::EmailAlreadyExists { email });
        }

        // Create user
        let user = sqlx::query_as!(
            User,
            r#"
            INSERT INTO users (name, email, age)
            VALUES ($1, $2, $3)
            RETURNING id, name, email, age, created_at
            "#,
            name,
            email,
            age
        )
        .fetch_one(&self.pool)
        .await?;

        tracing::info!("Created user: {} ({})", user.name, user.id);
        Ok(user)
    }

    pub async fn get_user_safe(&self, id: i32) -> Result<User, DatabaseError> {
        let user = sqlx::query_as!(
            User,
            "SELECT id, name, email, age, created_at FROM users WHERE id = $1",
            id
        )
        .fetch_optional(&self.pool)
        .await?
        .ok_or(DatabaseError::UserNotFound { id })?;

        Ok(user)
    }
}

// Logging setup example
pub fn setup_logging() {
    use tracing_subscriber::{layer::SubscriberExt, util::SubscriberInitExt};

    tracing_subscriber::registry()
        .with(tracing_subscriber::EnvFilter::new(
            std::env::var("RUST_LOG").unwrap_or_else(|_| "info,sqlx=debug".into()),
        ))
        .with(tracing_subscriber::fmt::layer())
        .init();
}