How can I unit test for MySQL database with Play 2.x?

Question

I need to test if my code creates the correct database statements. I know I can run my application with an in memory database. It's not perfect, but enough for this project. My SQL contains MySQL specific stuff.

When I run my test it breaks on the evolution that contains the following:

CREATE TABLE `Beaches` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE=InnoDB;
COLLATE='utf8_general_ci'

How to solve the problems with the MySQL specific syntax?

"Is there a jdbc mock library" did you mean mock for model classes so it will be possible to use models without real database? Then general-purpose moch libraries such as mockito can be used. — kolen, Mar 01 '13 at 04:55
No, I mean one that will mock the jdbc connection. So that I can ask which SQL statements were executed and possibly influence the result. — EECOLOR, Mar 01 '13 at 07:13

score 10 · Accepted Answer · answered Feb 28 '13 at 22:13

I found a way to get around (most) of the MySQL specific stuff (question 1.)

running(FakeApplication(additionalConfiguration = 
  inMemoryDatabase(options=Map("MODE" -> "MySQL")))) {

  DB.withConnection { implicit c =>
    SQL("""
        CREATE TABLE `Beaches` (
          `id` INT(10) NOT NULL AUTO_INCREMENT,
          `name` VARCHAR(255) NOT NULL,
          PRIMARY KEY (`id`)
        )
        /*! ENGINE=InnoDB; */
        /*! COLLATE='utf8_general_ci' */
        """
    ).execute()
    ok
  }
}

Note that H2 has a MODE=MySQL as described in the Working with the in-memory H2 database section of the Play documentation.

The other part are the c-style comments as described in the Comment Syntax section of the MySQL documentation.

How can I unit test for MySQL database with Play 2.x?

1 Answers1