Monad for Database Transactions

DbTransactionMonad.scala

// Simple monad to deal with multiple database transactions with same connection, their commits and rollbacks
// Something similar done in http://advorkovyy.blogspot.com.au/2010/10/transactional-monad-for-scala.html
 
import java.sql.Connection
 
trait DbTransaction[A] {
  def unit: Connection => A
  def map[B](f: A => B): DbTransaction[B] = DbTransaction {
    connection => f(unit(connection))
  }
  def flatMap[B](f: A => DbTransaction[B]): DbTransaction[B] = DbTransaction {
    connection => f(unit(connection)).unit(connection)
  }
 
  import scala.util.{Try, Success, Failure}
 
  def commitBlocking(connection: Connection): Try[A] = Try {
    connection.setAutoCommit(false)
    val a = unit(connection)
    connection.commit()
    a
  }
 
  import scala.concurrent.{Future, ExecutionContext}
 
  def commit(connection: Connection)(implicit ec: ExecutionContext): Future[A] = Future {
    connection.setAutoCommit(false)
    val a = unit(connection)
    connection.commit()
    a
  }
}
 
object DbTransaction {
  def apply[A](f: Connection => A): DbTransaction[A] = new DbTransaction[A] {
    def unit = f
  }
}
 
// Usage for example with Play Framework and Anorm
object DatabaseOperation {
 
  import anorm._
  import play.api.db.DB
  import play.api.Play.current
 
  def store(first: String, second: String) = {
    val op = for {
      f <- DbTransaction { 
        implicit connection => SQL("INSERT INTO first (value) values ({value})").on('value -> first).execute()
      }
      g <- DbTransaction {
        implicit connection => SQL("INSERT INTO second (value) values ({value})").on('value -> second).execute()
      }
    } yield g
    // This DB.withConnection can also be moved within commit/commitBlocking of DbTransaction trait
    // but then it would be bound to the DB implementation. This way it is more generic.
    DB.withConnection { implicit conn => op.commitBlocking(conn) }
  }
}