Play framework and Slick automatic database creati

Question

I'm using play 2.4 and Slick 3, Is it possible to generate automatically ddl scripts, it is evolutions?

In official docs I found some scripts, but where should I place it in play framework? http://slick.typesafe.com/doc/3.1.0/schemas.html

Do you know any libs to manage evolutions in code to not write plain SQL?

Answer 1

With Play 2.4 and newer, the slick plugin doesn't create evolutions any longer. For this I have added a page into development mode, that always shows the newest version of all evolutions in the browser. This approach is compatible with all coming changes in the module system, since it doesn't use any modules. An using dependency injection, it uses exactly that Slick database driver you have configured in your application.conf file

I have put the following line in config/routes:

GET /evolutions.sql                      controllers.SchemaEvolutionsController.evolutions

Then I created a controller (app/controllers/SchemaEvolutionsController.scala)

package controllers

import com.google.inject.Inject
import dao.CatDao
import models.HasSchemaDescription
import models.HasSchemaDescription.SqlSchemaDescription
import play.api.Environment
import play.api.mvc.{Action, Controller}
import play.api.Mode

class SchemaEvolutionsController @Inject() (environment: Environment, catDao : CatDao) extends Controller {

  def allSchemas : Seq[HasSchemaDescription] = List(catDao) // List all schemas here

  def descriptionsForAllSchemas : Seq[SqlSchemaDescription] = allSchemas.map(_.schemaDescription)

  def evolutions = Action {
    environment.mode match {
      case Mode.Prod => NotFound
      case _ => Ok(views.txt.evolutions(descriptionsForAllSchemas)).withHeaders(CONTENT_TYPE -> "text/plain")
    }
  }
}

For this controller there is of course a corresponding view (views/evolutions.scala.txt)

@import models.HasSchemaDescription.SqlSchemaDescription
@(schemaDescriptions : Seq[SqlSchemaDescription])

# Get the newest version of this evolutions script on the address
# http://localhost:9000@(controllers.routes.SchemaEvolutionsController.evolutions)
# when the server runs in development mode


# --- !Ups

@for(
    schemaDescription <- schemaDescriptions;
    statement <- schemaDescription.createStatements) {
@(statement.replaceAll(";",";;"));
}

# --- !Downs

@for(
    schemaDescription <- schemaDescriptions;
    statement <- schemaDescription.dropStatements) {
@(statement.replaceAll(";",";;"));
}

For the DAO objects I added a common trait to get the schema description (app/models/HasSchemaDescription):

package models

import models.HasSchemaDescription.SqlSchemaDescription

trait HasSchemaDescription {
  def schemaDescription: SqlSchemaDescription
}

object HasSchemaDescription {
  type SqlSchemaDescription = slick.profile.SqlProfile#SchemaDescription
}

Now for each DAO object, I must implement the trait and add the DAO to the SchemaEvolutionsController.

For example the DAO for serving cat objects:

class CatDao @Inject()(protected val dbConfigProvider: DatabaseConfigProvider)
  extends HasDatabaseConfigProvider[JdbcProfile] with HasSchemaDescription {
  import driver.api._

  private val Cats = TableQuery[CatsTable]

  def schemaDescription : SqlSchemaDescription = Cats.schema

  def findById(id : Int) : Future[Option[Cat]] =  db.run(Cats.filter(_.id === id).result.headOption)

  private class CatsTable(tag: Tag) extends Table[Cat](tag, "CAT") {

    def id = column[Int]("ID", O.PrimaryKey, O.AutoInc)
    def name = column[String]("NAME")
    def color = column[String]("COLOR")
    def age = column[Option[Int]]("AGE")

    def * = (id, name, color, age) <> (Cat.tupled, Cat.unapply _)
  }
}

With this example, you get the following result on http://localhost:9000/evolutions.sql

# Get the newest version of this evolutions script on the address
# http://localhost:9000/evolutions.sql
# when the server runs in development mode


# --- !Ups


create table `CAT` (`ID` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,`NAME` TEXT NOT NULL,`COLOR` TEXT NOT NULL,`AGE` INTEGER);


# --- !Downs


drop table `CAT`;

Answer 2

I made some workaround with PostgresDriver, I've created module, that prints DDL to file. After every code change I just need to replace 1.sql or later modify next evolution scripts:

ComputersDatabaseModule.scala

package bootstrap

import com.google.inject.AbstractModule
import play.api.{Mode, Play}

class ComputersDatabaseModule extends AbstractModule {

  protected def configure() = {
    bind(classOf[CreateDDL]).asEagerSingleton()
    bind(classOf[InitialData]).asEagerSingleton()
  }
}

