Cannot run tests on h2 in-memory database, rather it runs on PostgreSQL

Question

(I have multiple related questions, so I highlight them as bold)

I have a play app.

• play: 2.6.19
• scala: 2.12.6
• h2: 1.4.197
• postgresql: 42.2.5
• play-slick/play-slick-evolutions: 3.0.1
• slick-pg: 0.16.3

I am adding a test for DAO, and I believe it should run on an h2 in-memory database that is created when tests start, cleared when tests end.

However, my test always runs on PostgreSQL database I configure and use.

# application.conf
slick.dbs.default.profile="slick.jdbc.PostgresProfile$"
slick.dbs.default.db.driver="org.postgresql.Driver"
slick.dbs.default.db.url="jdbc:postgresql://localhost:5432/postgres"

Here is my test test/dao/TodoDAOImplSpec.scala.

package dao

import play.api.inject.guice.GuiceApplicationBuilder
import play.api.test.{Injecting, PlaySpecification, WithApplication}

class TodoDAOImplSpec extends PlaySpecification {
  val conf = Map(
    "slick.dbs.test.profile" -> "slick.jdbc.H2Profile$",
    "slick.dbs.test.db.driver" -> "org.h2.Driver",
    "slick.dbs.test.db.url" -> "jdbc:h2:mem:test;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=FALSE"
  )
  val fakeApp = new GuiceApplicationBuilder().configure(conf).build()
  //val fakeApp = new GuiceApplicationBuilder().configure(inMemoryDatabase()).build()
  //val fakeApp = new GuiceApplicationBuilder().configure(inMemoryDatabase("test")).build()
  "TodoDAO" should {
    "returns current state in local pgsql table" in new WithApplication(fakeApp) with Injecting {
      val todoDao = inject[TodoDAOImpl]
      val result = await(todoDao.index())
      result.size should_== 0
    }
  }
}

For fakeApp, I try all three, but none of them work as expected - my test still runs on my local PostgreSQL table (in which there are 3 todo items), so the test fails.

What I have tried/found:

First, inMemoryDatabase() simply returns a Map("db.<name>.driver"->"org.h2.Driver", "db.<name>.url"->""jdbc:h2:mem:play-test-xxx"), which looks very similar to my own conf map. However, there are 2 main differeneces:

• inMemoryDatabase uses db.<name>.xxx while my conf map uses slick.dbs.<name>.db.xxx. Which one should be correct?

Second, rename conf map's keys to "slick.dbs.default.profile", "slick.dbs.default.db.driver" and "slick.dbs.default.db.url" will throw error.

[error] p.a.d.e.DefaultEvolutionsApi - Unknown data type: "status_enum"; SQL statement:
ALTER TABLE todo ADD COLUMN status status_enum NOT NULL [50004-197] [ERROR:50004, SQLSTATE:HY004]

cannot create an instance for class dao.TodoDAOImplSpec

  caused by @79bg46315: Database 'default' is in an inconsistent state!

The finding is interesting - is it related to my use of PostgreSQL ENUM type and slick-pg? (See slick-pg issue with h2). Does it mean this is the right configuration for running h2 in-memory tests? If so, the question becomes How to fake PostgreSQL ENUM in h2.

Third, I follow this thread, run sbt '; set javaOptions += "-Dconfig.file=conf/application-test.conf"; test' with a test configuration file conf/application-test.conf:

include "application.conf"

slick.dbs.default.profile="slick.jdbc.H2Profile$"
slick.dbs.default.db.driver="org.h2.Driver"
slick.dbs.default.db.url="jdbc:h2:mem:test;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=FALSE"

Not surprisingly, I get the same error as the 2nd trial.

It seems to me that the 2nd and 3rd trials point to the right direction (Will work on this). But why must we set name to default? Any other better approach?

Answer 1

In play the default database is default. You could however change that to any other database name to want, but then you need to add the database name as well. For example, I want to have a comment database that has the user table:

CREATE TABLE comment.User(
id                      int(250)         NOT NULL AUTO_INCREMENT,
username                varchar(255), 
comment                 varchar(255), 
PRIMARY KEY             (id)); 

Then I need to have the configuration of it to connect to it (add it to the application.conf file):

db.comment.url="jdbc:mysql://localhost/comment"
db.comment.username=admin-username
db.comment.password="admin-password"

You could have the test database for your testing as mentioned above and use it within your test.

Database Tests Locally: Why not have the database, in local, as you have in production? The data is not there and running the test on local does not touch the production database; why you need an extra database?

Inconsistent State: This is when the MYSQL you wrote, changes the state of the current database within the database, that could be based on creation of a new table or when you want to delete it.

Also status_enum is not recognizable as a MySQL command obviously. Try the commands you want to use in MySQL console if you are not sure about it.