A peek in the Mirror with Scala

TL:DR Reading SQL ResultSets in Scala 3 is surprisingly concise — even when you want List, Option, NamedTuples, or case classes. Here’s how to do it in under 100 lines.

Metaprogramming in Scala 3 is really powerful – but macros are usually overkill. Instead, Mirror sacrificing just enough "compiler magic" to generalise over case classes without sacrificing type safety. In this blog post we'll peek into the Mirror of Product and use it to turn raw JDBC ResultSets into real domain types.

Along the way, we'll stop by another Scala 3 feature that I really like – named tuples that arrived in Scala 3.7. I even blogged about it in this post.

This post is a continuation of my previous post where I talk about handling ResultSets from SQL. So this blog post will cover reading a ResultSet into a NamedTuple or a case class. In short, we apply advanced Scala 3 features to generalise over different types.

It should be really obvious by now, but this is not an introduction to Scala. Quite far from it, as it uses many Scala 3 features that are mostly used in library code.

First, I'd like to set the scene by looking back at the previous post.

Foundation

The code for reading a ResultSet is, so far, as follows:

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)

It enables reading a ResultSet into a List[T] or Option[T] by using the ResultSetReader[T] type class, where T is one of the types that has a Reader type class instance. Currently, the Reader supports Int, Long, String, Double, Boolean and Tuples containing the same types.

Now we would like to add support for NamedTuples and case classes.

Named tuples

So far, everything is position-based. It works – until columns are reordered or new ones are added.

That’s where things start to break down.

What we want is to read values by name instead of by position. It’s similar to tuples — but named tuples let us attach meaning to each field.

Named tuples are a bit more tricky than Tuple but a really nice feature introduced in Scala 3.7 that I've written about in an earlier post. Named tuples are two tuples – one for the names and one for the values. In practice, we use them like a dictionary, e.g. (key: String, value: String). One important note is that the names are only a compile time thing. At runtime, they're just tuples. Let's see how we can use them.

What we want here is to be able to read ResultSets by name instead of by position.
Here's an example where I intentionally swap the columns to show what I mean:

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

The idea is to pick up the key and value from the result set and put them in a Tuple by their names instead of their positions.

To stay consistent with regular tuples, I'm going to use recursion here as well.

The empty case (NamedTuple.Empty) is straightforward to support.

given Reader[NamedTuple.Empty] = (_, _) => NamedTuple.Empty

The nested case is a bit trickier. First, we need to take a little detour before getting to reading the nested named tuple.

A little trick to have up our sleeve is a Name type class to get the name of the field at compile time which is needed to get the right column in the result set.

trait Name[N]:
  def value: String
object Name:
  case class Impl[N](value: String) extends Name[N]

inline given [N <: String]: Name[N] = Name.Impl(scala.compiletime.constValue[N])

As the names are compile time only, we can use scala.compiletime.constValue[N] to get the name at compile time.

So by using Name, I can create a type class for reading an unempty named tuple that uses recursion to do the heavy lifting. The following definition is where things get interesting.

given [N <: String: Name as name, V: Reader as headReader, Ns <: Tuple, Vs <: Tuple](
  using tailReader: Reader[NamedTuple.NamedTuple[Ns, Vs]]
):Reader[NamedTuple.NamedTuple[N *: Ns, V *: Vs]] = (rs, idx) =>
  val indexOfName = rs.findColumn(name.value)
  headReader(rs, indexOfName) *: tailReader(rs, idx).toTuple

Let's break it down.

First, let's explain the parameters, as there are quite a few:

• N is the name of the tuple head which uses name to get the value at compile time
• V is the type of the value in the tuple head which uses headReader to read the value
• Ns refers to the names of the nested named tuple
• Vs refers to the values of the nested named tuple

