Functionally retrieving rows from a database in Scala

Of course this is not purely functional, although it represents an improvement. What would a purely functional solution look like?

trait Sql[A] { self =>
  def unsafePerformIO(ds: javax.sql.DataSource): A = unsafePerformIO(ds.createConnection)

  def unsafePerformIO(conn: java.sql.Connection): A //abstract

  def map(f: A => B): Sql[B] = new Sql[B] { 
    def unsafePerformIO(conn: Connection) = f(self.unsafePerformIO(conn))
  }
  def flatMap(f: A => Sql[B]): Sql[B] = new Sql[B] { 
    def unsafePerformIO(conn: Connection)
      = f(self.unsafePerformIO(conn)).unsafePerformIO(conn)
  }

}

I imagine you might say "huh?" at this point. Let me introduce the companion:

object Sql {
  def apply[A](comp: Connection => A) = new Sql[A] {
    def unsafePerformIO(conn: Connection) = comp(conn)
  }
}

The idea (of course) is that you delay side-effects until the end of the world, encapsulating the values returned by the database in a type that represents value returned from DB call, in the same way Option represents value which may not exist.

The usage becomes something like this:

Sql { conn => process( conn.prepareStatement.executeQuery ) }

Where the process method might look like your code (above) perhaps.

I imagine this may raise a few questions:

What the? Huh? I mean, what the?

Well, it allows you to do something like this:

def getStuff1: Sql[Int]
def getStuff2: Sql[Int]
def useStuff(i: Int): Sql[String]

Then you can compose using for-comprehensions

val result = 
  for {
    i <- getStuff1
    j <- getStuff2
    s <- useStuff(i + j)
  } 
  yield s

What is the type of this expression? Well, it's an Sql[String] of course! To get hold of the String:

result.unsafePerformIO(myDataSource)

This creates a single connection which is then threaded through the computation.

What is missing? Well, resource management (i.e. cleaning up the connections of course). I understand this will all be available in scalaz7