Rustでデータベース操作コードをテストする方法：sqlx::testの使い方を徹底解説

   let user = sqlx::query!("SELECT * FROM users WHERE id = $1", user_id)
       .fetch_one(&pool)
       .await?;

   let result = sqlx::query!("SELECT name FROM users")
       .fetch_all(&pool)
       .await?;

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

   let user: User = sqlx::query_as!(User, "SELECT id, name FROM users WHERE id = $1", 1)
       .fetch_one(&pool)
       .await?;

cargo new sqlx_test_project
cd sqlx_test_project

[dependencies]
sqlx = { version = "0.7", features = ["runtime-tokio", "postgres", "macros", "chrono"] }
tokio = { version = "1", features = ["full"] }

CREATE DATABASE test_db;

DATABASE_URL=postgres://username:password@localhost/test_db

cargo install sqlx-cli

sqlx migrate add create_users_table

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

sqlx migrate run

use sqlx::PgPool;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let database_url = std::env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    let pool = PgPool::connect(&database_url).await?;
    Ok(())
}

sqlx_test_project/
├── Cargo.toml
├── .env
└── migrations/
    └── 20240101000000_create_users_table.sql

use sqlx::{PgPool, query};
use sqlx::test;

#[test]
async fn test_insert_user(pool: PgPool) -> sqlx::Result<()> {
    // テスト用データの挿入
    query!("INSERT INTO users (name, email) VALUES ($1, $2)", "Alice", "alice@example.com")
        .execute(&pool)
        .await?;

    // データの取得と検証
    let row = query!("SELECT name, email FROM users WHERE email = $1", "alice@example.com")
        .fetch_one(&pool)
        .await?;

    assert_eq!(row.name, "Alice");
    assert_eq!(row.email, "alice@example.com");

    Ok(())
}

cargo test

use sqlx::{PgPool, postgres::PgPoolOptions};
use std::env;

async fn create_test_pool() -> PgPool {
    let database_url = env::var("DATABASE_URL").expect("DATABASE_URL must be set for tests");
    PgPoolOptions::new()
        .max_connections(5) // 最大接続数
        .connect(&database_url)
        .await
        .expect("Failed to connect to the database")
}

use sqlx::{PgPool, query};

async fn setup_test_data(pool: &PgPool) -> sqlx::Result<()> {
    // テーブルのデータをクリーンアップ
    query!("DELETE FROM users").execute(pool).await?;

    // テストデータを挿入
    query!("INSERT INTO users (name, email) VALUES ($1, $2)", "Bob", "bob@example.com")
        .execute(pool)
        .await?;

    Ok(())
}

use sqlx::PgPool;

#[sqlx::test]
async fn test_fetch_user(pool: PgPool) -> sqlx::Result<()> {
    // テストデータのセットアップ
    setup_test_data(&pool).await?;

    // データの取得と検証
    let row = sqlx::query!("SELECT name, email FROM users WHERE email = $1", "bob@example.com")
        .fetch_one(&pool)
        .await?;

    assert_eq!(row.name, "Bob");
    assert_eq!(row.email, "bob@example.com");

    Ok(())
}

cargo test

running 2 tests
test tests::test_insert_user ... ok
test tests::test_fetch_user ... ok

test result: ok. 2 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.23s

running 1 test
test tests::test_fetch_user ... FAILED

failures:

---- tests::test_fetch_user stdout ----
thread 'tests::test_fetch_user' panicked at 'assertion failed: `(left == right)`
  left: `"Alice"`,
 right: `"Bob"`', src/tests.rs:20:9
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

failures:
    tests::test_fetch_user

test result: FAILED. 0 passed; 1 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.18s

  let row = sqlx::query!("SELECT name FROM users WHERE email = $1", "bob@example.com")
      .fetch_one(&pool)
      .await?;
  dbg!(&row);

  RUST_BACKTRACE=1 cargo test

Error: failed to connect to database: could not translate host name "localhost" to address

   DATABASE_URL=postgres://username:password@localhost/test_db

   sudo service postgresql start

relation "users" does not exist

   sqlx migrate run

   CREATE TABLE users (
       id SERIAL PRIMARY KEY,
       name TEXT NOT NULL,
       email TEXT NOT NULL UNIQUE
   );

error[E0277]: the trait bound `i32: FromRow` is not satisfied

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

   let user = sqlx::query_as!(User, "SELECT id, name FROM users WHERE id = $1", 1)
       .fetch_one(&pool)
       .await?;

there is no reactor running, must be called from the context of a Tokio 1.x runtime

   [dependencies]
   tokio = { version = "1", features = ["full"] }

   #[tokio::test]
   async fn test_example() {
       // テストコード
   }

duplicate key value violates unique constraint "users_email_key"

   sqlx::query!("DELETE FROM users").execute(&pool).await?;

   RUST_BACKTRACE=1 cargo test

use sqlx::{PgPool, Error};

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

// ユーザーを追加する関数
pub async fn add_user(pool: &PgPool, name: &str, email: &str) -> Result<User, Error> {
    sqlx::query_as!(
        User,
        "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, name, email",
        name,
        email
    )
    .fetch_one(pool)
    .await
}

// ユーザーを取得する関数
pub async fn get_user_by_email(pool: &PgPool, email: &str) -> Result<User, Error> {
    sqlx::query_as!(
        User,
        "SELECT id, name, email FROM users WHERE email = $1",
        email
    )
    .fetch_one(pool)
    .await
}

#[cfg(test)]
mod tests {
    use super::*;
    use sqlx::{PgPool, Executor};

    // テストデータベースのセットアップ
    async fn setup_test_db(pool: &PgPool) -> Result<(), sqlx::Error> {
        pool.execute("DELETE FROM users").await?;
        Ok(())
    }

    #[sqlx::test]
    async fn test_crud_operations(pool: PgPool) -> Result<(), sqlx::Error> {
        // テストデータベースの初期化
        setup_test_db(&pool).await?;

        // ユーザーを追加
        let new_user = add_user(&pool, "Alice", "alice@example.com").await?;
        assert_eq!(new_user.name, "Alice");
        assert_eq!(new_user.email, "alice@example.com");

        // ユーザーを取得
        let fetched_user = get_user_by_email(&pool, "alice@example.com").await?;
        assert_eq!(fetched_user, new_user);

        Ok(())
    }
}

use sqlx::{PgPool, Transaction, Error};

pub async fn add_user_with_transaction(
    pool: &PgPool,
    name: &str,
    email: &str,
) -> Result<User, Error> {
    let mut tx: Transaction<'_, sqlx::Postgres> = pool.begin().await?;

    let user = sqlx::query_as!(
        User,
        "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, name, email",
        name,
        email
    )
    .fetch_one(&mut *tx)
    .await?;

    tx.commit().await?;

    Ok(user)
}

#[cfg(test)]
mod transaction_tests {
    use super::*;

    #[sqlx::test]
    async fn test_transaction(pool: PgPool) -> Result<(), sqlx::Error> {
        // トランザクション内でユーザーを追加
        let user = add_user_with_transaction(&pool, "Bob", "bob@example.com").await?;
        assert_eq!(user.name, "Bob");
        assert_eq!(user.email, "bob@example.com");

        // データベースに反映されているか確認
        let fetched_user = get_user_by_email(&pool, "bob@example.com").await?;
        assert_eq!(fetched_user, user);

        Ok(())
    }
}

cargo test

running 2 tests
test tests::test_crud_operations ... ok
test transaction_tests::test_transaction ... ok

test result: ok. 2 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.35s