Shared Test Transactions

[mcasper]

When running tests in a multithreaded environment (like running tests against a webserver), currently the only way to ensure that your test database is clean across threads is to drop/truncate for every test. This works fine, but is tedious, and it would be nicer if connections and their test transactions could be shared, so that data is all localized and automatically cleaned up.

One way to accomplish this that I've been thinking about is to implement Sync for connections and Connection for Arc<T: Connection>, allowing pointers to a shared connection to be passed around and data within a test transaction to be visible. I know we've previously talked about not wanting to implement Sync for connections because they're not meant to be passed around, which is why I'd want to hide this away behind a tests or testing feature, included only in [dev-dependencies].

Some changes would be necessary in other supporting libraries (like r2d2/r2d2-diesel for example), but could lead to code like the following for testing (example using Rocket):

#[test]
fn test_index() {
  let pool = database::pool();
  let conn = pool.get().unwrap();
  let rocket_app = app::launch(pool);

  conn.begin_test_transaction().unwrap();

  // insert data into connection
  // build and send request to app
  // assert on response
}

If we like this, I'm happy to take on the feature work

[sgrif]

The solution I've been using in crates.io is to set the connection pool size to 1, but I agree that it'd be nice to make it easier. I do not want to implement Sync on connections, as they are explicitly not thread safe and several of them rely on being guaranteed to not be concurrently used across threads. We'd need a mutex for sure.

[fluffysquirrels]

Arc<Mutex<T: Send>> already implements Sync automagically (Mutex docs) and Connection requires Send. So can you just wrap transactions in Arc<Mutex<T>> in your app?

[ghost]

I'm a currently fighting this and I'm close to having a worrkable solution with rocket. I'm wondering one thing, how do I set the size of the pool to be 1?

[dbrgn]

In some cases setting the pool size to 1 won't work in integration tests, if multiple conections are required for the system-under-test.

Also, transactions don't work if the connection is not shared (e.g. if the sytem-under-test creates its own connections and transactions).

I created a new lib to ensure nonparallel functions (which can be used for tests as well): https://github.com/dbrgn/nonparallel/ This can be used to simulate table-level locking (since tests often use a single table), so that no two tests modify the same database table at the same time. This avoids the need for transactions.

To clean up the table after the test, I use scopeguard. Works really nicely.

Example:

use lazy_static::lazy_static;
use nonparallel::nonparallel;
use scopeguard::{self, ScopeGuard};

// ...

lazy_static! {
    static ref MUT_TABLE_A: Mutex<()> = Mutex::new(());
}
lazy_static! {
    static ref DB_POOL: Pool<PostgresConnectionManager> =
        { make_db_pool(&CONFIG).expect("Could not create db pool") };
}

/// Verify that the specified table is empty and initialize the scope guard
/// that will clear the table on drop.
fn init_guard_for_table(table: &str) -> ScopeGuard<u8, impl FnOnce(u8) + '_> {
    // Ensure that test table is empty
    let conn = DB_POOL.get().unwrap();
    let result = conn
        .query(&format!("SELECT count(*) FROM {}", table), &[])
        .expect("Could not query database");
    let row_count: i64 = result.get(0).get(0);
    assert_eq!(row_count, 0, "Test table {} is not empty!", table);

    // Clear table again when the guard is dropped
    scopeguard::guard(0, move |_| {
        let conn = DB_POOL.get().expect("Could not get db connection");
        println!("Clearing {}", table);
        conn.execute(&format!("DELETE FROM {}", table), &[])
            .expect(&format!("Could not clear table {}", table));
    })
}

#[test]
#[nonparallel(MUT_TABLE_A)]
fn get_names() {
    let _guard = init_guard_for_table("table_a");

    // ...run integration tests
}

I might create a blogpost about this sometime.

[darwin67]

I'm doing something like this and it works pretty well. Just using whatever r2d2 provides.

pub type Pool = r2d2::Pool<ConnectionManager<PgConnection>>;
pub type DBConnection = r2d2::PooledConnection<ConnectionManager<PgConnection>>;

lazy_static! {
    static ref POOL: Pool = {
        let db_url = env::var("DATABASE_URL").expect("DATABASE_URL must be set");
        let manager = ConnectionManager::<PgConnection>::new(db_url);
        let mut builder = r2d2::Pool::builder();

        if &env() == "test" {
            builder = builder.connection_customizer(Box::new(TestConnectionCustomizer));
        }

        builder
            .build(manager)
            .expect("Failed to create database connection pool")
    };
}

#[derive(Debug, Clone, Copy)]
pub struct TestConnectionCustomizer;

impl<C, E> CustomizeConnection<C, E> for TestConnectionCustomizer
where
    C: diesel::Connection,
{
    fn on_acquire(&self, conn: &mut C) -> Result<(), E> {
        conn.begin_test_transaction()
            .expect("Failed to start test transaction");

        Ok(())
    }
}