Using specs2 and FakeApplication() to test database fails evolution inserts

Question

This is for Play! Framework 2.0.

I'm trying to write a simple test case to ensure my user model is functioning properly and persisting data in my database. I'd like to run it in memory if possible so I can get a fresh start with every new run.

The issue I have is that my evolutions run(tables are created, data is inserted, but I can't query it as being there). First, my code.

CREATE TABLE user_data (
id SERIAL PRIMARY KEY,
user_name varchar(256) UNIQUE NOT NULL,
email varchar(256) NOT NULL,
password varchar(256) NOT NULL,
edits int NOT NULL,
reports int NOT NULL,
active BOOLEAN NOT NULL);

INSERT INTO user_data(user_name, email, password, edits, reports, active) VALUES ('user1', '[email protected]', '12345678', 0, 0, true);

In application.conf

db.default.driver=org.postgresql.Driver
db.default.url="postgres://user:password@localhost:5432/ME"

In build.scala

val appDependencies = Seq(
  // Add your project dependencies here,
    "postgresql" % "postgresql" % "9.1-901-1.jdbc4"
)

The test code

class User_dataSpec extends Specification {

  "The Database" should {
    "persist data properly" in {
  running(FakeApplication(additionalConfiguration = inMemoryDatabase())) {

      //User_data.findAll().length must beEqualTo(1)
      //Create users
      User_data.create("user1", "password1", "[email protected]") must beEqualTo(1)
      User_data.create("user2", "password2", "[email protected]") must beEqualTo(2)
      User_data.create("user1", "password3", "[email protected]") must beEqualTo(0)

      //Count users
      User_data.findAll().length must beEqualTo(2)

      //Verify users exist
      User_data.exists("user1") must beTrue
      User_data.exists("user2") must beTrue

      //Verify user doesn't exist
      User_data.exists("user3") must beFalse

      //Find users by ID
      User_data.findUser(1).get.user_name must beEqualTo("user1")
      User_data.findUser(2).get.user_name must beEqualTo("user2")

      //Fail to find users by ID
      User_data.findUser(3) must beNone

      //Find users by user_name
      User_data.findUser("user1").get.user_name must beEqualTo("user1")
      User_data.findUser("user2").get.user_name must beEqualTo("user2")

      //Fail to find users by user_name
      User_data.findUser("user3") must beNone

      //Authenticate users
      User_data.authenticate("user1", "password1") must beTrue
      User_data.authenticate("user2", "password2") must beTrue

      //Fail to authenticate users
      User_data.authenticate("user1", "password2") must beFalse
      User_data.authenticate("user3", "passwordX") must beFalse

      //Confirm the user was inserted properly
      val user = User_data.findUser("user1")
      user.get.user_name must beEqualTo("user1")
      user.get.email must beEqualTo("[email protected]")
      user.get.password must beEqualTo("password1")
      user.get.edits must beEqualTo(0)
      user.get.reports must beEqualTo(0)
      user.get.active must beTrue
      }
    }
  }
}

This code will pass as written, however it shouldn't. If I uncomment the first test case inside the running block to test that my findAll() function should be a length of 1 it will fail immediately. However, if I change this to use a persisted PostgreSQL DB on my machine, it will still fail immediately, but when I look at the PostgreSQL DB, my user_data table has the single evolution applied insert in it, and the play_evolutions table has the entry for my evolution and is marked as state = "applied" and last problem = "".

Any help would be appreciated, thanks.

(P.S., I am a first time poster, but will do my best to accept an answer as soon as possible for those willing to lend their help)

Answer 1

* UPDATED *

As Jakob stated, the reason evolutions are failing is probably because the SQL written for MySQL is incompatible with H2DB. You can solve this by using a separate MySQL for testing as per the original answer, or put H2DB into MySQL compatibility mode which may fix the problem (see Fixtures in Play! 2 for Scala).