Use Mysql in dev/prod and H2 in test

Question

Using the play framework 2.1, I'm trying to find the best way to have two different databases configurations:

• One to run my application based on mysql
• One to test my application based on H2

While it is very easy to do one or the other, I run into the following problems when I try to do both:

1. I cannot have the same database evolutions because there are some mysql specific commands that do not function with H2 even in mysql mode: this means two sets of evolutions and two separate database names
2. I'm not certain how to override the main application.conf file by another reserved to test in test mode. What I tried (passing the file name or overriding keys from the command line) seems to be reserved to prod mode.

My question: Can anyone recommend a good way to do both (mysql all the time and only H2 in test) without overly complicating running the application? Google did not help me.

Thanks for your help.

Answer 1

There are a couple of tricks you might find useful.

Firstly, MySQL's /*! */ notation allows you to add code which MySQL will obey, but other DBs will ignore, for example:

create table Users (
  id bigint not null auto_increment,
  name varchar(40)
) /*! engine=InnoDB */

It's not a silver bullet, but it'll let you paper over some of the differences between MySQL and H2's syntax. It's a MySQL-ism, so it won't help with other databases, but since most other databases aren't as quirky as MySQL, you probably wouldn't need it - we migrated our database from MySQL to PostgreSQL, which doesn't support the /*! */ notation, but PostgreSQL is similar enough to H2 that we didn't need it.

If you want to use a different config for dev and prod, you're probably best off having extra config for prod. The reason for this is that you'll probably start your dev server with play run, and start your prod server with play stage; target/start. target/start can take a -Dconfig.resource parameter. For example, create an extra config file prod.conf for prod that looks like:

include "application.conf"

# Extra config for prod - this will override the dev values in application.conf
db.default.driver=...
db.default.url=...
...

and create a start_prod script that looks like:

#!/bin/sh

# Optional - you might want to do this as part of the build/deploy process instead
#play stage
target/start -Dconfig.resource=prod.conf

In theory, you could do it the other way round, and have application.conf contain the prod conf, and create a dev.conf file, but you'll probably want a script to start prod anyway (you'll probably end up needing extra JVM/memory/GC parameters, or to add it to rc.d, or whatever).

Answer 2

Using different database engines is probably worst possible scenario, as you wrote yourself : differences in some functions, reserved keywords etc. causes that you need to write sometimes custom statements very specific for selected DB engine. Better use two separate databases using the same engine.

Unfortunately I don't know issues with config overriding, so if default ways for overriding configs fails... override id in application.conf - so you'll be able to comment whole block fast...)

Answer 3

Here's how to use in memory database for tests:

public class ApplicationTest extends WithApplication {
    @Before
    public void setup() {
        start(fakeApplication(inMemoryDatabase("default-test"), fakeGlobal()));
    }

    /// skipped ....
}

inMemoryDatabase() will use H2 driver by default. You can find more details in source code