How to write database-agnostic Play application and perform first-time database initialization?

Question

I'm using Slick with a Play Framework 2.1 and I have some troubles.

Given the following entity...

package models

import scala.slick.driver.PostgresDriver.simple._

case class Account(id: Option[Long], email: String, password: String)

object Accounts extends Table[Account]("account") {
  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def email = column[String]("email")
  def password = column[String]("password")
  def * = id.? ~ email ~ password <> (Account, Account.unapply _)
}

...I have to import a package for a specific database driver, but I want to use H2 for testing and PostgreSQL in production. How should I proceed?

I was able to workaround this by overriding the driver settings in my unit test:

package test

import org.specs2.mutable._

import play.api.test._
import play.api.test.Helpers._

import scala.slick.driver.H2Driver.simple._
import Database.threadLocalSession

import models.{Accounts, Account}

class AccountSpec extends Specification {

  "An Account" should {
    "be creatable" in {
      Database.forURL("jdbc:h2:mem:test1", driver = "org.h2.Driver") withSession {
        Accounts.ddl.create                                                                                                                                          
        Accounts.insert(Account(None, "[email protected]", "Password"))
        val account = for (account <- Accounts) yield account
        account.first.id.get mustEqual 1
      }
    }
  }
}

I don't like this solution and I'm wondering if there is an elegant way to write DB-agnostic code so there are two different database engines used - one in testing and another in production?

I don't want to use evolution, either, and prefer to let Slick create the database tables for me:

import play.api.Application
import play.api.GlobalSettings
import play.api.Play.current
import play.api.db.DB

import scala.slick.driver.PostgresDriver.simple._
import Database.threadLocalSession

import models.Accounts

object Global extends GlobalSettings {

  override def onStart(app: Application) {
    lazy val database = Database.forDataSource(DB.getDataSource())

    database withSession {
      Accounts.ddl.create
    }
  }
}

The first time I start the application, everything works fine... then, of course, the second time I start the application it crashes because the tables already exist in the PostgreSQL database.

That said, my last two questions are:

1. How can I determine whether or not the database tables already exist?
2. How can I make the onStart method above DB-agnostic so that I can test my application with FakeApplication?

Typewriting

As of 2015 the best option is probably play-slick plugin: https://mcmap.net/q/325009/-how-to-write-database-agnostic-play-application-and-perform-first-time-database-initialization – Lille

Answer 1

You find an example on how to use the cake pattern / dependency injection to decouple the Slick driver from the database access layer here: https://github.com/slick/slick-examples.

How to decouple the Slick driver and test application with FakeApplication

A few days ago I wrote a Slick integration library for play, which moves the driver dependency to the application.conf of the Play project: https://github.com/danieldietrich/slick-integration.

With the help of this library your example would be implemented as follows:

1) Add the dependency to project/Build.scala

"net.danieldietrich" %% "slick-integration" % "1.0-SNAPSHOT"

Add snapshot repository

resolvers += "Daniel's Repository" at "http://danieldietrich.net/repository/snapshots"

Or local repository, if slick-integration is published locally

resolvers += Resolver.mavenLocal

2) Add the Slick driver to conf/application.conf

slick.default.driver=scala.slick.driver.H2Driver

3) Implement app/models/Account.scala

In the case of slick-integration, it is assumed that you use primary keys of type Long which are auto incremented. The pk name is 'id'. The Table/Mapper implementation has default methods (delete, findAll, findById, insert, update). Your entities have to implement 'withId' which is needed by the 'insert' method.

package models

import scala.slick.integration._

case class Account(id: Option[Long], email: String, password: String)
    extends Entity[Account] {
  // currently needed by Mapper.create to set the auto generated id
  def withId(id: Long): Account = copy(id = Some(id))
}

// use cake pattern to 'inject' the Slick driver
trait AccountComponent extends _Component { self: Profile =>

  import profile.simple._

  object Accounts extends Mapper[Account]("account") {
    // def id is defined in Mapper
    def email = column[String]("email")
    def password = column[String]("password")
    def * = id.? ~ email ~ password <> (Account, Account.unapply _)
  }

}

4) Implement app/models/DAL.scala

This is the Data Access Layer (DAL) which is used by the controllers to access the database. Transactions are handled by the Table/Mapper implementation within the corresponding Component.

package models

import scala.slick.integration.PlayProfile
import scala.slick.integration._DAL
import scala.slick.lifted.DDL

import play.api.Play.current

class DAL(dbName: String) extends _DAL with AccountComponent
    /* with FooBarBazComponent */ with PlayProfile {

  // trait Profile implementation
  val profile = loadProfile(dbName)
  def db = dbProvider(dbName)

  // _DAL.ddl implementation
  lazy val ddl: DDL = Accounts.ddl // ++ FooBarBazs.ddl

}

object DAL extends DAL("default")

5) Implement test/test/AccountSpec.scala

package test

import models._
import models.DAL._
import org.specs2.mutable.Specification
import play.api.test.FakeApplication
import play.api.test.Helpers._
import scala.slick.session.Session

class AccountSpec extends Specification {

  def fakeApp[T](block: => T): T =
    running(FakeApplication(additionalConfiguration = inMemoryDatabase() ++
        Map("slick.default.driver" -> "scala.slick.driver.H2Driver",
          "evolutionplugin" -> "disabled"))) {
      try {
        db.withSession { implicit s: Session =>
          try {
            create
            block
          } finally {
            drop
          }
        }
      }
    }

