Play Framework Evolutions with Slick 2.0 Code Generator

The new Slick 2.0 database library comes with a simple code generator that generates Scala code for all your tables so you can quickly start coding against your database. It takes into consideration not only the tables and columns, but also primary keys and foreign key relationships.

Integration with Play Framework Evolutions – No More Boilerplate

So I thought it might be interesting to integrate this code generator with the Play Framework 2.2. Play has an Evolutions Plugin that takes care of database migrations. A basic evolutions file looks like this:

# /conf/evolutions/default/1.sql

# --- !Ups

CREATE TABLE `user`(
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `first_name` VARCHAR(255) NOT NULL,
    `last_name` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255),
    `create_date` DATETIME NOT NULL,
    UNIQUE KEY `email_uk` (`email`)
);

# --- !Downs

DROP TABLE `user`;

Now we want the Play Framework to automatically generate the necessary Slick code for this table, so that we’re ready to query our database, for example:

// lets print the ids of all users named chris
val users = User.filter(_.firstName like "%chris%").list
println(users.map(_.id).mkString(", "))

Of course, if we later remove the “first_name” column  from the user table (in a later migration for example), we want the compiler to tell us that the column is no longer there. This is a huge productivity boost when dealing with many tables, and avoids writing a lot of boilerplate code.

How can this be done?

My basic idea was to write small Play module that takes care of the code generation. Dependency on the Play Framework is needed because we need the Evolutions Plugin to know how our current database looks like.

You can find my working prototype of this here on GitHub. If you just want to give it a try in your own project, take the play module from my code and make the necessary changes to your SBT build file.

If you’re interested in how it works, read on…

Introducing the DbGen Play Module

The sample prototype project uses a Play sub-module called “dbgen” for code generation. The “dbgen” module consists of just one class (PlaySlickCodeGenerator.scala) and takes care of applying evolutions from the main project into an in-memory database (configured here: /dbgen/conf/application.conf). The main method starts a fake Play application, invokes the Evolutions plugin, and then calls the Slick code generator.

Of course, now we want to have a seamless integration into our build process while developing. SBT takes care of this, we just have to provide a new build task.

Automatic Code Generation when Evolutions change

The new build task should only run if our Evolutions (and therefore our database) have changed. Lets take a look at our Build.scala. With the main module and the DbGen submodule, it  looks like this:

// main Play project
val main = play.Project(appName, appVersion, appDependencies).settings(
  slickCodeGen <<= slickCodeGenTask, // register manual sbt command
  sourceGenerators in Compile <+= slickCodeGenTask // generate slick code
).dependsOn(dbGen)

// Slick code generator module
lazy val dbGen = play.Project("dbgen", appVersion, appDependencies, path = file("dbgen"))

// Code generation task
lazy val slickCodeGen = TaskKey[Seq[File]]("gen-tables")
lazy val slickCodeGenTask = ... // see complete Build.scala

This defines both modules, and that the main project depends on the dbgen code generator. Also, we define a new code generator task slickCodeGen that can be invoked from the play console (if we want to start it manually), as well as an entry for automatic source code generation (sourceGenerators in Compile).

The actual SBT task is also defined in the Build.scala, but not listed here for brevity. It basically checks whether evolutions have changed, and starts the PlaySlickCodeGenerator to generate the code if necessary. The output is placed into the “target\scala-2.10\src_managed\main” directory alongside other generated code by Play (routes, templates, etc.). It will be automatically picked up by IntelliJ IDEA and other Scala IDEs, for auto-completion support and other goodies.

The code generator can also be started manually from the play console using the “gen-tables” task:

$ play gen-tables
[info] Database evolutions have changed. Generating Slick code.
[success] Total time: 1 s, completed 29.12.2013 19:16:26

What you can do with this

  • You can now continue using Play evolutions, and your Slick tables will be automatically generated. No more Slick boilerplate.
  • When you make changes to your database, hit refresh and you’ll see the new tables and columns appear in auto-complete. Even have all the metadata like foreign key relationships.
  • Get compiler hints whenever database changes break your code.

Known Limitations:

The SQL in the Evolutions files has to be compatible with both your production database and the H2 in-memory database. H2 can be configured to be compatible with most databases. This example uses MySQL, hence it activates the MySQL compatibility mode of H2.

Request for comments

There’s got to be a better way to integrate the Evolutions Plugin with SBT. If you have an idea to make the solution cleaner or avoid having to use a fake Play Application, please let me know in the comments. If you found this useful, please let me know as well ;-)

 

 

This entry was posted in Play framework, Slick, Web Development and tagged , , , , , . Bookmark the permalink.
  • Pingback: Using Play Framework 2 in production - Chris' tech blog

  • mbseid

    This seems really sweet. I’m excited to play around with it later. I’ll definitely provide feedback. Thanks!

  • Marc

    Hi there — would you consider proposing this as a possible addition to the play-slick plugin? I’m currently integrating my own project (scalaquery-play-iteratees) there as well, and once that is done, I may be a resource to help you with this.

    • http://blog.papauschek.com/ Christian Papauschek

      Definitely, I already thought about some kind of integration with play-slick, but I’m not sure how it would work in practice – I would need input from someone more familiar with SBT. Play-slick right now is a Play plugin. I think for code-generator functionality it would have to be a SBT plugin. Would you be able to help with this?

      I opened a feature request for play-slick here:
      https://github.com/freekh/play-slick/issues/118

  • amedina

    Just a short question, maybe I am missing something. Why do you need the intermediate step with the in-memory database? Wouldn’t it be possible to simply apply the evolutions to the standard dev-db and then invoke the code-generator there?

    • http://blog.papauschek.com/ Christian Papauschek

      Yes in principle it’s possible to get the schema directly from your development database – but I found it not very practical.

      For example, you may want to run test code without any real database installed, and want to use in-memory stuff. Then you would not even be able to COMPILE your project without, say, a MySQL instance.