anorm-typed: Statically-Typed SQL Queries for Scala Play Applications

The Play framework's default persistence framework, Anorm, is a very thin wrapper around JDBC (the whole library is about 800 lines of code). Although I like the idea of a framework that treats a database as a database - instead of trying to shoehorn databases into the OO paradigm - Anorm has never really appealed to me. Since it's just a wrapper around SQL, you end up writing lots of raw SQL in your application. This is a problem, because the Scala compiler and typechecker have no opportunity to check your database interaction for errors. As flawed as ORM approaches can be, at least they can generate valid SQL for you. Consider this Anorm call from the Play! documentation:

  SQL(
    """
      select * from Country c 
      join CountryLanguage l on l.CountryCode = c.Code 
      where c.code = {countryCode};
   """
  ).on("countryCode" -> "FRA")

Here are just some of the ways this code can go wrong at runtime:

• A typo in an SQL keyword
• A typo in a column or table name
• Reference to a column or table that doesn't exist
• A typo in the "countryCode" key passed to the "on" function
• Passing in a non-string value for "countryCode"
• A mismatch between the parameters named in the query string and the keys passed to "on"

With Anorm's primary competitors (SLICK and Squeryl), you create mappings between columns and class fields, then use a query DSL to translate Scala Collections-like code into SQL. These frameworks are still vulnerable to some of the above problems, but they have some advantages:

• You map each column only once, so if you get the column's name or type wrong, there's only one place to correct it, and then the rest of your program will be free of that particular bug.
• These frameworks generate SQL themselves from a simple Scala DSL, so most syntax errors are ruled out.

Yet, these frameworks also introduce a number of issues:

• You need to manually maintain model mappings that can drift out of sync with the database
• The DSL's these libraries provide are necessarily limited. Some queries that would be straightforward and fast with pure SQL are simply inexpressible in these DSL's.
• Both mappings are database-agnostic. This has obvious advantages, but if you need to take advantage of a database-specific data type, function or syntactical convenience, you're out of luck.

About a month ago, Play developer Guillaume Bort announced a proof-of-concept implementation of a statically-checked version of Anorm, Play's persistence framework (source on Github). The framework was inspired by Joni Freeman's sqltyped framework. The main API of anorm-typed is the TypedSQL macro. When you compile a Scala file that contains TypedSQL calls, these calls are expanded into type-safe code that accepts parameters for any placeholders in the SQL and returns a tuple based on the column types selected in the query. Here's a short example:


  // assume
  // CREATE TABLE users(
  //    id integer,
  //    best_friend_id integer,
  //    name varchar(256)
  // );

  val q = TypedSQL("select * from users")
  q().list() // returns List[(Int, Int, String)]

  val q2 = TypedSQL("select name from users where id = ?")
  q2(5).single() // returns String

  val q3 = TypedSQL("update users set name = ? where id = 5")
  q3("Tyrone Slothrop").execute()

The anorm-typed module will catch every one of the errors I listed above - before your application can even compile. Note that everything here is type-checked, and that the code simply will not compile if we make a mistake matching Scala types to SQL types, if the SQL has a syntax error, if we use a nonexistent column, or if we provide the wrong number of arguments to the query. Awesome. Of course, there are some drawbacks to this approach:

• The TypedSQL macro needs to connect to your database during compilation. This can cause a number of issues:
  • CI servers or other automated builds will need to be able to access a database to finish compilation
  • IDE's have no idea what to do with the TypedSQL macro - IntelliJ highlights every call as an error, even though the code compiles fine.

Still, this is pretty close to my holy grail for database interaction. I'm planning to set aside some time to work on an alternative implementation that would suit my needs a little better: instead of a macro, I'm planning to build an SBT plugin for Play apps that would, as with the conf/routes compiler, compile a list of SQL queries into an autogenerated file.