Anorm implicit connection boilerplate

Question

Is there an easy way to get rid of anorm's implicit connection boilerplate?

I have a DB object given by:

import java.sql.Connection
import scalikejdbc.ConnectionPool

object DB {
  def withConnection[A](block: Connection => A): A = {
    val connection: Connection = ConnectionPool.borrow()

    try {
      block(connection)
    } finally {
      connection.close()
    }
  }
}

and then all queries must be enclosed by this pattern

def myMethod(par1: Int, par2: String): Seq[MyClass] = {
    DB.run SQL("SELECT a,b,c FROM table WHERE foo={par1} AND bar={par2}")
        .on('par1=par1, 'par2=par2)
        .as(MyClass.myRowParser *)
}

It would be nice to have a method on DB that scrapped the need of a function Connection => A with this implicit connection so that I could write simply:

def myMethod(par1: Int, par2: String): Seq[MyClass] = {
    DB.run SQL("SELECT a,b,c FROM table WHERE foo={par1} AND bar={par2}")
        .on('par1=par1, 'par2=par2)
        .as(MyClass.myRowParser *)
}

Is there a way to do this easily?

Answer 1

Depends on how far down the rabbit hole you want to go...within some of my DAO code, I define a Free monad over SQL operations, using something like this...firstly define a case class which wraps a generic function which takes a Connection to an A:

final case class SQLOperation[A](block: Connection ⇒ A)

Then, define a functor for it:

implicit object SQLOperationFunctor extends Functor[SQLOperation] {
    def map[A, B](a: SQLOperation[A])(f: A ⇒ B) = SQLOperation[B]((c:Connection) ⇒ f(a.block(c)))
}

Then, define a Free monadic type for it (which you can do using Scalaz Free type class, provided you have a functor defined):

type FreeSQLOperation[A] = Free[SQLOperation, A]

Just to make things easier, you can then just define an implicit to lift operations into the Free monad:

implicit def liftSQLOperationToFree[A](op : SQLOperation[A]) : FreeSQLOperation[A] = {
    Free.liftF(op)
}

Once you've got all that machinery defined, you then need to define an interpreter for the Free monadic actions, something like this which gives you a central point of command and control for your executions:

final def run[A](t : ⇒ FreeSQLOperation[A]) : SQLResult[A]  = t.fold(
    (a: A) ⇒  a.right,
    (op : SQLOperation[FreeSQLOperation[A]]) ⇒ {
        DB.withConnection { implicit c ⇒
            val t= Try(run(op.block(c)))
            t match {
                case scala.util.Success(a) ⇒  a
                case scala.util.Failure(ex) ⇒  ex.getMessage.left
            }
        }
    }
)

In this case, everything is just wrapped in Try so that exceptions are centrally handled and then mapped into a Validation or Disjunction type. (That's what the SQLResult type is...)

Once you have all this spanked together, in your DAO classes, you can then just add some generic methods like this for example:

def selectAll(params : ⇒ Seq[NamedParameter]) : FreeSQLOperation[List[V]] = {
    val clause = params.map(p ⇒ s"${p.name} = {${p.name}}").mkString(" and ")
    SQLOperation( implicit c ⇒
                      SQL(s"""
                             | select * from $source
                             |    where $clause
            """.stripMargin).on(params : _*).as(rowParser *).flatten
    )
}

or this:

def insert(params : ⇒ Seq[NamedParameter]) : FreeSQLOperation[Int] = {
    val columns = params.map(p ⇒ s"${p.name}").mkString(",")
    val values = params.map(p ⇒ s"{${p.name}}").mkString(",")
    SQLOperation( implicit c ⇒
        SQL(s"""
             | insert into $source ($columns)
             | values ($values)
             """.stripMargin).on(params : _*).executeInsert(scalar[Int].single)
    )
}

which you might put in a base class. Add the run method to the base class (the interpreter bit) and then within your higher-level DAO objects you can then write code that basically looks like this:

override def read(key: String): SQLResult[Principal] = {
    run {
        selectOne {
            Seq("uid" → key)
        }
   }
}

or this, where you can see how the Free monad allows you to chain operations together:

run {
        for {
            m1 ← updateByUid(uid){Seq("uid" → value.uid)}
            m2 ← updateByUid(value.uid){Seq("secret" → value.secret)}
            m3 ← updateByUid(value.uid){Seq("modified" → DateTime.now)}
            p ← selectOne { Seq("uid" → value.uid) }
        } yield (p)
    }

There's quite a lot of busywork to get things set up, but once you have, you're actual business logic becomes much clearer, de-cluttered and it also means that you can just swap in a different interpreter (definition of the run function) if you want to adopt different strategies for handling exceptions, logging etc...

HTH