Using sqlx::test for unit testing make much easier to test

What is sqlx::test?

In web development, sometime it is hard to test Repository layer because you may need DI or DB for test transaction.
sqlx provides sqlx::test attribute macro for testing purpose. This macro makes unit testing much easier. The sqlx::test create test DB and live connections automatically for you when run testing. And drop test database automatically after finish the testing.
You need set DATABASE_URL in environment value for DB connection.

To see more detail in doc: https://docs.rs/sqlx/latest/sqlx/attr.test.html

Support DB

Create find account by email function

Let create Account struct and a function to get the account by email.
The function parameters are database pool and email.

pub struct Account {
    pub id: uuid::Uuid,
    pub email: String,
}

pub async fn find_account_by_email(
    pool: &sqlx::PgPool,
    email: &str,
) -> Result<Option<Account>, sqlx::Error> {
    let account = sqlx::query_as!(
        Account,
        r#"
            SELECT id, email
            FROM accounts
            WHERE email = $1
        "#,
        email
    )
    .fetch_optional(pool)
    .await?;

    Ok(account)
}

Use sqlx::test for unit testing

Let write unit test for the function with sqlx::test. When you add the macro, the function can receive database pool or connection in first parameter, which connects the test database.

#[sqlx::test()]
async fn test_find_account_by_email(pool: PgPool) { // <-- add pool: PgPool
    let email = "test@test.com";
    let account = find_account_by_email(&pool, email)
        .await
        .expect("Failed to fetch account.")
        .expect("Account not found.");

    assert_eq!(account.email, email);
}

However, Database may NOT have test data, then you can use fixtures to add seeds before running test.
Let create /fixtures/accounts.sql for test data.

-- /fixtures/accounts.sql

INSERT INTO accounts (id, email)
VALUES
  ('8489f87b-a6bc-4904-a893-601e4a8d074f'::uuid, 'test@church-navi.com');

Add #[sqlx::test(fixtures(path = "fixtures", scripts("accounts")))] then before running test, sqlx::test automatically run the accounts.sql to insert data. You can add many scripts in scripts("accounts", "orders", "etc")

NOTE: fixtures path supports only RELATIVE PATH now.

#[sqlx::test(fixtures(path = "fixtures", scripts("accounts")))]
async fn test_find_account_by_email(pool: PgPool) {
    let email = "test@test.com";
    let account = find_account_by_email(&pool, email)
        .await
        .expect("Failed to fetch account.")
        .expect("Account not found.");

    assert_eq!(account.email, email);
}