Playframework evolutions files compatible with both postgres and h2

I've been developing a web site with the Play framework (scala) using H2 as a backend. Testing is nicely integrated, especially with the ability to run tests against an in-memory H2 db.

Now I'd like to move my datastore over to Postgres for various convenience reasons. This leaves me with a problem: How to continue to test and retain the simplicity of a fresh db for each test run? I see on the net that some people manage to run live against postgres and test against H2. However the two are not entirely compatible at the SQL level (even with H2 in Postgres compatibility mode). For instance SERIAL, BIGSERIAL and 开发者_JS百科BYTEA are not supported on H2.

Can I do this by using a constrained compatible intersection of both dialects, or is there another technique I'm missing?

Thanks for any help.

Alex

---

i know this is an older post, but it looks like there isn't an obvious solution still a few years later. as a short term fix, in play 2.4.x-2.5.x (so far only tested there), you can alter the way evolutions get applied during tests by creating a custom evolutions reader:

package support

import play.api.db.evolutions.{ClassLoaderEvolutionsReader, Evolutions, ResourceEvolutionsReader}

import java.io.{ByteArrayInputStream, InputStream}
import java.nio.charset.StandardCharsets
import scala.io.Source
import scala.util.Try

class EvolutionTransformingReader(
    classLoader: ClassLoader = classOf[ClassLoaderEvolutionsReader].getClassLoader,
    prefix: String = "")
  extends ResourceEvolutionsReader {

  def loadResource(db: String, revision: Int): Option[InputStream] =
    for {
      stream <- Option(classLoader.getResourceAsStream(prefix + Evolutions.resourceName(db, revision)))
      lines <- Try(Source.fromInputStream(stream).getLines).toOption
      updated = lines map convertPostgresLinesToH2
    } yield convertLinesToInputStream(updated)

  private val ColumnRename = """(?i)\s*ALTER TABLE (\w+) RENAME COLUMN (\w+) TO (\w+);""".r

  private def convertPostgresLinesToH2(line: String): String =
    line match {
      case ColumnRename(tableName, oldColumn, newColumn) =>
        s"""ALTER TABLE $tableName ALTER COLUMN $oldColumn RENAME TO $newColumn;"""
      case _ => line
    }

  private def convertLinesToInputStream(lines: Iterator[String]): InputStream =
    new ByteArrayInputStream(lines.mkString("\n").getBytes(StandardCharsets.UTF_8))
}

then pass it into the place where you apply evolutions during your tests:

Evolutions.applyEvolutions(registry.database, new EvolutionTransformingReader())

note that the reader is still in a pretty dumb state (it assumes the SQL statements are oneliners, which is not guaranteed), but this should be enough to get anyone started.

---

As evolution files use SQL directly, unless you limited yourself to a common cross-db-compatible subset of SQL you may have issues.

There is no real solution on that, but you can still use a fresh db for testing. Just set the following:

%test.jpa.ddl=create-drop
%test.db.driver=org.postgresql.Driver
%test.db=<jdbc url>
//etc

This should create a new postgres connection for test, create the db from scratch, run the evolutions, do the tests and remove all data once done.