Raible Designs

Play Scala's Anorm, Heroku and PostgreSQL Issues

This article is the 5th in a series on about my adventures developing a Fitness Tracking application for my talk at Devoxx in two weeks. Previous articles can be found at:

1. Integrating Scalate and Jade with Play 1.2.3
2. Trying to make CoffeeScript work with Scalate and Play
3. Integrating HTML5 Boilerplate with Scalate and Play
4. Developing with HTML5, CoffeeScript and Twitter's Bootstrap

Anorm
In my previous article, I described how I created my application's features using CoffeeScript and make it look good using Twitter's Bootstrap. Next, I turned to persisting this data with Anorm.

The Scala module includes a brand new data access layer called Anorm that uses plain SQL to make your database request and provides several API to parse and transform the resulting dataset.

I'm a big fan of ORMs like Hibernate and JPA, so having to learn a new JDBC abstraction wasn't exactly appealing at first. However, since Anorm is the default for Play Scala, I decided to try it. The easiest way for me to learn Anorm was to start coding with it. I used A first iteration for the data model as my guide and created model objects, companion objects that extended Magic (appropriately named) and wrote some tests using scalatest. I started with an "Athlete" model since I knew "User" was a keyword in PostgreSQL and that's what Heroku uses for its database.

package models

import play.db.anorm._
import play.db.anorm.defaults._

case class Athlete(
  id: Pk[Long],
  email: String, password: String, firstName: String, lastName: String
  ) {
}

object Athlete extends Magic[Athlete] {
  def connect(email: String, password: String) = {
    Athlete.find("email = {email} and password = {password}")
      .on("email" -> email, "password" -> password)
      .first()
  }

  def apply(firstName: String) = new Athlete(NotAssigned, null, null, firstName, null)
}

Then I wrote a couple tests for it in test/Tests.scala.

import play._
import play.test._

import org.scalatest._
import org.scalatest.junit._
import org.scalatest.matchers._

class BasicTests extends UnitFlatSpec with ShouldMatchers with BeforeAndAfterEach {

  import models._
  import play.db.anorm._

  override def beforeEach() {
      Fixtures.deleteDatabase()
  }

  it should "create and retrieve a Athlete" in {

      var athlete = Athlete(NotAssigned, "[email protected]", "secret", "Jim", "Smith")
      Athlete.create(athlete)

      val jim = Athlete.find(
          "email={email}").on("email" -> "[email protected]"
      ).first()

      jim should not be (None)
      jim.get.firstName should be("Jim")

  }

  it should "connect a Athlete" in {

      Athlete.create(Athlete(NotAssigned, "[email protected]", "secret", "Bob", "Johnson"))

      Athlete.connect("[email protected]", "secret") should not be (None)
      Athlete.connect("[email protected]", "badpassword") should be(None)
      Athlete.connect("[email protected]", "secret") should be(None)
  }

At this point, everything was fine and dandy. I could run "play test", open http://localhost/@tests in my browser and run the tests to see a beautiful shade of green on my screen. I continued following the tutorial, substituting "Post" with "Workout" and added Comments too. The Workout object shows some of the crazy-ass syntax that is Anorm getting fancy with Scala.

object Workout extends Magic[Workout] {

  def allWithAthlete: List[(Workout, Athlete)] =
    SQL(
      """
          select * from Workout w
          join Athlete a on w.athlete_id = a.id
          order by w.postedAt desc
      """
    ).as(Workout ~< Athlete ^^ flatten *)

  def allWithAthleteAndComments: List[(Workout, Athlete, List[Comment])] =
    SQL(
      """
          select * from Workout w
          join Athlete a on w.athlete_id = a.id
          left join Comment c on c.workout_id = w.id
          order by w.postedAt desc
      """
    ).as(Workout ~< Athlete ~< Workout.spanM(Comment) ^^ flatten *)

