How can I roll back an integration test with Slick 3 + Specs2?

Question

I want to write some integration tests for a service that runs slick and then clean a postgresql database up afterward up by rolling back a transaction, but I don't see a way to do it. I understand that I can test DBIO objects which have been composed together and roll them back, but it doesn't look like it's possible if I want to test at a higher level of abstraction.

In pseudocode, I want to do this:

StartDbTransaction() // setup
DoSomethingInDB() 
AssertSomething() 
RollBackDbTransaction() // teardown

For example, if I have this (simplified from the play-silhouette-slick-seed):

class PasswordInfoDAO(db: JdbcBackend#DatabaseDef) {

    // ...
    def remove(loginInfo: LoginInfo): Future[Unit] =
        db.run(passwordInfoSubQuery(loginInfo).delete).map(_ => ())

}

I thought I could write a ForEach trait along the lines of the Specs2 Guide, which gives this a generic example:

// a transaction with the database
trait Transaction

trait DatabaseContext extends ForEach[Transaction] {
    // you need to define the "foreach" method
    def foreach[R: AsResult](f: Transaction => R): Result = {
        val transaction = openDatabaseTransaction
        try AsResult(f(transaction))
        finally closeDatabaseTransaction(transaction)
    }

    // create and close a transaction
    def openDatabaseTransaction: Transaction = ???

    def closeDatabaseTransaction(t: Transaction) = ???
}

class FixtureSpecification extends mutable.Specification with DatabaseContext {
    "example 1" >> { t: Transaction =>
        println("use the transaction")
        ok
    }
    "example 2" >> { t: Transaction =>
        println("use it here as well")
        ok
    }
}

So for slick, I tried this:

override def foreach[R: AsResult](f: JdbcBackend#DatabaseDef => R): Result = {

    val db = dbConfig.db
    val session = db.createSession()
    session.conn.setAutoCommit(false)
    val result = AsResult(f(db))
    session.conn.rollback()
    result

}

Then I planned to use it sort of like this:

class PasswordInfoDAOSpec(implicit ee: ExecutionEnv)
  extends Specification with DatabaseContext {

    "password" should {
       "be removed from db" in { db =>

        // arrange
        db.run(...) // something to set up the database

        // act
        PasswordInfoDAO(db).remove(loginInfo).await

        // assert
        PasswordInfoDAO(db).find(loginInfo) must be None.await
      }
   }
}

The problem is that slick 3 will ignore my session (by design) and instead use a session pool, so my roll-back doesn't do anything. I think that Slick has an expectation that you should use it at the level of DBIOActions which can be composed together and possibly executed in different contexts. Slick 2 had a way to control the session with .withSession, but it was removed.

Is the only option to create, migrate and drop a test database with each test?

Answer 1

Here's a partial answer. It seems to be either impossible or at least very inadvisable to roll back a transaction by reaching down to JDBC. So instead I rewrote the repositories to return DBIOs instead of my business objects. It's the DBIO monadic bind operation that takes care of transaction logic, so that's really the only way to roll something back.

class MyRepository {

  def add(whatever: String): dbio.DBIOAction[Int, NoStream, Write with Write] = {
       // return a DBIOAction
  }
}

I have a function that binds an arbitrary action to a "fake" exception, and then returns the Future result of the original action and discards the exception:

case class IntentionalRollbackException[R](successResult: R) extends Exception("Rolling back transaction")

def runWithRollback[R, S <: slick.dbio.NoStream, E <: slick.dbio.Effect](action: DBIOAction[R, S, E]): Future[R] = {

  val block = action.flatMap(r => DBIO.failed(new IntentionalRollbackException(r)))

  val tryResult = dbConfig.db.run(block.transactionally.asTry)

  // not sure how to eliminate these casts from Any
  tryResult.map {
    case Failure(IntentionalRollbackException(successResult)) => successResult.asInstanceOf[R]
    case Failure(t) => throw t
    case Success(r) => r.asInstanceOf[R]
  }

}

So then I can use this from a spec:

val insertAction1 = new MyRepository().add("whatever 1").withPinnedSession
val insertAction2 = new MyRepository().add("whatever 2").withPinnedSession
val actions = insertAction1 andThen insertAction2
val result = Await.result(runWithRollback(action), 5.seconds)
result must be ...

I'm sure there's also a way to write this more cleanly for specs2 as a ForEach trait or something similar.

I took these ideas from this and this