database

🗄️ RUST DATABASE BEST PRACTICES

TL;DR: Comprehensive guidelines for database access in Rust using SQLx, focusing on type safety, async patterns, and testing strategies.

🔍 DATABASE ARCHITECTURE STRATEGY

graph TD
    Start["Database Integration"] --> DBChoice{"Database<br>Type?"}

    DBChoice -->|PostgreSQL| Postgres["PostgreSQL with SQLx"]
    DBChoice -->|SQLite| SQLite["SQLite with SQLx"]
    DBChoice -->|Multiple| MultiDB["Multi-Database Support"]

    Postgres --> ConnectionPool["Connection Pool Setup"]
    SQLite --> ConnectionPool
    MultiDB --> ConnectionPool

    ConnectionPool --> Migrations["Database Migrations"]
    Migrations --> EntityDesign["Entity Design"]

    EntityDesign --> RepositoryPattern["Repository Pattern"]
    RepositoryPattern --> QueryPatterns["Query Patterns"]

    QueryPatterns --> TypeSafety["Type Safety"]
    TypeSafety --> ErrorHandling["Error Handling"]

    ErrorHandling --> Testing["Database Testing"]
    Testing --> Transactions["Transaction Management"]
    Transactions --> Performance["Performance Optimization"]

    Performance --> Production["Production Database"]

    style Start fill:#4da6ff,stroke:#0066cc,color:white
    style ConnectionPool fill:#4dbb5f,stroke:#36873f,color:white
    style RepositoryPattern fill:#ffa64d,stroke:#cc7a30,color:white
    style Testing fill:#d94dbb,stroke:#a3378a,color:white

🎯 DATABASE LIBRARY SELECTION

SQLx as the Standard

• Always use SQLx - avoid rusqlite, tokio-postgres, or other lower-level libraries
• Support multiple databases through SQLx's unified interface
• Leverage compile-time query checking when possible
• Use async/await patterns for all database operations

# Cargo.toml - SQLx configuration
[dependencies]
sqlx = { version = "0.8", features = [
    "chrono",
    "postgres",
    "runtime-tokio-rustls",
    "sqlite",
    "uuid"
] }

🔧 QUERY PATTERNS

Use sqlx::query_as Instead of Macros

// ✅ Preferred: Use sqlx::query_as with custom types
#[derive(Debug, Clone, sqlx::FromRow, Serialize, Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct User {
    pub id: Uuid,
    pub username: String,
    pub email: String,
    pub created_at: DateTime<Utc>,
    pub updated_at: DateTime<Utc>,
    pub is_active: bool,
}

impl User {
    pub async fn find_by_id(
        pool: &PgPool,
        id: Uuid
    ) -> Result<Option<Self>, sqlx::Error> {
        sqlx::query_as::<_, User>(
            "SELECT id, username, email, created_at, updated_at, is_active
             FROM users
             WHERE id = $1"
        )
        .bind(id)
        .fetch_optional(pool)
        .await
    }

    pub async fn list_active_users(
        pool: &PgPool,
        limit: i64,
        offset: i64,
    ) -> Result<Vec<Self>, sqlx::Error> {
        sqlx::query_as::<_, User>(
            "SELECT id, username, email, created_at, updated_at, is_active
             FROM users
             WHERE is_active = true
             ORDER BY created_at DESC
             LIMIT $1 OFFSET $2"
        )
        .bind(limit)
        .bind(offset)
        .fetch_all(pool)
        .await
    }
}

// ❌ Avoid: sqlx::query! macro (compile-time dependencies)
// let result = sqlx::query!("SELECT * FROM users WHERE id = $1", user_id)
//     .fetch_one(pool)
//     .await?;