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 =&gt;
  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 =&gt;
  def unsafePerformIO(ds: javax.sql.DataSource): A = unsafePerformIO(ds.createConnection)

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

  def map(f: A =&gt; B): Sql[B] = new Sql[B] { 
    def unsafePerformIO(conn: Connection) = f(self.unsafePerformIO(conn))
  }
  def flatMap(f: A =&gt; 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 =&gt; 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 =&gt; 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 &lt;- getStuff1
    j &lt;- getStuff2
    s &lt;- 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