Rust Backend Database

2. Connection Pooling is Mandatory - Never create individual connections per request.

3. Migrations are Code - Treat migrations as first-class code. Version control, review, test.

4. Choose the Right Tool

• SQLx: Simple to medium complexity, direct SQL control
• SeaORM: Complex relations, dynamic queries, ActiveRecord patterns

use sqlx::postgres::PgPoolOptions;

pub async fn create_pool(database_url: &str) -> Result<PgPool, sqlx::Error> {
    PgPoolOptions::new()
        .max_connections(10)
        .min_connections(2)
        .acquire_timeout(Duration::from_secs(3))
        .max_lifetime(Duration::from_secs(30 * 60))
        .connect(database_url)
        .await
}

#[derive(FromRow)]
struct User {
    id: i64,
    email: String,
    name: Option<String>,
}

async fn get_user(pool: &PgPool, id: i64) -> Result<User, sqlx::Error> {
    sqlx::query_as!(User, "SELECT id, email, name FROM users WHERE id = $1", id)
        .fetch_one(pool)
        .await
}

let mut tx = pool.begin().await?;

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

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

tx.commit().await?;

// GOOD: Smart Entity Loader
let users_with_posts = user::Entity::load()
    .filter(user::Column::Active.eq(true))
    .with(post::Entity)           // 1-N: uses data loader
    .with(profile::Entity)        // 1-1: uses JOIN
    .all(&db)
    .await?;