SQL String interpolation in Scala

TL:DR A short Scala library that makes writing SQL queries really simple by using string interpolation.

I just wanted to write some simple SQL queries for a small project of mine. I could have picked a mature Scala SQL library like Doobie or Anorm. However, it got me thinking – how much work is it really to write a simple one. Simple SQL is a library that does exactly that, but the code is a bit more complex than I would like. There’s nothing wrong with using Scala macros, but I would prefer to keep things really simple.

That said, this is far from an introduction to Scala. Here I use a lot of Scala 3 features. For instance, string interpolation is something that Scala developers use all the time. On the contrary, hardly anyone implements a new one like I do here. That's mainly something library developers do. However, I think it's a nice example of how simple (not necessarily easy) it is to use string interpolation in Scala.

The goal of this blog post is to support SQL the way most people would like to write it – straightforward without having to worry about SQL injection. That should be taken care of. And on the other hand, it is just SQL, nothing more. Creating a Scala DSL for it would be fun, but SQL is easy enough for small projects.

The same thing works in Doobie and Anorm – write SQL like a newbie and SQL injection is handled. So this can also be viewed as a (very) simple introduction to how those libraries work.

The goal

The goal here is to be able to write code that works like this:

import java.sql.{Connection, ResultSet}

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

Which would replace code like this:

import java.sql.{Connection, ResultSet}
  
def get(key: String)(using connection: Connection): ResultSet = 
  val ps = connection.prepareStatement(
    """
      SELECT value
      FROM text_store
      WHERE key = ?
    """
  )
  ps.setString(1, key)
  ps.executeQuery()

So the core idea is straightforward

1. Replace every interpolated value with ?
2. Collect the values in order
3. Apply them to a PreparedStatement

But first let's look at the setup.

Setup

Just to make sure we are on the same page, let's create a simple table called text_store:

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

Let's continue with the scala code

String interpolation

Scala has a nice feature called string interpolation.

So adding support for SQL interpolation could be as simple as:

extension (sc: StringContext)
  def sql(args: Any*): Any = ???

It's an oversimplification, but it's enough for the compiler to support the sql. Which means we can write something like:

sql"""
  SELECT value
  FROM text_store
  WHERE key = ?
"""

Adding parameters

Using Any as arguments won’t work here, since an Any can't add values to a prepared statement.

So we need to define a type that can actually add values to a prepared statement. The simplest way to do that is to define a trait:

import java.sql.PreparedStatement

trait Param:
  def apply(ps: PreparedStatement, offset: Int): Unit

The Param for key will be responsible for doing something like ps.setString(1, key) for each value.

Which brings us:

extension (sc: StringContext)
  def sql(args: Param*): Any = ???

The problem here is that we would like to use a value like String or Int as a parameter - not a Param. So this brings us to the next step – defining a conversion from a type to a Param.

Converting values to parameters

The plan here is to define a conversion from a value – like a String, Int, Boolean, etc. – to a Param. Here we need a little helper that helps us in a few ways. First it limits the kinds of values that can be converted to the Param trait. Secondly, it helps us deal with the different ways to set values on a prepared statement. The name of the helper trait is Writer.

A Writer[T] can set a value of type T on a prepared statement. Here I'm extending the functionality of T by using a concept called type class). It is defined as:

trait Writer[T]:
  def setValue(ps: PreparedStatement, offset: Int, value: T): Unit

Then we need to make a conversion from a given value, eg key, into a Param. The conversion needs the right Writer[T] to do the job. Naturally, the compiler will look for a Writer[T] in scope and do an implicit conversion.

So let's add a given conversion from a T to the Param companion object.

object Param:
  def apply[T](value: T, writer: Writer[T]): Param =
    (ps: PreparedStatement, offset: Int) =>
      writer.setValue(ps, offset, value)

  given conversion[T: Writer as writer]: Conversion[T, Param] = (t: T) => Param(t, writer)

The apply method is just a helper method that creates a Param from a value and a Writer. The given conversion is the actual conversion from a value to a Param.

Supporting types

So let's add support for writing some of the most common types.