  "An Account" should {
    "be creatable" in fakeApp {
      val account = Accounts.insert(Account(None, "[email protected]", "Password"))
      val id = account.id
      id mustNotEqual None 
      Accounts.findById(id.get) mustEqual Some(account)
    }
  }

}

How to determine whether or not the database tables already exist

I cannot give you a sufficient answer to this question...

... but perhaps this is not really s.th you want to do. What if you add an attribute to an table, say Account.active? If you want to safe the data currently stored within your tables, then an alter script would do the job. Currently, such an alter script has to be written by hand. The DAL.ddl.createStatements could be used to retrieve the create statements. They should be sorted to be better comparable with previous versions. Then a diff (with previous version) is used to manually create the alter script. Here, evolutions are used to alter the db schema.

Here's an example on how to generate (the first) evolution:

object EvolutionGenerator extends App {

  import models.DAL

  import play.api.test._
  import play.api.test.Helpers._

    running(FakeApplication(additionalConfiguration = inMemoryDatabase() ++
        Map("slick.default.driver" -> "scala.slick.driver.PostgresDriver",
          "evolutionplugin" -> "disabled"))) {


    val evolution = (
      """|# --- !Ups
         |""" + DAL.ddl.createStatements.mkString("\n", ";\n\n", ";\n") +
      """|
         |# --- !Downs
         |""" + DAL.ddl.dropStatements.mkString("\n", ";\n\n", ";\n")).stripMargin

    println(evolution)

  }

}

Delius

Just another question... I'm trying your code and I'm wondering how to modify the Mapper class so that it could alternatively take an Id provided by the caller. For example, the Id of the Account entity should be a hash code generated from the email address... and any other data related to this account in the database should be referenced by this hash code. Furthermore, how do you handle foreign keys? – Typewriting

j3d, a great explanation of foreign keys are in this recent talk by stefan zeiger: skillsmatter.com/podcast/scala/slick-database-access-with-scala (skip to minute 32:00). – Delius

j3d, regarding the id, it is always a technical pk of type Long, when you use case classes of type Entity and the Mapper trait. If you want to use a functional key (like a hash code of an email), you just need to implement your own Component which is the same like _Component but wraps an object of type Table instead of Mapper. – Delius

I'm sorry, my question was not clear enough. I know how to define a FK... I'm just wondering how to manage one-to-many relationships with a mandatory, auto-generated ID as implemented in _Component... Anyway I don't want to bore any further. I really appreciated your availability and valuable support. Thank you very much. – Typewriting

Ok, sorry :) I haven't tried that yet because Slick is new for me too. In general there are three (not necessarily distinct) cases: 1) your id is auto generated of a specific type - this is what _Component currently does. A foreign key to it is strait forward as mentioned in the video (see above comment) 2) your id is generated elsewhere. here you have to set it manually before inserting the entity into the database. A foreign key to it should also be strait forward as described in 1). 3) you have a 'composite' pk, consisting of several attributes, say name and email...(see next comment) – Delius

(continued)...if you reference an entity which has such a composite pk, I would try to create a foreign key to every attribute of the pk, here: two foreign keys (to name and email). -- because this scales not very well (you have many additional foreign key fields in your tables) I chose to use only generated id's as pk's in slick-integration. If you have a functional unique attribute (such as email-hash) just add a unique constrait to that attribute - and an index if you want to seach for it... But I don't have tried this with slick yet... – Delius

Answer 2

I was also trying to address this problem: the ability to switch databases between test and production. The idea of wrapping each table object in a trait was unappealing.

I am not trying to discuss the pros and cons of the cake pattern here, but I found another solution, for those who are interested.

Basically, make an object like this:

package mypackage
import scala.slick.driver.H2Driver
import scala.slick.driver.ExtendedProfile
import scala.slick.driver.PostgresDriver

object MovableDriver {
  val simple = profile.simple
  lazy val profile: ExtendedProfile = {
    sys.env.get("database") match {
      case Some("postgres") => PostgresDriver
      case _ => H2Driver
    }
  }
}

Obviously, you can do any decision logic you like here. It does not have to be based on system properties.

Now, instead of:

import scala.slick.driver.H2Driver.simple._

You can say

import mypackage.MovableDriver.simple._

UPDATE: A Slick 3.0 Version, courtesy of trent-ahrens:

package mypackage

import com.typesafe.config.ConfigFactory

import scala.slick.driver.{H2Driver, JdbcDriver, MySQLDriver}

object AgnosticDriver {
  val simple = profile.simple
  lazy val profile: JdbcDriver = {
    sys.env.get("DB_ENVIRONMENT") match {
      case Some(e) => ConfigFactory.load().getString(s"$e.slickDriver") match {
        case "scala.slick.driver.H2Driver" => H2Driver
        case "scala.slick.driver.MySQLDriver" => MySQLDriver
      }
      case _ => H2Driver
    }
  }
}

Tuantuareg

this is a easy and simple solution for the problem. I did have to make some adjustments to work with slick 3.0, gist here: gist.github.com/lolboxen/24f90773621497c412f8 – Trencher

Thank you. I've been meaning to get around to doing this. I will paste your code in to complete the answer. – Tuantuareg

Answer 3

The play-slick does exactly the same as what is proposed in the other answers, and it seems to be under the umbrella of Play/Typesafe.

You just can import import play.api.db.slick.Config.driver.simple._ and it will choose the appropriate driver according to conf/application.conf.

It also offers some more things like connection pooling, DDL generation...

Lille

Answer 4

If, like me, you're not using Play! for the project, a solution is provided by Nishruu here

Valentinavalentine