Transaction based integration testing with actix-web

[OskarPersson]

When making a web project in Django or Go I'm used to be able to easily create integration tests that are all running against a single real test database (Postgres in my case). With Django this is done automatically when using django.test.TestCase where it wraps every test function in a transaction and any request is handled within that same transaction. With Go I can similarly use txdb to achieve the same thing where instead each test gets its own sql.DB, each with their own unique identifier (for example a uuid).

Now when using Rust I'm trying to achieve the same thing with sqlx and Actix-web but have not yet succeeded.

For example, with the following Cargo.toml

[package]
name = "actix-sqlx-tests"
version = "0.1.0"
authors = []
edition = "2018"

[dependencies]
actix-test = "0.1.0-beta.12"
actix-web = "4.0.0-rc.3"
sqlx = { version = "0.5.10", default-features = false, features = ["chrono", "macros", "migrate", "postgres", "runtime-tokio-native-tls"] }

and main.rs

use actix_web::{get, web, HttpResponse, Responder};
use sqlx::postgres::{PgPool, PgRow};
use sqlx::Row;

const DATABASE_URL: &str = "postgres://postgres:postgres@localhost:32769/base";

#[get("/one")]
async fn one(pool: web::Data<PgPool>) -> impl Responder {
    let mut tx = pool.get_ref().begin().await.unwrap();
    sqlx::query("INSERT INTO foo (bar) VALUES ('inside nested transaction')")
        .execute(&mut tx)
        .await
        .unwrap();
    tx.rollback().await.unwrap();

    let cnt = sqlx::query("SELECT count(*) AS cnt FROM foo")
        .map(|row: PgRow| row.get::<i64, _>("cnt"))
        .fetch_one(pool.get_ref())
        .await
        .unwrap();

    sqlx::query("INSERT INTO foo (bar) VALUES ('inside test transaction')")
        .execute(&mut tx)
        .await
        .unwrap();

    HttpResponse::Ok().body(cnt.to_string())
}

#[actix_web::main]
async fn main() -> std::io::Result<()> {
    Ok(())
}

#[cfg(test)]
pub mod tests {
    use super::*;
    use actix_web::web::{Bytes, Data};
    use actix_web::{http, App};
    use sqlx::postgres::PgPoolOptions;

    #[actix_web::test]
    async fn test_endpoint() {
        let pool = PgPoolOptions::new()
            .max_connections(1)
            .connect(DATABASE_URL)
            .await
            .expect("pool failed");

        sqlx::query("BEGIN")
            .execute(&pool)
            .await
            .expect("BEGIN failed");

        sqlx::query("INSERT INTO foo (bar) VALUES ('test1')")
            .execute(&pool)
            .await
            .expect("INSERT test failed");

        let saved_pool = pool.clone();

        let app =
            actix_test::start(move || App::new().app_data(Data::new(pool.clone())).service(one));

        let req = app.get("/one");
        let mut resp = req.send().await.unwrap();

        assert_eq!(resp.status(), http::StatusCode::OK);
        assert_eq!(resp.body().await.unwrap(), Bytes::from_static(b"1"));

        sqlx::query("ROLLBACK")
            .execute(&saved_pool)
            .await
            .expect("ROLLBACK failed");
    }
}

This does not work since the test and the app are working two different pool instances. Instead I would like to have the handler "one" to have access to the object "test1" that was created in the test itself and thus return "1" since that is the only row available at the time since the inner transaction is rollbacked. The 'inside test transaction' should be rolled back in the rollback issued in the test itself. (And if the test were to fail anywhere, none of the data created should be preserved either since it is all then created in a transaction that is never rollbacked nor commited).

How can I achieve this? Would something similar to txdb as mentioned above work?

[augustocdias]

First you don't have 2 different pools. You have just one. When you clone you're basically adding a new reference counted pointer to the internal pool. So your test and actix are sharing the same pool.

Second you're not committing your insert clause and inside actix you're opening a new transaction. It will never see it unless you commit or have them inside the same transaction.

Removing the transaction from actix won't help because you can't guarantee they'll be in the same connection either.