  def byIdWithAthleteAndComments(id: Long): Option[(Workout, Athlete, List[Comment])] =
    SQL(
      """
          select * from Workout w
          join Athlete a on w.athlete_id = a.id
          left join Comment c on c.workout_id = w.id
          where w.id = {id}
      """
    ).on("id" -> id).as(Workout ~< Athlete ~< Workout.spanM(Comment) ^^ flatten ?)
}

All of these methods return Tuples, which is quite different from an ORM that returns an object that you call methods on to get its related items. Below is an example of how this is referenced in a Scalate template:

-@ val workout:(models.Workout,models.Athlete,Seq[models.Comment])
-
  var commentsTitle = "No Comments"
  if (workout._3.size > 0)
    commentsTitle = workout._3.size + " comments, lastest by " + workout._3(workout._3.size - 1).author
  
div(class="workout")
  h2.title
    a(href={action(controllers.Profile.show(workout._1.id()))}) #{workout._1.title}
  .metadata
    span.user Posted by #{workout._2.firstName} on
    span.date #{workout._1.postedAt}
    .description
      = workout._1.description

Evolutions on Heroku
I was happy with my progress until I tried to deploy my app to Heroku. I added db=${DATABASE_URL} to my application.conf as recommended by Database-driven web apps with Play! on Heroku/Cedar. However, when I deployed, it failed because my database tables weren't created.

2011-10-05T04:08:52+00:00 app[web.1]: 04:08:52,712 WARN  ~ Your database is not up to date.
2011-10-05T04:08:52+00:00 app[web.1]: 04:08:52,712 WARN  ~ Use `play evolutions` command to manage database evolutions.
2011-10-05T04:08:52+00:00 app[web.1]: 04:08:52,713 ERROR ~
2011-10-05T04:08:52+00:00 app[web.1]:
2011-10-05T04:08:52+00:00 app[web.1]: @681m15j3l
2011-10-05T04:08:52+00:00 app[web.1]: Can't start in PROD mode with errors
2011-10-05T04:08:52+00:00 app[web.1]:
2011-10-05T04:08:52+00:00 app[web.1]: Your database needs evolution!
2011-10-05T04:08:52+00:00 app[web.1]: An SQL script will be run on your database.
2011-10-05T04:08:52+00:00 app[web.1]:
2011-10-05T04:08:52+00:00 app[web.1]: play.db.Evolutions$InvalidDatabaseRevision

With James Ward's help, I learned I needed to use "heroku run" to apply evolutions. So I ran the following command:

heroku run "play evolutions:apply --%prod" 

Unfortunately, this failed:

Running play evolutions:apply --%prod attached to terminal... up, run. 
5 
~        _            _ 
~  _ __ | | __ _ _  _| | 
~ | '_ \| |/ _' | || |_| 
~ |  __/|_|\____|\__ (_) 
~ |_|            |__/ 
~ 
~ play! 1.2.3, http://www.playframework.org 
~ framework ID is prod 
~ 
Oct 17, 2011 7:05:46 PM play.Logger warn 
WARNING: Cannot replace DATABASE_URL in configuration (db=$ 
{DATABASE_URL}) 
Exception in thread "main" java.lang.NullPointerException 
        at play.db.Evolutions.main(Evolutions.java:54)

After opening a ticket with Heroku support, I learned this was because DATABASE_URL was not set ("heroku config" shows your variables). Apparently, this should be set when you create your app, but somehow wasn't for mine. To fix, I had to run the following command:

$ heroku pg:promote SHARED_DATABASE 
-----> Promoting SHARED_DATABASE to DATABASE_URL... done

PostgreSQL and Dates
The next issue I ran into was with loading default data. I have the following BootStrap.scala class in my project to load default data:

class BootStrap extends Job { 
  override def doJob() { 
    import models._ 
    import play.test._ 
    // Import initial data if the database is empty 
    if (Athlete.count().single() == 0) { 
      Yaml[List[Any]]("initial-data.yml").foreach { 
        _ match { 
          case a: Athlete => Athlete.create(a) 
          case w: Workout => Workout.create(w) 
          case c: Comment => Comment.create(c) 
        } 
      } 
    } 
  } 
} 

For some reason, only my "athlete" table was getting populated and the others weren't. I tried turning on debugging and trace, but nothing showed up in the logs. This appears to be a frequent issue with Play. When data fails to load, there's no logging indicating what went wrong. To make matters worse with Anorm, there's no way to log the SQL that it's attempting to run. My BootStrap job was working fine when connecting to "db=mem", but stopped after switching to PostgreSQL. The support I got for this issue was disappointing, since it caused crickets on Play's Google Group. I finally figured out "support of Date for insertion" was added to Anorm a couple months ago.

To get the latest play-scala code into my project, I cloned play-scala, built it locally and uploaded it to my server. Then I added the following to dependencies.yml and ran "play deps --sync".

require:
    ...
    - upgrades -> scala 0.9.1-20111025
    ...

repositories:
    - upgrades:
        type: http
        artifact: "http://static.raibledesigns.com/[module]-[revision].zip"
        contains:
            - upgrades -> *

Summary
When I started writing this article, I was going to talk about some improvements I made to Scalate Play interoperability. However, I think I'll save that for next time and possibly turn it into a plugin using play-excel as an example.

As you can tell from this article, my experience with Anorm was frustrating - particularly due to the lack of error messages when operations failed. The lack of support was expected, as this usually happens when you're living on the bleeding edge. However, based on this experience, I can't help but think that it might be a while before Play 2.0 is ready for production use.

The good news is IntelliJ is adding support for Play. Maybe this will help increase adoption and inspire the framework's developers to stabilize and improve Play Scala before moving the entire framework to Scala. After all, it seems they've encountered some issues making Scala as fast as Java.