So N and V make up the head of the tuple. Ns and Vs make up the tail of the tuple which uses tailReader to read the nested named tuple. Which means we can recurse on the nested named tuple and let the compiler do the work by using this Reader as long as there are elements in the nested named tuple. Only when it's empty, it will use the Reader for the empty tuple – Reader[EmptyTuple].

The last line is interesting. It uses the toTuple method to convert the nested NamedTuple to a Tuple. The compiler knows that it's a named tuple in compile time and infers the change. At runtime there is no named tuple as it's just a way of adding names instead of numbers to the elements in the tuple.

Note that the tail still uses positional indexing. This works because each element resolves its own column index independently via findColumn.

Supporting products

Named tuples are great, but case classes are better.

Let's see how to support products.

A product is a case class with at least one field. Like this one:

case class Pair(key: String, value: String)

Which means we would like to write:

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

Of course, the pattern is to add a type class for reading products. So we need to add a Reader[P] type class, where P is the product.

Here's the core of it – named tuples already encode the shape of a product.

So, if we can read a named tuple, we can read a case class for free.

We just have to derive the field names and types from the case class definition, use the named tuple reader and then convert it to the case class.

Here we need to use the scala.deriving package which contains Mirror to get the labels and types of the fields.

import scala.deriving.*

given [P: Mirror.ProductOf as mirror](
  using reader: Reader[NamedTuple.NamedTuple[mirror.MirroredElemLabels, mirror.MirroredElemTypes]]
):Reader[P] = (rs, idx) =>
  val nt = reader(rs, idx)
  mirror.fromTuple(nt)

So now we have support for products like Pair.

There's quite a lot going on here. Understanding Mirror.ProductOf is the core of the whole thing. It's a type class that allows us to get the labels and types from the fields of a case class. The compiler knows this information at compile time and infers what we need – mirror.MirroredElemLabels and mirror.MirroredElemTypes. The first is the labels of the fields, and the second is the types of the fields. These are used to get a reader for a named tuple that matches the fields of the case class. The fromTuple method is a helper method that converts the named tuple to the P case class.

The whole thing is called type class derivation. It's a powerful tool that allows us to derive instances of type classes for our types. It can be a bit tricky to get used to, but it's quite powerful.

As a side note, I'm using the names for reading the fields instead of the positions. This may not be ideal in all cases, but it's a good example of how to use Mirror.ProductOf.

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 Name[N]:
    def value: String

  object Name:
    case class Impl[N](value: String) extends Name[N]
    inline given [N <: String]: Name[N] = Impl(scala.compiletime.constValue[N])

  given Reader[NamedTuple.Empty] = (_, _) => NamedTuple.Empty
  given [N <: String: Name as name, V: Reader as headReader, Ns <: Tuple, Vs <: Tuple](
     using tailReader: Reader[NamedTuple.NamedTuple[Ns, Vs]]
  ):Reader[NamedTuple.NamedTuple[N *: Ns, V *: Vs]] = (rs, idx) =>
    val indexOfName = rs.findColumn(name.value)
    headReader(rs, indexOfName) *: tailReader(rs, idx).toTuple

  given [P: scala.deriving.Mirror.ProductOf as mirror](
    using reader: Reader[NamedTuple.NamedTuple[mirror.MirroredElemLabels, mirror.MirroredElemTypes]]
  ):Reader[P] = (rs, idx) =>
    val nt = reader(rs, idx)
    mirror.fromTuple(nt)
  
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

There's still room for improvement. For nested structures, the reader should return how much data it consumes to support reading more than one column at a time. The obvious example is reading nested tuples. This is important for a library but not really for looking at the concepts here. So, I'm leaving that as an exercise for the reader.

Summary

In under 100 lines of Scala 3, we’ve built a small, type-safe ResultSet reader that supports basic types, tuples, named tuples, and case classes. It’s a simple example, but it shows how powerful Scala 3’s type classes and derivation can be.

I've added an example of usage to Scastie for a comprehensive overview.

The key insight:

If we can read named tuples, we can read case classes for free.

Everything else is just wiring.