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?

• 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? Dec 16 '11 at 8:56

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

• So you ah.. hmm.. made an Sql Monad? :) Feb 28 '12 at 15:17