Reading ResultSets with Scala

TL:DR Reading a JDBC ResultSet with Scala 3 is really simple when using type classes.

My previous post was about reading SQL queries by using string interpolation in Scala. I think it's time to continue and handle the ResultSet as well. Returning a ResultSet works, but it begs for a better solution.

I'm going to use a few Scala 3 concepts that are quite convenient for this task.

But first, we need to set the scene.

Setting the scene

We defined a PostgreSQL database with the following table:

CREATE TABLE text_store (
    key TEXT PRIMARY KEY NOT NULL,
    value TEXT NOT NULL
)

It's just a simple key-value store – pretty much the simplest setup I can imagine.

Last time we left off with the following code:

def get(key: String)(using Connection): ResultSet =
  sql"""
    SELECT value
    FROM text_store
    WHERE key = $key
  """.asResultSet

Nice start, but returning a ResultSet is, as I've already said, not what we want. I would rather have an Option[String] as a result.

The first goal

The first goal is to be able to write:

def get(key: String)(using Connection): Option[String] =
  sql"""
    SELECT value
    FROM text_store
    WHERE key = $key
  """.as[Option[String]]

There are going to be a few steps to get there. However, let's start really simple.

def get(key: String)(using Connection): Option[String] =
  sql"""
    SELECT value
    FROM text_store
    WHERE key = $key
  """.asOptionString

That's the first step: adding support for asOptionString.

A little bit of repetition

Just to get everybody on the same page, let's look at what we are working with.

First, we have that the SQL query is represented as:

val query: SQL = sql"""
    SELECT value
    FROM text_store
    WHERE key = $key
  """

Where SQL is a case class:

case class SQL(statement: String, params: Seq[Param])

Then, adding support for asOptionString is as simple as:

import java.sql.{Connection, ResultSet}

extension (query: SQL) 
  def asOptionString(using Connection): Option[String] = 
    val rs: ResultSet = query.asResultSet
    if rs.next() then
      Some(rs.getString(1))
    else 
      None

Here, the asResultSet method is defined in the previous post. It's just a wrapper around the SQL query that gives us a result set.

The asOptionString method is very tailored to the specific case of reading a single optional value from the result set. We would like to generalize it to support different types of values like Option[String]. So let's move toward supporting as[Option[String]].

So taking one step further, it is time to generalize the String to T which means going from asOptionString to asOption[T].

Reading basic values

Adding support for reading optional values can be done by:

def get(key: String)(using Connection): Option[String] =
  sql"""
    SELECT value
    FROM text_store
    WHERE key = $key
  """.asOption[String]

For that, we need a type class that can read a value from a ResultSet. That is the Reader[T] type class:

trait Reader[T]:
  def read(rs: ResultSet, index: Int): T

To use Reader[T], we can create a method like:

extension (query: SQL)
  def asOption[T: Reader as reader](using Connection): Option[T] = 
    val rs: ResultSet = query.asResultSet
    if rs.next() then
      Some(reader.read(rs, 1))
    else 
      None

Here the reader that supports T is provided by the compiler.

To support different types of values, we define a Reader[T] for each type that the compiler can infer.

object Reader:
  given Reader[Int] = (rs, idx) => rs.getInt(idx)
  given Reader[Long] = (rs, idx) => rs.getLong(idx)
  given Reader[String] = (rs, idx) => rs.getString(idx)
  given Reader[Double] = (rs, idx) => rs.getDouble(idx)
  given Reader[Boolean] = (rs, idx) => rs.getBoolean(idx)

Now, it is possible to write:

def getOption(key: String)(using Connection): Option[String] =
  sql"""
    SELECT value
    FROM text_store
    WHERE key = $key
  """.asOption[String]

So, one step closer to the goal.

Reading optional results

The next step is to generalize further by adding support for Option[String] as a result. Optional here means that there are no rows in the result set.

So what we would like to write is:

def get(key: String)(using Connection): Option[String] =
  sql"""
    SELECT value
    FROM text_store
    WHERE key = $key
  """.as[Option[String]]

Here we need to define a different type class for reading full result sets.

trait ResultSetReader[T]:
  def apply(rs: ResultSet): T

Just to clarify, ResultSetReader[T] reads an entire result set, while Reader[T] reads a single column value. This separation makes it possible to reuse the same Reader[T] for different result shapes like Option[T] and List[T].

Using the ResultSetReader[T] provided by the compiler we take another step by adding a method like:

extension (query: SQL)
  def as[T: ResultSetReader as resultSetReader](using Connection): T = 
    val rs: ResultSet = query.asResultSet
    resultSetReader(rs)

This means that I need to define a ResultSetReader for Option[String].

given ResultSetReader[Option[String]] = rs => 
  if rs.next() then
    Some(rs.getString(1))
  else
    None

