Internal DSLs with Scala

I’ve been playing around with Scala again lately. Writing a (internal) DSL or a fluent api was still on todo-list.

Instead of writing some arbitrary language for a made-up domain I decided to pick a language and a domain I know: SQL. Or, a rather small subset.

The first step was creating a model of the language (no, I didn’t start with this diagram):


As you can see a query object consists of:

  • Operation (i.e. select, update, delete)
  • From
  • Where, containing various predicates and ways to combine predicates
  • Optional ordering

In Scala my implementation of the model looks like this:

case class Query(val operation:Operation, val from: From, val where: Where, val order: Option[Direction] = None) {
def order(dir: Direction) = this.copy(order = Option(dir))

abstract class Operation {
def from(table: String) = From(this, table)
case class Select(val fields:String*) extends Operation

case class From(val operation:Operation, val table: String) {
def where(clauses: Clause*): Query = Query(operation, this, Where(clauses:_*))

case class Where(val clauses: Clause*)

abstract class Clause {
def and(otherField: Clause): Clause = And(this, otherField)
def or(otherField: Clause): Clause = Or(this, otherField)

case class StringEquals(val f: String, val value: String) extends Clause
case class NumberEquals(val f: String, val value: Number) extends Clause
case class BooleanEquals(val f: String, val value: Boolean) extends Clause
case class In(val field: String, val values: String*) extends Clause
case class And(val clauses: Clause*) extends Clause
case class Or(val clauses: Clause*) extends Clause

abstract class Direction
case class Asc(field: String) extends Direction
case class Desc(field: String) extends Direction

As you can see the code is a straightforward implementation of the model, only using immutable values. I added some utility methods which will be used to ‘chain’ objects:

  • Query#order – Clones the query object and overrides the order with the specified order
  • Operation#from – creates a from clause from the operation object
  • Clause#and / Clause#or – combines two clauses

Next to the model I created a QueryBuilder object which contains some implicit conversions and utility methods:

object QueryBuilder {
implicit def tuple2field(t: (String, String)): StringEquals = StringEquals(t._1, t._2)
implicit def tuple2field(t: (String, Int)): NumberEquals = NumberEquals(t._1, t._2)
implicit def tuple2field(t: (String, Boolean)): BooleanEquals = BooleanEquals(t._1, t._2)

/** entrypoint for starting a select query */
def select(fields:String*) = Select(fields:_*)
def in(field: String, values: String*) = In(field, values: _*)

The implicit conversions allow tuples to be converted to typed case classes. With the above we can write Scala code which resembles SQL. I Wrote some tests (using ScalaTest) which demonstrate how it works. Example inputs for my tests include:

val q = select ("*") from ("user") where (("name","peter") and (("active", true) or ("role", "admin")))
val q = select ("*") from ("user") where (("name","p'eter"))
val q = select ("*") from ("user") where (("id", 100))
val q = select ("*") from ("user") where (in("name","pe'ter","petrus"))
val q = select ("*") from ("user") where (("name","peter")) order Desc("name")

To generate a SQL String from a Query object I wrote a fairly basic generator and an implicit conversion to convert queries to SQL:

case class SQL(val sql:String)

object AnsiSqlRenderer {
implicit def query2sql(q:Query):SQL = SQL(sql(q))

def sql(q: Query): String = {
).mkString(" ").trim

def expandOperation(q:Query):String = q.operation match {
case Select(fields) => "select %s".format(fields.mkString(","))
case _ => throw new IllegalArgumentException("Operation %s not implemented".format(q.operation))

def expandFrom(q: Query) = "from %s".format(q.from.table)
def expandWhere(q: Query) = "where %s".format(" "))

def expandClause(clause: Clause): String = clause match {
case StringEquals(field, value) => "%s = %s".format(field, quote(value))
case BooleanEquals(field, value) => "%s = %s".format(field, value)
case NumberEquals(field, value) => "%s = %s".format(field, value)
case in:In => "%s in (%s)".format(in.field,","))
case and:And =>"(", " and ", ")")
case or:Or =>"(", " or ", ")")
case _ => throw new IllegalArgumentException("Clause %s not implemented".format(clause))

def expandOrder(q: Query) = q.order match {
case Some(direction) => direction match {
case Asc(field) => "order by %s asc".format(field)
case Desc(field) => "order by %s desc".format(field)
case None => ""

def quote(value: String) = "'%s'".format(escape(value))
def escape(value: String) = value.replaceAll("'", "''")

Most of the beef is in (recursively) expanding the where clause into a String. When using the implicit conversion you can now do the following:

scala> val q = select ("*") from ("user") where (("name","peter") and (("active", true) or ("role", "admin")))
scala> q.sql
res0: java.lang.String = select * from user where (name = 'peter' and (active = true or role = 'admin'))

Apart from the parentheses (probably I’ll figure out how to get rid of some more of them one day) the two look very similar. But the first one creates a typesafe object graph which can be rendered/validated/manipulated in various ways!

The example could be extended to allow ‘greater/smaller then’ clauses (using operator overloading?) or joins in the from clause; feel free to clone my repository and do so!

Full sources can be found in the Scala-SQL-DSL github repository.

This entry was posted in dsl, scala. Bookmark the permalink.

6 Responses to Internal DSLs with Scala

  1. Paolo Losi says:

    to get rid of some more parens try calling like this:

    where (“name” -> “peter”)

  2. hb says:

    Cool. Thanks for posting.

  3. juan uys says:

    Have a look at the source code for Mapper (the ORM used with the Lift web framework).

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>