How to write an agnostic database Play an application and perform initial database initialization?

Question

I am using Slick with Play Framework 2.1 and I have some problems.

Given the following object ...

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 need to import a package for a specific database driver, but I want to use H2 for testing and PostgreSQL in production . How do I proceed?

I managed to get around 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, "[e-mail]", "Password")) val account = for (account <- Accounts) yield account account.first.id.get mustEqual 1 } } } } 

I don’t like this solution, and I wonder if there is an elegant way to write DB-agnostic code, so two different database kernels are used: one in testing and the other in production?

I don’t want to use evolution either, and prefer Slick to create 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 run the application, everything works fine ... then, of course, the second time I launch the application, it crashes because the tables already exist in the PostgreSQL database.

However, my last two questions are:

• How to determine if database tables already exist?
• How can I make the onStart method above DB-agnostic so that I can test my application using FakeApplication ?

Answer 1

You can find an example of using a template / dependency template to decouple the Slick driver from the database access level here: https://github.com/slick/slick-examples .

How to separate Slick driver and test application using FakeApplication

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

Using this library, your example will be implemented as follows:

1) Add the dependency to the /Build.scala project

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

Add Snapshot Repository

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

Or a local repository if weak integration is published locally

 resolvers += Resolver.mavenLocal 

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

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

3) Deploy application / models / Account.scala

In the case of slick integration, it is assumed that you are using primary keys of type Long, which automatically increase. The name pk is 'id'. The Table / Mapper implementation has default methods (delete, findAll, findById, insert, update). Your objects must implement the "withId", which is necessary for 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) Deploy application / models / DAL.scala

This is the data access level (DAL) that controllers use to access the database. Transactions are handled by the Table / Mapper implementation in 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, "[e-mail]", "Password")) val id = account.id id mustNotEqual None Accounts.findById(id.get) mustEqual Some(account) } } } 

How to determine if database tables already exist

I can not give you a sufficient answer to this question ...

... but maybe this is not the way you want. What if you add an attribute to the table, say Account.active ? If you want to save the data currently stored in your tables, then a script request will be executed. Currently, such an alter script must be written manually. DAL.ddl.createStatements can be used to retrieve create statements. They should be sorted so that they are better compatible with previous versions. Then diff (with the previous version) is used to manually create an alter script. Here evolutions are used to change the db pattern.

Here is an example of 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) } } 

Answer 2

I also tried to solve this problem: the ability to switch databases between test and production. The idea of ​​wrapping each table object in a tag was unattractive.

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

Basically, create 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 use any decision logic that you like here. It should not be based on system properties.

Now instead of:

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

you can say

 import mypackage.MovableDriver.simple._ 

UPDATE: Slick 3.0 version courtesy of trent-ahrens:

 package com.lolboxen.scala.slick.driver 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 } } } 

Answer 3

play-slick does the same as the other answers, and it seems to be under the Play / typed umbrella.

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

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

Answer 4

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