Wrapping a test in a transaction

Question

Assuming I have several tests that do something like this

"should do something with the database" in new WithApplication {
    DB.withTransaction { implicit con =>
        // Query the database.
        // Perform my tests.

        // Rollback the transaction to return database to initial state
        con.rollback()
    }
}

I don't want to remember to wrap all my database tests in a transaction and call con.rollback() at the end of each test manually.

Instead I tried writing a trait that wraps the entire test in a transaction so I could just write my tests like this

"do something to the database" in new WithApplication with DatabaseTest {
    SQL("...").execute()
}

This is what I have so far

trait DatabaseTest extends Around with Scope {
    abstract override def around[T: AsResult](test: => T): Result = {
        super.around = {
            import play.api.Play.current

            DB.withTransaction { implicit con =>
                val result = test
                con.rollback()
                result
            }
    }
}

However when I try running the test above I get this error

could not find implicit value for parameter connection: java.sql.Connection

So my question is whether what I'm trying to do is possible, and if so, what's wrong with my code and what would I have to change to get it to work.

Answer 1

Implicits in Scala is not quite as magic as transactions added to some kind of context in Java application servers for example, instead they work by telling the compiler "if this parameter of type T is missing, look in the scope around where I'm used for a value of type T marked as implicit".

This is why your usual code that looks like this works:

def doStuff()(implicit aConnection: Connection) = ???

DB.withTransaction { implicit theConnection =>
    doStuff() // secretly the compiler adds theConnection here
}

But this also means that you cannot get it for free because some class you inherited have an implicit connection, it needs to be in scope to be available, and Around does not let you change the signature of the test, so you cant really get it into scope using that.

One way to do it might be to avoid withTransaction and instead use DB.getConnection in a scope, something like this (from the top of my head, so might not be exact/compile):

trait Tx extends Scope with After {
  implicit lazy val conn = DB.getConnection(autocommit = false)

  override def after() = conn.rollback()
}

Then use it in your tests, which will in the implicit connection into scope:

"something something" in new Tx {
   SQL("...").execute()
}