Functionally retrieving rows from a database in Scala

Question

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?

• Landei: The second example looks better to me. I can't try it out right now, but is there a possibility to use Iterator instead of Stream?

Answer 1

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

• Hugo Sereno Ferreira: So you ah.. hmm.. made an Sql Monad? :)