Database Integration - Setup with sqlx

1. Welcome and Icebreaker:

What database do you have the most experience with, and what do you like/dislike about it?

2. Outline the Session:

Agenda:

1. Concept Explanation: Integrating a database with Axum using sqlx.
2. Live Coding: Set up a database connection pool and add it to app state.
3. Tasks: Practice by connecting to your own database.

Learning Outcomes:

• Set up sqlx and connect your Axum app to a database.
• Add the connection pool to shared state.

Expectations: Try connecting to a local or cloud database and test the connection!

3. Concept Explanation:

sqlx is an async SQL crate for Rust; query!/query_as! offer compile-time checking when provided a database schema (or offline metadata), while query is checked at runtime. You can use it with PostgreSQL, MySQL, or SQLite. In Axum, you typically share a connection pool (e.g., PgPool) via AppState.

Add sqlx = { version = "0.8", features = ["runtime-tokio", "tls-rustls-ring", "postgres"] } alongside Axum 0.8 and Tokio 1. Set DATABASE_URL to your PostgreSQL URL before running; never commit credentials.

4. Live Coding Demonstration:

use axum::{extract::State, Router, routing::get};
use sqlx::PgPool;
use std::sync::Arc;

struct AppState {
    db: PgPool,
}

async fn health(State(state): State<Arc<AppState>>) -> &'static str {
    if sqlx::query("SELECT 1").execute(&state.db).await.is_ok() {
        "Database OK"
    } else {
        "Database Error"
    }
}

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let url = std::env::var("DATABASE_URL")?;
    let db = PgPool::connect(&url).await?;
    let state = Arc::new(AppState { db });
    let app = Router::new()
        .route("/health", get(health))
        .with_state(state);
    let listener = tokio::net::TcpListener::bind("127.0.0.1:3000").await?;
    axum::serve(listener, app).await?;
    Ok(())
}

Best Practices:

• Use environment variables for database URLs.
• Use connection pools for efficiency.

Common Pitfalls:

• Not handling connection errors.
• Forgetting to add the pool to app state.

5. Tasks

• Set up a SQLite or MySQL pool instead of PostgreSQL.
• Add a /version endpoint that queries the database version.
• Try running a migration using sqlx-cli.