Menu
Pedro Gómez

Testing Play Framework with H2 in-memory database

Tagged in: Play Framework, Scala

Testing our Play Framework code using a production-ready database is slow. That's why today we are going to review a simple approach we can use to speed up and simplify our integration tests using H2.

H2 is a relational database management system written in Java we can embed in JVM based applications or run in the client-server mode. Thanks to this database (partially) supporting the SQL specification, we'll be able to improve our tests execution just by pointing our application at an H2 instance. As this database is running embedded into the app and it's keeping all the data in memory, all queries will be faster with no dependencies on any other instance being initialized previously.

You can read this blog post as a guide showing how to set up a Play Framework 2.6.13 project and write some tests using:

All the code we will show in the following lines can be found in this GitHub repository.

If for some reason you'd like to use this approach but without Play Framework you can review a similar implementation in this GitHub repository using Finatra instead.

First of all, we need to create a simple Play application. You can quickly do this just executing this command sbt new playframework/play-scala-seed.g8. You can check if the project was created properly by executing sbt test.

Once the application is ready, we need to set up some libraries to improve the way we interact with the database. In this case, we are going to follow these steps:

If you need to test the real connection between the Play application and a real database you can start the Docker instances required to get a MySQL instance up and running quickly by executing cd docker && docker-compose up -d.

We'd strongly recommend you to review our project set up and copy/paste the project configuration. You can find the plugins configuration here and the build.sbt configuration here.

At this point, all the infrastructure needed to start writing a Play application using MySQL as our database is ready. To test if everything works as expected you can execute these commands:

cd docker
docker-compose up -d
cd ..
sbt flywayClean
sbt flywayMigrate
sbt flywayInfo
sbt test
sbt dist

If you want to automatize these checks you will need to set up a continuous integration system. In this example, we will use Travis CI. Add Travis CI support to your project and a new .travis.yml file to your project with the following content:

sudo: required
dist: trusty
language: scala
scala:
  - 2.12.4
jdk:
  - oraclejdk8

services:
  - mysql

before_install:
  - mysql -e "create database IF NOT EXISTS tpf;" -uroot
  - mysql -u root -e "CREATE USER 'tpf'@'localhost' IDENTIFIED BY 'tpf';"
  - mysql -u root -e "GRANT ALL ON tpf.* TO 'tpf'@'localhost';"

script:
  - sbt scalafmt::test
  - sbt test:scalafmt::test
  - sbt test
  - sbt dist
  - sbt flywayClean
  - sbt flywayMigrate
  - sbt flywayInfo

If you review the .travis.yml file, you'll see how the migrations are also tested thanks to the usage of a mysql service and the flyway sbt plugin we configured before.

Now it's time for writing some tests! We are going to write an integration test checking that we can save information related to developers with an ID, username, and email and retrieve this information correctly. We will also apply all the evolutions and will clear the database before every test automatically. To do this, we need a class representing a developer and another class persisting it. We will use Slick and SlickCodegen to facilitate the usage of the database.

case class Developer(id: UUID, username: String, email: Option[String])

class DevelopersDAO @Inject()(protected val dbConfigProvider: DatabaseConfigProvider)(
    implicit ec: ExecutionContext)
    extends HasDatabaseConfigProvider[JdbcProfile] {

  import dbConfig.profile.api._

  def createOrUpdate(developer: Developer): Future[Developer] =
    db.run(DevelopersTable.insertOrUpdate(developer)).map(_ => developer)

  def getById(id: UUID): Future[Option[Developer]] =
    db.run(DevelopersTable.filter(_.id === id.toString).result.headOption.map(asDomain))

}

implicit def asRow(developer: Developer): DevelopersRow =
    DevelopersRow(developer.id.toString, developer.username, developer.email)

implicit def asDomain(row: DevelopersRow): Developer =
    Developer(UUID.fromString(row.id), row.username, row.email)

implicit def asDomain(maybeRow: Option[DevelopersRow]): Option[Developer] = maybeRow.map(asDomain)

Thanks to Slick and SlickCodegen, the amount of code we need to write to persist a Developer instance is just a few lines :)

Once we have our production environment ready, let's write the tests! We will need a base class we will use to point the application at the H2 instance instead of the MySQL instance configured by default:

