Surkyl Server - Stack Overview

SQLx + Axum + Rust One-Pager

Stack Versions: Axum 0.8.6 | SQLx 0.8.2 | Rust 1.90+

Overview

SQLx is a pure Rust SQL toolkit with compile-time checked queries, async support, and no ORM overhead. Axum is a web framework built on tokio/tower with excellent ergonomics and type safety. Together they provide a performant, type-safe backend stack.

Key Features

SQLx

• Compile-time verification: Queries checked against your database schema at compile time
• Database agnostic: Supports PostgreSQL, MySQL, SQLite, MSSQL
• Async-first: Built on tokio for non-blocking I/O
• Zero-cost abstraction: No runtime overhead from macros
• Transactions: Full ACID support with nested transactions
• Connection pooling: Built-in with PgPool/MySqlPool/SqlitePool

Axum 0.8

• Extractors: Type-safe request parsing (Path, Query, Json, State, etc.)
• Tower integration: Middleware, rate limiting, timeouts via tower
• Minimal boilerplate: Handlers are just async functions
• Type-safe routing: Compile-time route validation
• WebSocket support: First-class async WebSocket handling

Basic Setup

[dependencies]
axum = "0.8.6"
tokio = { version = "1", features = ["full"] }
sqlx = { version = "0.8.2", features = ["runtime-tokio", "postgres", "macros", "migrate"] }
tower = "0.5"
tower-http = { version = "0.6", features = ["trace", "cors"] }
serde = { version = "1", features = ["derive"] }

Connection Pool Pattern

use sqlx::postgres::PgPool;
use axum::{Router, extract::State};

#[derive(Clone)]
struct AppState {
    db: PgPool,
}

#[tokio::main]
async fn main() {
    // Create connection pool
    let database_url = std::env::var("DATABASE_URL")
        .expect("DATABASE_URL must be set");

    let pool = PgPool::connect(&database_url)
        .await
        .expect("Failed to connect to database");

    let state = AppState { db: pool };

    let app = Router::new()
        .route("/users", get(list_users))
        .with_state(state);

    let listener = tokio::net::TcpListener::bind("0.0.0.0:3000")
        .await
        .unwrap();

    axum::serve(listener, app).await.unwrap();
}

Query Patterns

Compile-Time Checked Queries

use sqlx::query_as;
use axum::{extract::State, Json};

#[derive(sqlx::FromRow, serde::Serialize)]
struct User {
    id: i32,
    email: String,
    name: String,
}

async fn list_users(
    State(state): State<AppState>
) -> Result<Json<Vec<User>>, (StatusCode, String)> {
    // Checked at compile time with DATABASE_URL set
    let users = sqlx::query_as!(
        User,
        "SELECT id, email, name FROM users WHERE active = true"
    )
    .fetch_all(&state.db)
    .await
    .map_err(|e| (StatusCode::INTERNAL_SERVER_ERROR, e.to_string()))?;

    Ok(Json(users))
}

Runtime Queries (Dynamic SQL)

async fn dynamic_query(
    State(state): State<AppState>,
    Query(params): Query<SearchParams>
) -> Result<Json<Vec<User>>, StatusCode> {
    let mut query = "SELECT id, email, name FROM users WHERE 1=1".to_string();

    if let Some(email) = &params.email {
        query.push_str(" AND email LIKE $1");
    }

    let users = sqlx::query_as::<_, User>(&query)
        .bind(format!("%{}%", params.email.unwrap_or_default()))
        .fetch_all(&state.db)
        .await
        .map_err(|_| StatusCode::INTERNAL_SERVER_ERROR)?;

    Ok(Json(users))
}

Transactions

use sqlx::{Postgres, Transaction};

async fn create_user_with_profile(
    State(state): State<AppState>,
    Json(payload): Json<CreateUserRequest>
) -> Result<StatusCode, StatusCode> {
    let mut tx: Transaction<Postgres> = state.db.begin().await
        .map_err(|_| StatusCode::INTERNAL_SERVER_ERROR)?;

    let user_id = sqlx::query_scalar!(
        "INSERT INTO users (email, name) VALUES ($1, $2) RETURNING id",
        payload.email,
        payload.name
    )
    .fetch_one(&mut *tx)
    .await
    .map_err(|_| StatusCode::INTERNAL_SERVER_ERROR)?;

    sqlx::query!(
        "INSERT INTO profiles (user_id, bio) VALUES ($1, $2)",
        user_id,
        payload.bio
    )
    .execute(&mut *tx)
    .await
    .map_err(|_| StatusCode::INTERNAL_SERVER_ERROR)?;

    tx.commit().await
        .map_err(|_| StatusCode::INTERNAL_SERVER_ERROR)?;

    Ok(StatusCode::CREATED)
}

Migrations

# Install SQLx CLI
cargo install sqlx-cli --no-default-features --features postgres

