Add example to docs on how to auto-rollback transactions

[Arcovion]

In sequel I can use DB.transaction(rollback: :always) { test_code } for writing tests where every test is run in it's own transaction and then rolled back on completion. How can I do this with doobie?

I see the util.transactor.Strategy class should help me, but I'm struggling a bit and need an example showing the correct way to do this.

[wedens]

The easiest way is to rollback x: ConnectionIO[A] using x *> HC.rollback.

With Strategy you can do (using existing lens syntax) val rollbackTransactor = (Transactor.strategy >=> Strategy.after).modify(transactor, _ => HC.rollback).

[tpolecat]

You can shorten the above incantation a bit.

// Create a new transactor from an old one
val xa2 = Transactor.after.set(xa, HC.rollback)

This requires the 0.4.2-SNAPSHOT right now. With earlier versions you need to subclass Transactor which is a bit more work.

[pabloa]

If I write an integration test saying something like:

// In the test: Create a new transactor from an old one 
val xa2 = Transactor.after.set(xa, HC.rollback)
...

// in some service/s, several doobie calls are like:
temUpdater.transact(xa2).unsafePerformIO
... 
// at the end of the integration tests, using some finders, we check objects coming from DB.
val a = finderA.transact(xa2).unsafePerformIO
assert( a.prop1 == valA1)
val b = finderA.transact(xa2).unsafePerformIO
assert( b.prop4 == valB4)

// end of the test, rollback happens and database is clean again.

Is all happening in 1 database transaction?

[tpolecat]

Doc issue rolled into #506

[zhenwenc]

Hi @pabloa , have you managed to find a solution? I came across the similar requirement recently, I can rollback individual ConnectionIO with Transactor.after.set(xa, HC.rollback) , but not for multiple transact calls (like the above example @pabloa posted).

@tpolecat do you have a recommended way to write integration tests? Not sure if we can avoid truncating all tables before each test cases.

Thanks!

[pabloa]

Hello,

Well. We did not find a solution to our problem. Right now we moved to kotlin / python.

[worace]

Sorry to keep commenting on a closed issue but I am interested in this as well.

It seems that the recommended approach is to keep everything in ConnectionIO as much as possible and compose all of your ConnectionIOs together in order to batch them within a single auto-rollback transaction which will keep your db clean after each test. It is even possible to lift other effects into ConnectionIO as shown in this StackOverflow Post so in some cases you can even fold other non-doobie-based operations into this model.

However, I still think that in more sophisticated apps you're eventually going to run into a need for more extensive integration testing that actually runs the effects (an obvious example would be executing a web request in a Play or http4s app).

As mentioned above, you can just truncate your tables between tests to make this work, but it makes for a much slower test run, to the point that it is relatively unusable. So it feels like one of the (perhaps few) cases where it may actually be worth it for the db layer to expose some kind of backdoor to allow more imperative external access to the transaction management plumbing.

From digging around in the doobie docs I pieced together a configuration that achieves this by overriding the transactor with a no-op strategy, and then using an externally managed connection pool to wrap each test in a transaction via scalatest fixtures:

package myapp

import javax.sql.DataSource
import com.zaxxer.hikari.HikariDataSource
import org.scalatest.BeforeAndAfterAll
import org.scalatest.fixture
import myapp.accounts.UserRepo

class TransactionalDBTest extends fixture.FunSuite with BeforeAndAfterAll {
  // Start a connection pool outside of doobie
  val pool = {
    val ds = new HikariDataSource()
    ds.setDataSourceClassName("org.postgresql.ds.PGSimpleDataSource")
    ds.addDataSourceProperty("url", "jdbc:postgresql://localhost/myapp_test")
    ds.addDataSourceProperty("user", "postgres")
    ds.addDataSourceProperty("password", "postgres")
    ds
  }

  type FixtureParam = UserRepo

  override def withFixture(test: OneArgTest) = {
    import doobie.{Transactor, HC}
    import doobie.util.transactor.Strategy
    import cats.effect.{ContextShift, IO}
    import scala.concurrent.ExecutionContext.Implicits.global
    import doobie.free.FC.unit

    implicit val cs : ContextShift[IO] = IO.contextShift(global)

    // Manually check out a connection for this test and open a transaction
    val conn = pool.getConnection()
    conn.setAutoCommit(false)
    val savepoint = conn.setSavepoint()
    // Create a transactor from the specific connection we have checked out for this test
    val baseXa = Transactor.fromConnection[IO](conn, global)
    // Create a "no-op" strategy by disabling all the default handlers
    val noOpStrategy = Strategy.default.copy(after = unit, oops = unit, always = unit)
    val xa = Transactor.strategy.set(baseXa, noOpStrategy)
    try {
      val repo = new UserRepo(xa)
      test(repo)
    } finally {
      // After the test, manually rollback to the defined savepoint
      println(conn.rollback(savepoint))
      conn.close()
    }
  }

  override def afterAll() {
    pool.close()
  }
}

I haven't used this setup very hard in a production context, but from the bit of testing I've done it seems to work. And it allows you to fully execute the db-based effects and still have things cleaned up after the test.

I suspect this approach is very yucky from an FP and overall hygiene perspective. I'd be curious if anyone has thoughts on whether it's a totally terrible idea or just a somewhat terrible idea. Or if anyone out there has figured out a more wholesome way to achieve something similar I'd love to hear their advice as well.

[zhenwenc]

Having each test case wrapped into one transaction which will eventually rolled back won't commit makes running test cases concurrently possible 😉. I have been doing this in Rust with diesel test_transaction, the experience is great!

[worace]

Having each test case wrapped into one transaction which will eventually rolled back won't commit makes running test cases concurrently possible

Absolutely. This is definitely the preferred strategy in other more established DB access libraries (e.g. Rails' transactional fixtures or Ecto's SQL Sandbox, or the Diesel example you shared). And to be clear, this is possible in Doobie as well using the existing HC.rollback after strategy on Transactor.

However I think the way DB-related effects are managed in Doobie makes it a little trickier to integrate into other ad-hoc test runner setups, which is why I'm interested in figuring out what a "sanctioned" strategy for this kind of need might look like.