Rust & Databases

// Cargo.toml:
// sqlx = { version = "0.8", features = ["runtime-tokio", "postgres", "macros"] }
// tokio = { version = "1", features = ["full"] }

use sqlx::postgres::PgPoolOptions;
use sqlx::FromRow;

#[derive(Debug, FromRow)]
struct User {
    id: i64,
    name: String,
    email: String,
    active: bool,
}

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

    // Compile-time checked query!
    // sqlx checks this against the actual database schema
    let users = sqlx::query_as::<_, User>(
        "SELECT id, name, email, active FROM users WHERE active = $1"
    )
    .bind(true)
    .fetch_all(&pool)
    .await?;

    for user in &users {
        println!("{}: {} ({})", user.id, user.name, user.email);
    }

    // Insert with returning
    let new_user = sqlx::query_as::<_, User>(
        "INSERT INTO users (name, email, active) VALUES ($1, $2, true) RETURNING *"
    )
    .bind("Alice")
    .bind("alice@example.com")
    .fetch_one(&pool)
    .await?;

    println!("Created: {:?}", new_user);

    // Using the query! macro for even stronger compile-time checks
    // let user = sqlx::query!("SELECT id, name FROM users WHERE id = $1", 1i64)
    //     .fetch_one(&pool)
    //     .await?;
    // println!("{}: {}", user.id, user.name);

    Ok(())
}

use sqlx::PgPool;

async fn transfer_funds(
    pool: &PgPool,
    from_id: i64,
    to_id: i64,
    amount: f64,
) -> Result<(), sqlx::Error> {
    // Start a transaction
    let mut tx = pool.begin().await?;

    // Debit
    sqlx::query("UPDATE accounts SET balance = balance - $1 WHERE id = $2")
        .bind(amount)
        .bind(from_id)
        .execute(&mut *tx)
        .await?;

    // Credit
    sqlx::query("UPDATE accounts SET balance = balance + $1 WHERE id = $2")
        .bind(amount)
        .bind(to_id)
        .execute(&mut *tx)
        .await?;

    // Commit (rolls back on drop if not committed)
    tx.commit().await?;

    Ok(())
}

# Install SQLx CLI
cargo install sqlx-cli

# Create a migration
sqlx migrate add create_users_table

# migrations/20240101000000_create_users_table.sql:
# CREATE TABLE users (
#     id BIGSERIAL PRIMARY KEY,
#     name VARCHAR(255) NOT NULL,
#     email VARCHAR(255) UNIQUE NOT NULL,
#     active BOOLEAN DEFAULT true,
#     created_at TIMESTAMPTZ DEFAULT NOW()
# );

# Run migrations
sqlx migrate run

# Revert last migration
sqlx migrate revert

# Check migration status
sqlx migrate info

// Cargo.toml:
// diesel = { version = "2", features = ["postgres"] }

use diesel::prelude::*;

// Diesel uses schema macros generated from your database
// diesel::table! {
//     users (id) {
//         id -> Int8,
//         name -> Varchar,
//         email -> Varchar,
//         active -> Bool,
//     }
// }

#[derive(Queryable, Debug)]
struct User {
    id: i64,
    name: String,
    email: String,
    active: bool,
}

#[derive(Insertable)]
#[diesel(table_name = users)]
struct NewUser<'a> {
    name: &'a str,
    email: &'a str,
}

fn find_active_users(conn: &mut PgConnection) -> QueryResult> {
    use self::users::dsl::*;

    users
        .filter(active.eq(true))
        .order(name.asc())
        .limit(10)
        .load::(conn)
}

fn create_user(conn: &mut PgConnection, user_name: &str, user_email: &str) -> QueryResult {
    use self::users::dsl::*;

    let new_user = NewUser {
        name: user_name,
        email: user_email,
    };

    diesel::insert_into(users)
        .values(&new_user)
        .get_result(conn)
}

mod users {
    diesel::table! {
        users (id) {
            id -> Int8,
            name -> Varchar,
            email -> Varchar,
            active -> Bool,
        }
    }
}

// SeaORM is fully async and works with tokio
// Cargo.toml: sea-orm = { version = "1", features = ["sqlx-postgres", "runtime-tokio-rustls"] }

// SeaORM generates entity files from your database
// sea-orm-cli generate entity -o src/entities

// Example query with SeaORM:
// use sea_orm::*;
// use entity::users;
//
// async fn find_users(db: &DatabaseConnection) -> Result, DbErr> {
//     users::Entity::find()
//         .filter(users::Column::Active.eq(true))
//         .order_by_asc(users::Column::Name)
//         .all(db)
//         .await
// }
//
// async fn create_user(db: &DatabaseConnection, name: &str, email: &str) -> Result {
//     let user = users::ActiveModel {
//         name: Set(name.to_owned()),
//         email: Set(email.to_owned()),
//         active: Set(true),
//         ..Default::default()
//     };
//     user.insert(db).await
// }

// Choosing a database library:
// SQLx:    Raw SQL with compile-time checks. Best for complex queries.
// Diesel:  Full ORM, sync-first. Best for type-safe query building.
// SeaORM:  Async ORM. Best for async web apps (Axum/Actix).