CreateDDL.scala

package bootstrap

import java.io.PrintWriter
import javax.inject.Inject

import dao.{CompaniesMapping, ComputersMapping}
import play.api.db.slick.{HasDatabaseConfigProvider, DatabaseConfigProvider}
import slick.driver.JdbcProfile


/**
  * Creates DDL script
  */
private[bootstrap] class CreateDDL @Inject()(protected val dbConfigProvider: DatabaseConfigProvider) extends HasDatabaseConfigProvider[JdbcProfile] with
  ComputersMapping with CompaniesMapping {

  def createDDLScript() = {
    import slick.driver.PostgresDriver.api._

    val allSchemas = companies.schema ++ computers.schema

    val writer = new PrintWriter("target/migration_ddl.sql")
    writer.write("# --- !Ups\n\n")
    allSchemas.createStatements.foreach { s => writer.write(s + ";\n") }

    writer.write("\n\n# --- !Downs\n\n")
    allSchemas.dropStatements.foreach { s => writer.write(s + ";\n") }

    writer.close()
  }

  createDDLScript()
}

ComputersDAO.scala

package dao

import java.util.Date
import javax.inject.{Inject, Singleton}
import models.{Company, Computer, Page}
import play.api.db.slick.{DatabaseConfigProvider, HasDatabaseConfigProvider}
import play.api.libs.concurrent.Execution.Implicits.defaultContext
import slick.driver.JdbcProfile

import scala.concurrent.Future

trait ComputersMapping { self: HasDatabaseConfigProvider[JdbcProfile] =>
  import driver.api._

  class Computers(tag: Tag) extends Table[Computer](tag, "COMPUTER") {

    implicit val dateColumnType = MappedColumnType.base[Date, Long](d => d.getTime, d => new Date(d))

    def id = column[Long]("ID", O.PrimaryKey, O.AutoInc)
    def name = column[String]("NAME")
    def introduced = column[Option[Date]]("INTRODUCED")
    def discontinued = column[Option[Date]]("DISCONTINUED")
    def companyId = column[Option[Long]]("COMPANY_ID")

    def * = (id.?, name, introduced, discontinued, companyId) <> (Computer.tupled, Computer.unapply)
  }

  val computers = TableQuery[Computers]
}

@Singleton()
class ComputersDAO @Inject() (protected val dbConfigProvider: DatabaseConfigProvider) extends CompaniesMapping with ComputersMapping
  with HasDatabaseConfigProvider[JdbcProfile] {
  import driver.api._

  /** Retrieve a computer from the id. */
  def findById(id: Long): Future[Option[Computer]] =
    db.run(computers.filter(_.id === id).result.headOption)

  /** Count all computers. */
  def count(): Future[Int] = {
    // this should be changed to
    // db.run(computers.length.result)
    // when https://github.com/slick/slick/issues/1237 is fixed
    db.run(computers.map(_.id).length.result)
  }
  /** Count computers with a filter. */
  def count(filter: String): Future[Int] = {
    db.run(computers.filter { computer => computer.name.toLowerCase like filter.toLowerCase }.length.result)
  }

  /** Return a page of (Computer,Company) */
  def list(page: Int = 0, pageSize: Int = 10, orderBy: Int = 1, filter: String = "%"): Future[Page[(Computer, Company)]] = {

    val offset = pageSize * page
    val query =
      (for {
        (computer, company) <- computers joinLeft companies on (_.companyId === _.id)
        if computer.name.toLowerCase like filter.toLowerCase
      } yield (computer, company.map(_.id), company.map(_.name)))
        .drop(offset)
        .take(pageSize)

    for {
      totalRows <- count(filter)
      list = query.result.map { rows => rows.collect { case (computer, id, Some(name)) => (computer, Company(id, name)) } }
      result <- db.run(list)
    } yield Page(result, page, offset, totalRows)
  }

  /** Insert a new computer. */
  def insert(computer: Computer): Future[Unit] =
    db.run(computers += computer).map(_ => ())

  /** Insert new computers. */
  def insert(computers: Seq[Computer]): Future[Unit] =
    db.run(this.computers ++= computers).map(_ => ())

  /** Update a computer. */
  def update(id: Long, computer: Computer): Future[Unit] = {
    val computerToUpdate: Computer = computer.copy(Some(id))
    db.run(computers.filter(_.id === id).update(computerToUpdate)).map(_ => ())
  }

  /** Delete a computer. */
  def delete(id: Long): Future[Unit] =
    db.run(computers.filter(_.id === id).delete).map(_ => ())

}

Add in configuration (application.config):

play.modules.enabled += "bootstrap.ComputersDatabaseModule"