How to create unit tests against non in-memory database such as MySQL in Play framework, with resetting to known state?

Question

I want to create unit tests that cover code that use relational database in Play framework 2.1.0. There are many possibilities for this and all cause problems:

Testing on in-memory H2 database

Play framework documentation proposes to run unit tests on H2 in-memory database, even if main database used for development and production use other software (i.e. MySQL):

app = Helpers.fakeApplication(Helpers.inMemoryDatabase()); 

My application don't use complicated RDBMS features such as stored procedures and most database access cases are ebean calls, so it should be compatible with both MySQL and H2.

However, table creation statements in evolutions use MySQL-specific features, such as specifying ENGINE = InnoDB, DEFAULT CHARACTER SET = utf8, etc. I fear if I will remove these proprietary parts of CREATE TABLE, MySQL will use some default setting that I can't control and that depend on version, so to test and develop application main MySQL config must be modified.

Anybody used this approach (making evolutions compatible with both MySQL and H2)?

Other ideas how it can be handled:

• Separate evolutions for MySQL and H2 (not a good idea)
• Some way to make H2 ignore additional MySQL stuff in create table (MySQL compatibility mode don't work, it still complain even on default character set). I don't know how.

Testing on the same database driver as main database

The only advantage of H2 in-memory database that it is fast, and testing on the same database driver than dev/production database may be better, because it is closer to real environment.

How it can be done right in Play framework?

Tried:

Map<String, String> settings = new HashMap<String, String>(); settings.put("db.default.url", "jdbc:mysql://localhost/sometestdatabase"); settings.put("db.default.jndiName", "DefaultDS"); app = Helpers.fakeApplication(settings); 

Looks like evolutions work here, but how it's best to clean database before each test? By creating custom code that truncates each table? If it will drop tables, then will evolutions run again before next test, or they are applied once per play test command? Or once per Helpers.fakeApplication() invocation?

What are best practices here? Heard about dbunit, is it possible to integrate it without much pain and quirks?

Answer 1

First, I would recommend you to use the same RDBMS for testing and production as it could avoid some hard-to-find bugs.

Concerning the need to clean your database between each test, you can use Ebean DdlGenerator to generate scripts to create a clean database and JUnit's @Before annotation to automatically execute these scripts before every test.

Using the DdlGenerator can be done like this :

    EbeanServer server = Ebean.getServer(serverName);     ServerConfig config = new ServerConfig();     DdlGenerator ddl = new DdlGenerator((SpiEbeanServer) server, new MySqlPlatform(), config); 

This code can be placed in a base-class that you could make inherit your tests (or inside a custom Runner that you can use with the @RunWith annotation).

It will also allow you to easily automate the FakeApplication creation, avoiding some boilerplate code.

Some links that can be helpful :

• http://blog.matthieuguillermin.fr/2012/03/unit-testing-tricks-for-play-2-0-and-ebean/
• https://gist.github.com/nboire/2819920