Functionally retrieving rows from a database in Scala

Consider the following piece of code:

var result = List[Measurement]()

val dbResult = conn.createStatement.executeQuery(unsentMeasurements)

while(dbResult.next) {
  result ::= Measurement(dbResult.getString("stuff"), dbResult.getString("morestuff"))
}

result.reverse

Versus this:

val dbResult = conn.createStatement.executeQuery(unsentMeasurements)

Stream.continually(dbResult).takeWhile(_.next) map { r =>
  Measurement(r.getString("stuff"), r.getString("morestuff"))
}

Any flaws in my rationale? Any improvements?

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

StackExchange Code Review Q#6678, answer score: 3