There we have support for reading an Option[String], but I would like support for any type of Option. So let's go back and use our Reader[T] type class as a type parameter.

given [T: Reader as reader]:ResultSetReader[Option[T]] = rs => 
  if rs.next() then
    Some(reader(rs, 1))
  else
    None

Now there is support for as[Option[String]].

Option[T] is nice, but limiting. Adding support for List[T] is the natural next step.

Reading lists

To read List[T], we need to provide a ResultSetReader[List[T]].

given [T: Reader as reader]:ResultSetReader[List[T]] = rs => 
  val builder = List.newBuilder[T]
  while rs.next() do
    builder.addOne(reader(rs, 1))
  builder.result()

Then we can write:

def getKeys()(using Connection): List[String] =
  sql"""
    SELECT key
    FROM text_store
  """.as[List[String]]

So far so good.

Reading tuples

The next natural step is to support Tuple.

Which means getting support for writing:

def getAllTuples()(using Connection): List[(String, String)] =
  sql"""
    SELECT key, value
    FROM text_store
  """.as[List[(String, String)]]

The approach is adding a type class for Reader[T <: Tuple]. I will use recursion in the implementation. It doesn't have to be recursion, but I prefer it to keep things simple. On the flip side, it might not be the most performant way of doing it, but it's good enough for now.

First, we need to support the simplest of cases – the empty tuple.

given Reader[EmptyTuple] = (_, _) => EmptyTuple

Then we need to support for reading the head of the tuple and let recursion do the rest.

given [H: Reader as headReader, T <: Tuple: Reader as tailReader]: Reader[H *: T] = (rs, idx) => 
    headReader(rs, idx) *: tailReader(rs, idx + 1)

And that's tuple support.

This approach scales surprisingly well — in a later post we can extend the same idea to case class mapping. That will involve another powerful Scala 3 feature: Mirror.

However, let's move on in a different direction.

Optional values

Some tables have nullable columns. The obvious way to handle that is to use Option in Scala. Which means adding support for Reader[Option[T]] where T is any type that has a Reader[T].

The implementation is as follows:

given [T: Reader as reader]: Reader[Option[T]] = (rs, idx) =>
  val value = reader(rs, idx)
  if rs.wasNull() then 
    None 
  else 
    Some(value)

I follow the same approach as for ResultSetReader[T], by requiring a reader of type Reader[T]. The key idea is to do the least amount of work possible and let the compiler figure out the rest.

Putting it all together

There is a full overview of all the code in one gist:

import java.sql.{PreparedStatement, Connection, ResultSet}

trait Reader[T]:
  def apply(rs: ResultSet, index: Int): T

object  Reader:
  given Reader[Int] = (rs, idx) => rs.getInt(idx)
  given Reader[Long] = (rs, idx) => rs.getLong(idx)
  given Reader[String] = (rs, idx) => rs.getString(idx)
  given Reader[Double] = (rs, idx) => rs.getDouble(idx)
  given Reader[Boolean] = (rs, idx) => rs.getBoolean(idx)
  given [T: Reader as reader]: Reader[Option[T]] = (rs, idx) =>
    val value = reader(rs, idx)
    if rs.wasNull() then None else Some(value)
  
  given Reader[EmptyTuple] = (_, _) => EmptyTuple
  given [H: Reader as headReader, T <: Tuple: Reader as tailReader]: Reader[H *: T] = (rs, idx) =>
    headReader(rs, idx) *: tailReader(rs, idx + 1)

  
trait ResultSetReader[T]:
  def apply(rs: ResultSet): T

object ResultSetReader:
  given [T: Reader as reader]:ResultSetReader[Option[T]] = rs =>
    if rs.next() then Some(reader(rs, 1)) else None

  given [T: Reader as reader]:ResultSetReader[List[T]] = rs =>
    val builder = List.newBuilder[T]
    while rs.next() do
      builder.addOne(reader(rs, 1))
    builder.result()

extension (query: SQL)
  def as[T: ResultSetReader as resultSetReader](using Connection): T =
    val rs: ResultSet = query.asResultSet
    resultSetReader(rs)

Further work

I still think there are more interesting topics that come out of handling ResultSets. It would be great to add support for NamedTuples and case classes. That is a topic for another post.

Summary

So now we have support for:

sql"...".as[Option[String]]
sql"...".as[List[String]]
sql"...".as[List[(String, String)]]

A quite elegant design.

What makes this powerful is that we only implemented a few small type classes, yet the compiler now automatically derives support for many result shapes.

I've added an example of usage to Scastie for you to play with.

I hope you enjoyed this post. The purpose is to show the power of some Scala 3 features in a simple example. It's not meant to be a complete solution, but it's not that far from it. What I find remarkable is that it's in less than 40 lines of code. I would say the magic word is given.