object InMemoryDatabaseFlatSpec {
  private val inMemoryDatabaseConfiguration: Map[String, Any] = Map(
    "slick.dbs.default.profile"     -> "slick.jdbc.H2Profile$",
    "slick.dbs.default.driver" -> "slick.driver.H2Driver$",
  "slick.dbs.default.db.driver" -> "org.h2.Driver",
  "slick.dbs.default.db.url"      -> "jdbc:h2:mem:play;MODE=MYSQL;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=FALSE",
  "slick.dbs.default.db.user"     -> "",
  "slick.dbs.default.db.password" -> ""
  )

}
abstract class InMemoryDatabaseFlatSpec extends FlatSpec with GuiceOneAppPerSuite {

  import InMemoryDatabaseFlatSpec._

  override def fakeApplication(): Application = {
    val builder = overrideDependencies(
      new GuiceApplicationBuilder()
        .configure(inMemoryDatabaseConfiguration)
    )
    builder.build()
  }

  def overrideDependencies(application: GuiceApplicationBuilder): GuiceApplicationBuilder = {
    application
  }

}

This base class let us get an application instance pointing at H2 instead of the default configured MySQL. When it's initialized, we can start using the application dependency injector to get an instance of any class from our production code.

Once this base class is ready, we are going to create a trait to clear the database once per test:

trait DatabaseCleanerOnEachTest
    extends HasDatabaseConfigProvider[JdbcProfile]
    with BeforeAndAfterEach {
  this: Suite with InMemoryDatabaseFlatSpec =>

  override lazy val dbConfigProvider: DatabaseConfigProvider =
    app.injector.instanceOf[DatabaseConfigProvider]

  override protected def beforeEach(): Unit = {
    super.beforeEach()
    clearDatabase()
  }

  override protected def afterEach(): Unit = {
    clearDatabase()
    super.afterEach()
  }

  def clearDatabase(): Unit = {
    Try(dropTables())
    createTables()
  }

  private def createTables() = {
    Tables.schema.createStatements.toList.foreach { query =>
      db.run(SQLActionBuilder(List(query), SetUnit).asUpdate).awaitForResult
    }
  }

  private def dropTables() = {
    Tables.schema.dropStatements.toList.reverse.foreach { query =>
      db.run(SQLActionBuilder(List(query), SetUnit).asUpdate).awaitForResult
    }
  }

}

Thanks to this trait, our database will be regenerated after and before every test execution, and we'll also be able to recreate the DB schema thanks to the classes generated by Slick Codegen. Once all the infrastructure is ready, it's time to write our tests :)


class DevelopersSpec
    extends InMemoryDatabaseFlatSpec
    with DatabaseCleanerOnEachTest
    with PropertyChecks
    with Matchers {

  private val developersDao = app.injector.instanceOf[DevelopersDAO]

  "Developers" should "be created and retrieved by id" in {
    forAll(arbitraryDeveloper) { developer =>
      val createdDeveloper = developersDao.createOrUpdate(developer).awaitForResult

      val obtainedDeveloper = developersDao.getById(developer.id).awaitForResult

      developer shouldBe createdDeveloper
      obtainedDeveloper shouldBe Some(developer)
    }
  }

  it should "return none if there are no developers persisted associated with the id passed as parameter" in {
    forAll(Gen.uuid) { id =>
      val developer = developersDao.getById(id).awaitForResult

      developer shouldBe None
    }
  }

}

  val arbitraryDeveloper: Gen[Developer] = for {
    id       <- Gen.uuid
    username <- Gen.alphaNumStr.suchThat(_.length < 255)
    email <- Gen.option(
      Gen.oneOf(
        Seq("toni@karumi.com",
            "pedro@karumi.com",
            "fran@karumi.com",
            "davide@karumi.com",
            "jorge@karumi.com",
            "sergio@karumi.com")))
  } yield Developer(id, username, email)

Just for this example, we've decided to use ScalaCheck. This library is a property-based testing framework we've combined with the infrastructure we developed in the previous section to write several developers into the database using random data. If you'd like to know more about property-based testing you can review the following blog posts:

Remember that if during the test execution you need to debug the content of the database you can always use your browser by running sbt h2-browser.

Based on the approach described before, we are now able to write integration tests, writing and reading from our database using a much faster version that is also going to remove all the stored data after the test. Additionally, all the information persisted after a tests execution will be deleted before running the following test, so we don't need to take care of it.

Pedro Gómez

I'm a software engineer focused on new technologies, open source, clean code, and testing. Right now I'm working as a Senior Software Engineer at Karumi, a little software development studio.