Integration Testing with H2

It goes without saying that testing code is essential, if you don’t want to have buggy code in production, but how can you test code that queries a database? One solution is an in-memory database, and a common in-memory database is H2. Here is some code that queries a database. More specifically the method getNumDistinctInColumn returns the number of distinct values in a specified column of a specified table.

class DatabaseApp {

  def getNumDistinctInColumn(colName: String, tableName: String, conn: Connection): Int = {
    val query = "SELECT COUNT(DISTINCT " + colName + ") FROM " + tableName
    val result = executeSql(query, conn)
    result.next
    result.getInt(1)
  }

  def executeSql(query: String, conn: Connection): ResultSet = {
    val statement = conn.createStatement
    statement.executeQuery(query)
  }

}

Let's say we have a table with two columns. The first column represents the names of people and the second column represents their favorite place to get food. The table was created with the following query

CREATE TABLE favorite_food (
    name        varchar(255) not null,
    food        varchar(255) not null,
    constraint pk_favorite_food primary key (name)
);

In reality this table has hundreds of millions of entries, but to test our code we want to have just a handful of rows, so that we can count the number of distinct values in a column manually and make sure that our tests return the expected values.

INSERT INTO favorite_food (name, food) values ('Tom', 'Station Cafe');
INSERT INTO favorite_food (name, food) values ('Jouko', 'HEB');
INSERT INTO favorite_food (name, food) values ('Bob', 'HEB');

We need to create the connection to the database. We can do that with the following code

  def conn: Connection = {
    val jdbcUrl = "jdbc:h2:mem:test;MODE=Oracle"
    ConnectionUtils.getConnection(jdbcUrl)
  }

Where the contents of ConnectionUtils is

import com.zaxxer.hikari.{HikariConfig, HikariDataSource}
import javax.sql.DataSource
import java.sql.Connection

object ConnectionUtils {
  def getDataSource(jdbcUrl: String): DataSource = {
    val config: HikariConfig = new HikariConfig()
    config.setJdbcUrl(jdbcUrl)
    config.setUsername("dummy")
    config.setPassword("dummy")
    new HikariDataSource(config)
  }

  def getConnection(jdbcUrl: String, autocommit: Boolean = true): Connection = {
    val datasource: DataSource = getDataSource(jdbcUrl)
    val conn = datasource.getConnection()
    conn.setAutoCommit(autocommit)
    conn
  }

}

Here we have set H2 to emulate Oracle, by setting MODE=Oracle. You can set mode to other values such as MSSQLServer, MySQL, PostgreSQL, Ignite, and others. As we will see later H2 cannot emulate Oracle or other database systems perfectly.

We want to make sure that our tests are independent of each other. If one test inserts into the database we don’t want the next test to see the inserted row. We want to create the database at the beginning of each test and clean it after each test. We can accomplish this by using withFixture from WordSpec.

class TestDatabaseApp extends WordSpec with Matchers {

  def conn: Connection = {
    val jdbcUrl = "jdbc:h2:mem:test;MODE=Oracle"
    ConnectionUtils.getConnection(jdbcUrl)
  }

  override def withFixture(test: NoArgTest): Outcome = {
    createDB
    try test()
    finally cleanDB
  }

  def createDB: Unit = {
    RunScript.execute(conn, new FileReader("src/it/resources/createDB.sql"))
    RunScript.execute(conn, new FileReader("src/it/resources/populateDB.sql"))
  }

  def cleanDB: Unit = {
    RunScript.execute(conn, new FileReader("src/it/resources/cleanDB.sql"))
  }
}

Here createDB.sql contains the queries to create the table, populateDB.sql has the three insert statements from above, and cleanDB.sql has “DROP TABLE favorite_food;”. We can now add a couple of tests.

class TestDatabaseApp extends WordSpec with Matchers {

  override def withFixture(test: NoArgTest): Outcome = {
    createDB
    try test()
    finally cleanDB
  }

  def conn: Connection = {
    val jdbcUrl = "jdbc:h2:mem:test;MODE=Oracle"
    ConnectionUtils.getConnection(jdbcUrl)
  }

  "DatabaseApp#getNumDistinctsInColumn" should {
    "update table and return new number of distincts" in {
      val dbApp = new DatabaseApp
      val colName = "food"
      val tableName = "favorite_food"
      val query = "INSERT INTO favorite_food (name, food) values ('Alice', 'Whataburger')"
      conn.createStatement.executeUpdate(query)
      val result = dbApp.getNumDistinctInColumn(colName, tableName, conn)
      result shouldBe 3
    }
  }

  "DatabaseApp#getNumDistinctsInColumn" should {
    "return number of distincts" in {
      val dbApp = new DatabaseApp
      val colName = "food"
      val tableName = "favorite_food"
      val result = dbApp.getNumDistinctInColumn(colName, tableName, conn)
      result shouldBe 2
    }
  }

  def createDB: Unit = {
    RunScript.execute(conn, new FileReader("src/it/resources/createDB.sql"))
    RunScript.execute(conn, new FileReader("src/it/resources/populateDB.sql"))
  }

  def cleanDB: Unit = {
    RunScript.execute(conn, new FileReader("src/it/resources/cleanDB.sql"))
  }
}

In the first test “update table and return number of distincts” one row is inserted into the table with the values Alice, Whataburger and then we get the number of distinct favorite places to eat. The distinct favorite places to get food are Station Cafe, HEB, and Whataburger. Therefore, the result should be 3. In the second test we get the number of distinct favorite places to get food right away. In this case the distinct favorite places to get food are Station Cafe and HEB. Therefore, the result should be 2. This illustrates how the tests are independent, as the first test does not change the result of the second test.

A word of warning

Setting MODE=Oracle does not mean that all valid Oracle queries will be executed by H2, and H2 may execute queries that are not valid Oracle queries. For example you may have a query of the form SELECT * FROM ((SELECT * FROM T) T2), and this will work fine in Oracle, but H2 will give a syntax error. H2 will execute a query of the form SELECT * FROM (SELECT * FROM T) T2, but in complicated queries you might not be able to reconcile the two, for example if you have many nested SELECT statements.