Setting up Scala Play testing with H2 in-memory database

Question

Play docs say "The H2 in memory database is very convenient for development because your evolutions are run from scratch when play is restarted." This is all I want. And here is what I have done so far (Play 2.5.6):

I created a testing configuration file to use H2 with evolutions, as follows:

play.evolutions {
  db.default.enabled = true
  autoApply = true
}

db {
  default {
    driver = org.h2.Driver
    url = "jdbc:h2:mem:test;MODE=MYSQL;DATABASE_TO_UPPER=false;DB_CLOSE_DELAY=-1"
    username = sa
    password = ""
    pool = "bonecp"   // otherwise you can't log your sql...
    bonecp.logStatements=true
  }
}

I added a simple evolutions file "/conf/evolutions/default/1.sql":

# --- !Ups

CREATE TABLE PROJECTS (
  id int(11) NOT NULL
) ;

# --- !Downs

DROP TABLE IF EXISTS PROJECTS;

My controller takes a database. This is where the test database should be injected instead of the MySQL one. It has a test action that reads a table:

class DataManagementController @Inject()(db: Database) extends Controller {

      def test() = Action {
        db.withConnection { conn =>
          val st = conn.createStatement()
          val res = st.executeQuery("SELECT * FROM PROJECTS")
          while (res.next()) { println(res.getInt("id")) }
        }
        Ok("")
      }
}

I wrote my first test of that action as follows:

class ControllerSpec extends PlaySpec with OneAppPerSuite {

  val TestDb = Databases.inMemory("default")
  val dataCtrl = new DataManagementController(TestDb)

  "DataManagementController" should {
    "test" in {
      dataCtrl.test().apply(FakeRequest())
    }
  }
}

When I run the test, I see evolutions getting applied to the H2 db, but at the time queries are run, everything got erased (?) despite the DB_CLOSE_DELAY=-1 option:

~ test-only ControllerSpec

[info] ControllerSpec:
[debug] c.j.b.BoneCPDataSource - JDBC URL = jdbc:h2:mem:test;MODE=MYSQL;DATABASE_TO_UPPER=false;DB_CLOSE_DELAY=-1, Username = sa, partitions = 1, max (per partition) = 30, min (per partition) = 5, idle max age = 10 min, idle test period = 1 min, strategy = DEFAULT
[debug] c.j.b.StatementHandle - select id, hash, apply_script, revert_script, state, last_problem from play_evolutions where state like 'applying_%'
[error] o.j.StatementLogger - java.sql.Statement.executeQuery: select id, hash, apply_script, revert_script, state, last_problem from play_evolutions where state like 'applying_%';
throws exception: org.h2.jdbc.JdbcSQLException: Table "play_evolutions" not found; SQL statement:
select id, hash, apply_script, revert_script, state, last_problem from play_evolutions where state like 'applying_%' [42102-192]
[...]
[debug] c.j.b.StatementHandle -
  create table play_evolutions (
[...]
[debug] c.j.b.StatementHandle - CREATE TABLE PROJECTS (
    id int(11) NOT NULL AUTO_INCREMENT,
    person_id int(11) NOT NULL,
    name varchar(255)  NOT NULL,
    PRIMARY KEY (id),
)
[debug] c.j.b.StatementHandle - update play_evolutions set state = 'applied' where id = 1
[debug] c.j.b.StatementHandle - select id, hash, apply_script, revert_script, state, last_problem from play_evolutions where state like 'applying_%'

[info] DataManagementController
[info] application - Creating Pool for datasource 'default'
[info] - should test *** FAILED ***
[info]   org.h2.jdbc.JdbcSQLException: Table "PROJECTS" not found;
[...]

If I explore the database with h2-browser (url: "jdbc:h2:mem:test" + args), it is empty. But if I now modify "1.sql", I get

Database 'default' is in an inconsistent state![An evolution has not been applied properly. Please check the problem and resolve it manually before marking it as resolved.]

but it does not say how to resolve the problem (since I see an empty database in the browser).

I would greatly appreciate any help setting up a test database. I have seen many posts here asking similar questions, with either no answer or none accepted, several different approaches, and I literally have been trying for days. N.B. It would make a useful section in StackOverflow Docs (and Play docs btw).

Moritz Moritz · Accepted Answer · 2016-09-18T16:18:43

I haven't checked against Play 2.5, but I'd expect this to be pretty similar to the latest 2.4 versions.

It's important to understand the lifecyle of your components here. The way your test is set up, there's actually two H2 databases involved.

test is managed by PlaySpec according to the test configuration you are providing. This usually requires to use WithApplication. Here DB_CLOSE_DELAY is properly set.
You are running your tests against an H2 database default you've created yourself. DB_CLOSE_DELAY is not set for this instance, but you could provide a map of urlOptions:

Databases.inMemory("default", urlOptions = Map("DB_CLOSE_DELAY" -> "-1"))

There's a couple of options (without trying to be complete):

Use a plain Specs2 specification rather than the PlaySpec. This way you avoid to have a fake Play app running in the background. But you won't benefit from Play's evolutions.

Apply migrations / insert test fixtures using H2 and the url option INIT=RUNSCRIPT FROM 'classpath:evolutions/default/1.sql'
Point the database in your spec against test rather than default to make sure you are connecting to the same database and use WithApplication to let Play manage it.
Instead of instantiating your controller in the test, you can also let Play's router do it and call the action. Checkout the documentation here.

Setting up Scala Play testing with H2 in-memory database

1 Answers