Document on how to write testcases within sqlx?

[TommyLike]

We use sqlx to handle MySQL business, my question is how to write testcases for those SQL related operations? I mean setting up the connections/pools and peroform migrations before/after testcases, if thera any document/library for this purpose?

[xiayulu]

Hey, I face the same question like you.

After read the project's tests case, I made my solution:

• prepare the testing database: usually a database running in docker.

• prepare migrations: I use sqlx migrate add -r <name> generate migrations.

• write some helper functions like get_pool, inspired by reading sqlx-test.

pub fn setup_env() -> Result<()> {
    dotenvy::dotenv()?;

    Ok(())
}

// Make a new pool
// Ensure [dotenvy] and [env_logger] have been setup
pub async fn get_pool<DB>() -> Result<Pool<DB>>
where
    DB: Database,
{
    setup_env()?;

    let pool = PoolOptions::<DB>::new()
        .min_connections(0)
        .max_connections(5)
        .test_before_acquire(true)
        .connect(&env::var("DATABASE_URL")?)
        .await?;

    Ok(pool)
}

pub async fn clean_db(pool: &PgPool) -> Result<()> {
    // roll back migrations
    sqlx::migrate!("./migrations").undo(pool, 1000).await?;

    // drop `_sqlx_migrations`
    sqlx::query("DROP TABLE IF EXISTS _sqlx_migrations;")
        .execute(pool)
        .await?;

    Ok(())
}

• write test cases:

#[sqlx::test]
async fn it_connects() -> Result<()> {
    let mut conn = get_conn::<Postgres>().await?;

    let value = sqlx::query("select 1 + 1")
        .try_map(|row: PgRow| row.try_get::<i32, _>(0))
        .fetch_one(&mut conn)
        .await?;

    assert_eq!(2i32, value);

    Ok(())
}

#[sqlx::test]
#[ignore = "clean database will cause other test case failed."]
async fn migrations_works() -> Result<()> {
    let pool = get_pool::<Postgres>().await?;

    let sql = r#"
select
	exists (
	select
	from
		pg_tables
	where
		schemaname = 'public'
		and tablename = 'users'
);
"#;
    let data = sqlx::query(sql).fetch_one(&pool).await?;
    let exists: bool = data.get("exists");
    assert!(!exists);

    sqlx::migrate!("./migrations").run(&pool).await?;

    // after migration
    let data = sqlx::query(sql).fetch_one(&pool).await?;
    let exists: bool = data.get("exists");
    assert!(exists);

    // clear env
    clean_db(&pool).await?;

    Ok(())
}

#[sqlx::test]
async fn user_crud_works() -> Result<()> {
    let pool = get_pool::<Postgres>().await?;
    // clear env
    clean_db(&pool).await?;

    sqlx::migrate!("./migrations").run(&pool).await?;

    let form = dao::user::NewUser {
        name: "hello".to_owned(),
        avatar: "world".to_owned(),
        bio: "".to_owned(),
    };

    let user = dao::user::User::create(&pool, form).await?;

    assert!(user.id > 0);
    assert!(user.name == "hello".to_owned());
    assert!(!user.is_del);

    sleep(Duration::from_millis(100)).await;
    let form = dao::user::UpdateUser {
        id: user.id,
        name: Some("haha".to_owned()),
        avatar: None,
        bio: None,
    };

    let user = dao::user::User::update(&pool, form).await?;
    assert!(user.name == "haha".to_owned());
    assert!(user.avatar == "world".to_owned());
    assert!(user.updated_at > user.created_at + time::Duration::milliseconds(100));

    Ok(())
}

• run tests

cargo test --test dao -- --nocapture

Hope my solution helps.

Existing Problem:

• running test cases parallel in the same database may conflict. possible solution: using virtual database like Mock DB.
• setup testing env using helper function maybe too simple. possible solution: Using a struct like TestManager to config testing env.

[TommyLike]

Thanks, will have a try @xiayulu