Parsing With ANORM in Play Framework 2

Being used to JPA/Hibernate it took a while to figure out how powerful and easy ANORM actually is. So as a help for other people trying to wrap their head around ANORM, here are some hints:

Parse one db table (or query result) with many columns into smaller parts:

One common problem is creating a more rich domain model than the database schema, this is how you could achieve that by combining parsers:

table COMPANY(name, street, zipcode, city)
case class Address(street: String, zip: String, city: String)
 val addressParser = str("street") ~ str("zipcode") ~ str("city"
) map {
  case street~zip~city => Address(street, zip, city)
}
case class Company(name: String, address: Address)
val companyParser = str("name") ~ addressParser map {
  case name~address => Company(name, address)
}

Construct re-usable parser with functions:

Sometimes you have a domain object such as an address that might occur in multiple other domain classes but still not being put in one single database table, or perhaps multiple instances of address belonging to one owner stored flat in the table of the owning entity. This example uses a function to generate the first parser with different prefixes and then using two of those to map the table:

table: PERSON(name, home_street, home_city, work_street, work_city)
case class Address(street: String, city: String)
def addressParser(prefix: String) = str(prefix + "street") ~ str(prefix + "city") map {
  case street~zip~city => Address(street, zip, city)
}
case class Person(name: String, home: Address, work: Option[Address])
val personParser = str("name") ~ addressParser("home_") ~ addressParser("work_") map {
  case name~home~work => Person(name, home, work)
}

Re-usable optional parsers

Lets say the address in the first example was optional, the address fields might all be null, lets represent that as an Option[Address] rather than making each field of address optional:

case class Company(name: String, address: Option[Address])
val companyParser = str("name") ~ (addressParser?) map {
  case name~address => Company(name, address)
}

This solution also works well for LEFT JOINs where you want to parse two domain classes but where the second may not exist:

def aWithDetails: (A, Option[ADetails]) =
  SQL("SELECT * FROM a LEFT JOIN a_details.a_id = a.id")
    .as(aParser ~ (aDetailsParser?)*)

Abstraction on top of anorm

Some stuff you want to do all the time, and that might get repetetive doing it low-level with anorm for each model-companion-DAO in your domain model. This can be done in many ways, here is one example for simplifying querying which uses the column names as parameter names to generate a query:

def findWhere[A](table: String, params: Seq[(String, ParameterValue[Any])], parser: RowParser[A]): Seq[A] =
  DB.withConnection { implicit connection =>
    val query = "SELECT * FROM " + table +
      (if (params.isEmpty) {
        ""
      } else {
        params.map { case (col, _) => col + " = {" + col + "}" }
          .mkString(" AND ")
      })
    SQL(query).on(params).as(parser*)
  }

This utility function can then be used like this, with the values being implicitly converted to ParameterValue[Any]:

def findById(id: Long): Option[Post] =
  findWhere("post", Seq("id" -> id), parser).headOption
def findByTitle(title: String): Option[Post] =
  findWhere("post", Seq("title" -> title), parser).headOption
def all: Seq[Post] = findWhere("post", Nil, parser)