Play! 2.1 RowParsers and LEFT JOINs

If you've used Play! Framework version 2.1, and you've tried out the RowParser API, you may have wondered, as I have, how to handle LEFT JOIN queries, since the rows from the left table will appear at least once and the rows from the right table may or may not appear. If the right table does appear, you want to parse the data using an existing RowParser, rather than having some custom parser and case class which has all Option fields.

In my initial approach, inspired by this question on the Play! Framework Google Group, was a bit parentheses-heavy.
// Parser for "foo" table
val foo = get[Long]("foo.id")

// Parser for "bar" table
val bar = get[Long]("bar.id")

// Combined parser
val combo = foo ~ (bar ?)

// Results as List[~[Long, Option[Long]]]
val results = SQL("SELECT * FROM foo LEFT JOIN bar ON foo.id = bar.foo_id").as(combo *)
Since the parentheses bothered me a bit, I enriched the RowParser class to provide a convenience method ~? which combines the two functions.
implicit def enrichRowParser[A](a: RowParser[A]) = new RichRowParser(a)

class RichRowParser[A](val delegate: RowParser[A]) extends AnyVal {

def ~?[B](p: RowParser[B]) = {
delegate ~ (p ?)
}

}
The declaration for combo can then be redefined without the parentheses which seems a bit more readable.
val combo = foo ~? bar

Using PostgreSQL's native JSON support in Play Framework 2.1-RC1

I have been experimenting with PostgreSQL 9.2's native json datatype, and wanted to have Play Framework 2.1-RC1 automatically convert the raw PostgreSQL JDBC PGobject object into a Play-specific JsValue object. Neither Play nor JDBC understand PostgreSQL's native json datatype, so neither library provides an implicit conversion. Automatic conversion can be performed by defining a parameter extractor. Without a parameter extractor, a compilation error will occur:

could not find implicit value for parameter extractor: anorm.Column[play.api.libs.json.JsValue]

In my case, I could have worked around this by extracting the value as a String and parsing this value. Note that because the data field is marked NOT NULL, I don't need to use an Option[String] in the extractor.

get[Pk[Long]]("sample.id") ~
get[String]("sample.data") map {
  case id~data => Sample(id, Json.parse(data))
}

For INSERTs and UPDATEs, I'm using the PostgreSQL CAST keyword as I haven't experimented with how to get the JsValue object converted transparently into a JDBC object (presumably of type PGobject) or defining a new varchar → json CAST on the database side. Without the CAST() the INSERT fails because the JDBC driver doesn't set the proper type:

org.postgresql.util.PSQLException: ERROR: column "data" is of type json but expression is of type character varying
Hint: You will need to rewrite or cast the expression.

Here is the sample schema:

CREATE TABLE
  sample
(
  id serial PRIMARY KEY,
  data json NOT NULL
);

Here's how a Sample model looks:

package models

import anorm._
import anorm.SqlParser._
import play.api.db._
import play.api.Play.current
import play.api.libs.json.{Json,JsValue}

case class Sample (
  id: Pk[Long] = NotAssigned,
  data: JsValue
)

object Sample {

  val extractor = {
    get[Pk[Long]]("sample.id") ~
    get[JsValue]("sample.data") map {
      case id~data => Sample(id, data)
  }

  implicit def rowToJsValue: Column[JsValue] = Column.nonNull { (value, meta) =>
    val MetaDataItem(qualified, nullable, clazz) = meta
    value match {
      case pgo: org.postgresql.util.PGobject => Right(Json.parse(pgo.getValue))
      case _ => Left(TypeDoesNotMatch("Cannot convert " + value + ":" +
          value.asInstanceOf[AnyRef].getClass + " to JsValue for column " + qualified))
    }
  }

  def list() = {
    DB.withConnection { implicit connection =>
      SQL(
        """
          SELECT
            *
          FROM
            sample
        """
      ).as(Sample.extractor *)
    }
  }

  def create(data: JsValue) = {
    DB.withConnection { implicit connection =>
      SQL(
        """
          INSERT INTO
            sample
          (
            data
          )
          VALUES(
            CAST({data} AS json)
          )
          RETURNING id
        """
      ).on(
        'data -> Json.stringify(data)
      ).as(scalar[Long].single)
    }
  }
}

This approach is inspired by and adapted from a StackOverflow question.

PostgreSQL JDBC pool exhaustion under Play Framework 2.1-RC1

After experimenting with Scala and Play Framework version 2.1-RC1 and the default H2 Database, I decided to switch over to use PostgreSQL for persistence.

After installing PostgreSQL 9.2.2 and creating my test user and database, I reconfigured my Play application.

Update project/Build.scala to include the PostgreSQL driver as a dependency. val appDependencies = Seq( jdbc, anorm, "postgresql" % "postgresql" % "9.1-901-1.jdbc4" )

Update conf/application.conf to use the PostgreSQL driver for the database named "default". db.default.driver=org.postgresql.Driver db.default.url="postgres://testuser:testpass@localhost:5432/testdb" #db.default.user=sa #db.default.password=""

When applying database evolutions, the database connection pool would be exhausted:

org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections

The only solution was to restart the Play application entirely as I wasn't inclined to increase the connection limit without knowing why the existing limit was being hit. Re-applying the evolution would trigger the problem again.

Per Play's JDBC settings documentation, I could adjust connection pools, but as far as I could tell, the pool size wasn't the actual issue, since PostgreSQL was configured to support more connections than Play should have been using.

In my case, the problem was due to how Play handles semicolons included in a quoted string within a DML statement (initially, I had attributed this to how JDBC itself handled semicolons). You can view the current approach to statement splitting in framework/src/play-jdbc/src/main/scala/play/api/db/evolutions/Evolutions.scala line 233. Questions about this approach can be viewed on Play's Google Group and on Play 2.0 pull request #134.

This issue causes the query to fail execution and the Evolutions code retries enough times to consume the server's available connections. As I'm inserting seed data, I removed the semicolon from the quoted string and corrected the values after the evolution was applied.