Matt RaibleMatt Raible is a Web Architecture Consultant specializing in open source frameworks.

10+ YEARS


Over 10 years ago, I wrote my first blog post. Since then, I've authored books, had kids, traveled the world, found Trish and blogged about it all.

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, "jim@gmail.com", "secret", "Jim", "Smith")
      Athlete.create(athlete)

      val jim = Athlete.find(
          "email={email}").on("email" -> "jim@gmail.com"
      ).first()

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

  }

  it should "connect a Athlete" in {

      Athlete.create(Athlete(NotAssigned, "bob@gmail.com", "secret", "Bob", "Johnson"))

      Athlete.connect("bob@gmail.com", "secret") should not be (None)
      Athlete.connect("bob@gmail.com", "badpassword") should be(None)
      Athlete.connect("tom@gmail.com", "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.

Posted in Java at Nov 02 2011, 11:54:25 AM MDT 6 Comments
Comments:

Thanks a lot for the article.

Although keep learning new technologies is good to keep yourself on the top; however, I still believe that it is better to master one technology and use it as your mature choice. If I am going to change my technology every time I am implementing a new project then I am going to trash all my experience and start from scratch which will lead to new kind of issues and frustration.

Many of the frameworks in JavaLand promise high productivity and agile approach, I don't mind to learn these new frameworks, but believe me the learning curve is going to reduce your productivity as you work through a practical project (not the WoW videos every framework is showing).

Posted by Hussam on November 02, 2011 at 02:09 PM MDT #

I'm glad you got this working! Your Devoxx session is gonna rock!

Do you know if they are sticking with Anorm in Play 2?

-James

Posted by James Ward on November 04, 2011 at 07:17 AM MDT #

James - thanks for the encouraging words. I'm definitely looking forward to Devoxx ... I'm getting thirsty just thinking about it. ;)

According to the Play Framework Google Group: Yes ->

"Anorm will be integrated by default in the Scala part. Since Anorm is not an ORM, we might provide a sample integration of an ORM for people that want to stick to ORMs meanwhile."

Posted by Matt Raible on November 04, 2011 at 02:17 PM MDT #

Your series of articles about Scala and play is really interesting. I wish i could attend your presentation at Devoxx.

But i am a little bit skeptical about the anorm/play combination and Scala usage in general for web development. I was wondering if at least it improved code readability and i think it fails reaching this goal (talking about Anorm and the parser combinators) . I tried to understand this whole parser combinator stuff and the following line of you example:

(Workout ~< Athlete ~< Workout.spanM(Comment) ^^ flatten *)

After 10mn of research on the internet, i am still not sure that i understand it (i am in the process of learning Scala). I am not sure that using Scala would help me being more productive and improve the maintainability of my code !

Posted by lucdew on November 07, 2011 at 09:39 PM MST #

> I started with an "Athlete" model since I knew "User" was a keyword in PostgreSQL

You can use a different table name like this:

object User extends Magic[User](Some("AthleteUser"))

Posted by Marius on November 17, 2011 at 10:29 AM MST #

Why is having to write your own SQL, query by query, better than Hibernate?

For a simple Search page, users expect to be able to enter search strings into various fields & generate different criteria. Searching on any of Order Name, Customer, Contact Phone, Delivery Address, Date, Products.. etc).

The above Anorm approach obviously wouldn't scale to *combination* searches. eg search by Customer *and* Order Name.

I'm not sure at all, either, why losing object structure & having all the data in less-strongly typed is meant to be better?

I thought domain objects were the important part of the application -- where we should have type information, named members & similar "modern" (post 1960) conveniences of programmability.

Numbered tuple slots are little better than numbered registers, no? What is this.. a crazed mixture of SQL and type-checked assembly language?

Posted by Thomas Whitmore on October 14, 2013 at 06:50 PM MDT #

Post a Comment:
  • HTML Syntax: Allowed