object Writer:
  given Writer[Int] = (ps, offset, value) => ps.setInt(offset, value)
  given Writer[Long] = (ps, offset, value) => ps.setLong(offset, value)
  given Writer[String] = (ps, offset, value) => ps.setString(offset, value)
  given Writer[Double] = (ps, offset, value) => ps.setDouble(offset, value)
  given Writer[Boolean] = (ps, offset, value) => ps.setBoolean(offset, value)

I leave adding more as an exercise for the reader. Here we're using the SAM (single abstract method) syntax instead of writing the full class. The result is the same.

For some, it will make sense to add a function like coMap[U](f: U => T): Writer[U]. That would allow for easy support of wrapped value types. I leave that as an exercise for the reader for now.

Getting a result set

So, going back to our original goal of supporting:

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

What is left is supporting asResultRet at the end. That means that the string interpolation should return something that can give us a result set.

Without jumping too far ahead and while keeping flexibility for later, I think it's natural for our string interpolation to return a SQL case class.

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

So this brings us to:

extension (sc: StringContext)
  def sql(params: Param*): SQL = 
    SQL(sc.parts.mkString("?"), params)

To clarify, sc.parts is a sequence of strings. Where every part is the full string separated by the values.

So sc.parts are the same as:

Seq("""
    SELECT value 
    FROM text_store
    WHERE key = """,
    """
  """) 

Which means the string is separated by the interpolated values into a sequence of strings.

So far, so good: we have a simple SQL interpolation.

Then next step is to add support for asResultSet on the SQL case class. I could add it as a method on the case class, but I think it's better to keep it separate for now. At least it makes it easier to explain things one step at a time, and keeping Connection out of the SQL case class.

So let's add it to the SQL as an extension method:

import java.sql.{Connection, ResultSet}

extension (sql: SQL)
  def asResultSet(using connection: Connection): ResultSet = 
    val ps = connection.prepareStatement(sql.statement)
    sql.params.zipWithIndex.foreach:
      (param, index) => param(ps, index + 1)
    ps.executeQuery()

I could have added asResultSet directly to the SQL case class. However, I wanted to keep SQL simple when I first introduced it.

So finally we have a working SQL interpolation that supports my initial goal of

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

All in one

To get the full picture; here is the full code.

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

trait Param:
  def apply(ps: PreparedStatement, offset: Int): Unit

object Param:
  def apply[T](value: T, writer: Writer[T]): Param =
    (ps: PreparedStatement, offset: Int) => 
      writer.setValue(ps, offset, value)
      
  given conversion[T: Writer as writer]: Conversion[T, Param] = 
    (t: T) => Param(t, writer)

trait Writer[T]:
  def setValue(ps: PreparedStatement, offset: Int, value: T): Unit

object Writer:
  given Writer[Int] = (ps, offset, value) => ps.setInt(offset, value)
  given Writer[Long] = (ps, offset, value) => ps.setLong(offset, value)
  given Writer[String] = (ps, offset, value) => ps.setString(offset, value)
  given Writer[Double] = (ps, offset, value) => ps.setDouble(offset, value)
  given Writer[Boolean] = (ps, offset, value) => ps.setBoolean(offset, value)

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

extension (sc: StringContext)
  def sql(params: Param*): SQL =
    SQL(sc.parts.mkString("?"), params)

extension (sql: SQL)
  def asResultSet(using connection: Connection): ResultSet =
    val ps = connection.prepareStatement(sql.statement)
    sql.params.zipWithIndex.foreach:
      (param, index) => param(ps, index + 1)
    ps.executeQuery()

The implementation is just shy of 40 lines of code. I think it's a good example of how simple it is to use string interpolation in Scala.

Further work

I think this is a good starting point. However, adding other extension methods like execute to SQL would be natural, to support other types of statements.

I would also like to add support for more writers, as I mentioned earlier.

A small side note: I wanted to show off Conversion, but it’s not really necessary. There are just a few changes to make that work. However, it's a nice exercise for the reader.

On the other hand, handling the ResultSet is a blog post in itself.

Summary

String interpolation in Scala has some interesting applications, and SQL is one of the more useful ones.

It’s not just string interpolation — there are a lot of nice Scala 3 features at work here. We have extension methods, type classes and conversions. It might be a bit overwhelming at first, but it's all quite simple. However, when combining them, it becomes quite powerful.