SQLite 複数接続使用時の挙動テスト

gistfile1.rs

use sqlx::sqlite::SqlitePoolOptions;
use anyhow::Result;
use sqlx::{Row, SqlitePool};

fn main() {}

#[tokio::test]
async fn test() -> Result<()> {
    const LOOP_COUNT: usize = 10;
    let db_dir = tempfile::tempdir()?;
    let pool = SqlitePoolOptions::new()
        .min_connections(100)
        .connect(format!("sqlite://{}/test.db?mode=rwc", db_dir.path().display()).as_str())
        .await?;

    sqlx::query(r"
create table test (
  id integer primary key autoincrement not null,
  c1 text not null
);")
        .execute(&pool)
        .await?;

    // 1. test pool
    for _ in 0..LOOP_COUNT {
        let id = insert(&pool).await?;
        let c1 = find_by_id(id, &pool).await?.unwrap();
        assert_eq!(c1, "aaa", "pool");
    }

    // 2. test connection
    for _ in 0..LOOP_COUNT {
        let mut conn = pool.acquire().await?;
        let id = insert(&mut conn).await?;
        let c1 = find_by_id(id, &mut conn).await?.unwrap();
        assert_eq!(c1, "aaa", "connection");
    }

    // 3. test different connection
    for _ in 0..LOOP_COUNT {
        let mut conn = pool.acquire().await?;
        let id = insert(&mut conn).await?;
        let mut conn = pool.acquire().await?;
        let c1 = find_by_id(id, &mut conn).await?.unwrap();
        assert_eq!(c1, "aaa", "different connection");
    }

    // 4. test tx
    for _ in 0..LOOP_COUNT {
        let mut tx = pool.begin().await?;
        let id = insert(&mut tx).await?;
        let c1 = find_by_id(id, &mut tx).await?.unwrap();
        assert_eq!(c1, "aaa", "tx");
    }

    // 5. test connection & tx
    for _ in 0..LOOP_COUNT {
        let mut tx = pool.begin().await?;
        let id = insert(&mut tx).await?;
        let c1 = find_by_id(id, &pool).await?.unwrap();
        assert_eq!(c1, "aaa", "connection & tx");
    }

    Ok(())
}

async fn insert<'a, A>(conn: A) -> Result<i32>
    where
        A: sqlx::Acquire<'a, Database=sqlx::Sqlite>
{
    let mut conn = conn.acquire().await?;
    let row = sqlx::query("insert into test (c1) values ($1) returning id")
        .bind("aaa")
        .fetch_one(&mut *conn)
        .await?;
    Ok(row.get(0))
}

async fn find_by_id<'a, A>(id: i32, conn: A) -> Result<Option<String>>
    where
        A: sqlx::Acquire<'a, Database=sqlx::Sqlite>
{
    let mut conn = conn.acquire().await?;
    let row = sqlx::query("select c1 from test where id = $1")
        .bind(id)
        .fetch_optional(&mut *conn)
        .await?;
    Ok(row.map(|x| x.get(0)))
}