Rust SQLx 2026完全ガイド: コンパイル時クエリ検証とデータベース面接対策

[dependencies]
sqlx = { version = "0.8", features = ["runtime-tokio", "postgres", "macros"] }
tokio = { version = "1", features = ["full"] }
dotenvy = "0.15"

[dev-dependencies]
sqlx-cli = "0.8"

DATABASE_URL=postgres://user:password@localhost:5432/myapp

cargo install sqlx-cli --no-default-features --features postgres
sqlx database create
sqlx migrate add create_users_table

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

#[derive(Debug, FromRow)]
struct User {
    id: i32,
    email: String,
    name: Option<String>,
    created_at: chrono::DateTime<chrono::Utc>,
}

async fn get_user_by_id(pool: &sqlx::PgPool, user_id: i32) -> Result<Option<User>, sqlx::Error> {
    let user = sqlx::query_as!(
        User,
        r#"
        SELECT id, email, name, created_at
        FROM users
        WHERE id = $1
        "#,
        user_id
    )
    .fetch_optional(pool)
    .await?;

    Ok(user)
}

cargo sqlx prepare

// オフラインモードでも同じコードが動作する
let users = sqlx::query_as!(
    User,
    "SELECT id, email, name, created_at FROM users WHERE active = true"
)
.fetch_all(pool)
.await?;

async fn transfer_funds(
    pool: &sqlx::PgPool,
    from_account: i32,
    to_account: i32,
    amount: i64,
) -> Result<(), sqlx::Error> {
    let mut tx = pool.begin().await?;

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

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

    tx.commit().await?;
    Ok(())
}

-- migrations/20260912000001_create_users.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100),
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);

use sqlx::migrate::MigrateDatabase;

async fn run_migrations(pool: &sqlx::PgPool) -> Result<(), sqlx::Error> {
    sqlx::migrate!("./migrations")
        .run(pool)
        .await?;
    Ok(())
}

use sqlx::QueryBuilder;

async fn search_users(
    pool: &sqlx::PgPool,
    name_filter: Option<&str>,
    email_filter: Option<&str>,
    limit: i64,
) -> Result<Vec<User>, sqlx::Error> {
    let mut builder: QueryBuilder<sqlx::Postgres> = QueryBuilder::new(
        "SELECT id, email, name, created_at FROM users WHERE 1=1"
    );

    if let Some(name) = name_filter {
        builder.push(" AND name ILIKE ");
        builder.push_bind(format!("%{}%", name));
    }

    if let Some(email) = email_filter {
        builder.push(" AND email ILIKE ");
        builder.push_bind(format!("%{}%", email));
    }

    builder.push(" ORDER BY created_at DESC LIMIT ");
    builder.push_bind(limit);

    let query = builder.build_query_as::<User>();
    let users = query.fetch_all(pool).await?;

    Ok(users)
}

// SQLx: 生SQL + コンパイル時検証
let user = sqlx::query_as!(User, "SELECT * FROM users WHERE id = $1", id)
    .fetch_one(pool).await?;

// Diesel: Rust DSL
let user = users::table
    .filter(users::id.eq(id))
    .first::<User>(conn)?;

// SeaORM: ActiveRecordパターン
let user = User::find_by_id(id).one(db).await?;

let pool = PgPoolOptions::new()
    .max_connections(20)
    .min_connections(5)
    .acquire_timeout(std::time::Duration::from_secs(3))
    .idle_timeout(std::time::Duration::from_secs(600))
    .connect(&database_url)
    .await?;

use sqlx::Error as SqlxError;

fn handle_db_error(err: SqlxError) -> AppError {
    match err {
        SqlxError::RowNotFound => AppError::NotFound,
        SqlxError::Database(db_err) => {
            if db_err.is_unique_violation() {
                AppError::Conflict("Resource already exists".into())
            } else {
                AppError::Internal(db_err.to_string())
            }
        }
        _ => AppError::Internal(err.to_string()),
    }
}