# Create migration
sqlx migrate add create_users_table # nx run surkyl-server:db:migrations -- add create_users_table

# Run migrations
sqlx migrate run # nx run surkyl-server:db:migrate

# Revert last migration
sqlx migrate revert # nx run surkyl-server:db:migrations -- revert

Example migration file (migrations/XXXXXX_create_users_table.sql):

-- Add migration script here
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    active BOOLEAN DEFAULT true
);

CREATE INDEX idx_users_email ON users(email);

Error Handling Pattern

use axum::{
    http::StatusCode,
    response::{IntoResponse, Response},
    Json,
};
use serde_json::json;

enum ApiError {
    DatabaseError(sqlx::Error),
    NotFound,
    ValidationError(String),
}

impl IntoResponse for ApiError {
    fn into_response(self) -> Response {
        let (status, message) = match self {
            ApiError::DatabaseError(e) => {
                tracing::error!("Database error: {:?}", e);
                (StatusCode::INTERNAL_SERVER_ERROR, "Internal server error")
            }
            ApiError::NotFound => (StatusCode::NOT_FOUND, "Resource not found"),
            ApiError::ValidationError(msg) => (StatusCode::BAD_REQUEST, msg.as_str()),
        };

        (status, Json(json!({ "error": message }))).into_response()
    }
}

impl From<sqlx::Error> for ApiError {
    fn from(e: sqlx::Error) -> Self {
        match e {
            sqlx::Error::RowNotFound => ApiError::NotFound,
            _ => ApiError::DatabaseError(e),
        }
    }
}

Multi-Tenancy Pattern (for Surkyl)

// Row-Level Security with tenant_id
async fn get_tenant_data(
    State(state): State<AppState>,
    Path(tenant_id): Path<Uuid>,
    Extension(user): Extension<AuthUser>,
) -> Result<Json<Vec<Data>>, ApiError> {
    // Verify user has access to tenant
    let has_access = sqlx::query_scalar!(
        "SELECT EXISTS(SELECT 1 FROM tenant_users WHERE tenant_id = $1 AND user_id = $2)",
        tenant_id,
        user.id
    )
    .fetch_one(&state.db)
    .await?
    .unwrap_or(false);

    if !has_access {
        return Err(ApiError::NotFound);
    }

    let data = sqlx::query_as!(
        Data,
        "SELECT * FROM data WHERE tenant_id = $1",
        tenant_id
    )
    .fetch_all(&state.db)
    .await?;

    Ok(Json(data))
}

Performance Tips

1. Connection Pool Sizing: max_connections should be CPU cores * 2-4
2. Prepared Statements: SQLx caches them automatically for query!() and query_as!()
3. Batch Operations: Use fetch_all() instead of multiple fetch_one() calls
4. Indexes: Add indexes for WHERE, JOIN, ORDER BY columns
5. Query Logging: Enable with RUST_LOG=sqlx=debug for slow query analysis
6. Compile-Time Checks: Set DATABASE_URL at build time or use sqlx prepare offline mode

Offline Mode (for CI/CD)

# Generate query metadata
cargo sqlx prepare

# Check in sqlx-data.json to git
git add .sqlx/

# Build without database connection
cargo build

Add to Cargo.toml:

[dependencies]
sqlx = { version = "0.8.2", features = ["runtime-tokio", "postgres", "macros", "migrate", "offline"] }

Common Gotchas

1. Compile-time checks fail: Ensure DATABASE_URL is set and database is running
2. Connection exhaustion: Always use connection pool, not individual connections
3. Transaction deadlocks: Keep transactions short, acquire locks in consistent order
4. Type mismatches: SQLx is strict about NULL handling - use Option<T> for nullable columns
5. Prepared statement limit: PostgreSQL has a limit of ~64K prepared statements per connection

Testing

#[cfg(test)]
mod tests {
    use super::*;
    use sqlx::postgres::PgPoolOptions;

    #[tokio::test]
    async fn test_create_user() {
        let pool = PgPoolOptions::new()
            .max_connections(1)
            .connect(&std::env::var("TEST_DATABASE_URL").unwrap())
            .await
            .unwrap();

        // Run migrations
        sqlx::migrate!().run(&pool).await.unwrap();

        // Test code here
        let user = create_user(&pool, "[email protected]").await.unwrap();
        assert_eq!(user.email, "[email protected]");

        // Cleanup
        sqlx::query!("DELETE FROM users WHERE id = $1", user.id)
            .execute(&pool)
            .await
            .unwrap();
    }
}

Resources

• SQLx Docs: https://docs.rs/sqlx/0.8.2/sqlx/
• Axum Docs: https://docs.rs/axum/0.8.6/axum/
• Axum Examples: https://github.com/tokio-rs/axum/tree/main/examples
• SQLx GitHub: https://github.com/launchbadge/sqlx