Reset Your H2 Database For A Clean State Between Functional Tests

If you’re running automated functional testing as part of your build process, you probably know about test isolation (Martin Fowler has a great article on this and other issues with test non-determinism):

  1. A single test can be run by itself, without the rest of the test suite.
  2. A single test failure should not prevent any other tests from running successfully.
  3. Tests can be run in any order.

For test isolation, you need a clean application state between tests. This can be accomplished in several ways, and depends on your problem domain. For example, if your application is multi-tenant, you could have each test create a new tenant. A tenant could be something as simple as a user.

We approached our automated functional testing in a slightly different way: resetting the entire database between tests. But first, a little background:

  1. Developers are writing automated browser tests using Geb.
  2. These Geb tests are run as part of build process against a local server – they are not run against a shared environment. There is a separate QA-managed test suite that is responsible for testing in an integration environment.
  3. The local server is running against an in-memory H2 database and stubbed third-party services, and starts up with a clean set of test data.

As we began writing our Geb tests, we had a cleanup section in each test to reverse changes we had made: delete records, un-update records, etc. However, this quickly ballooned out of control as it was impossible to know in advance where a test would fail, so we had to build a complicated set of logic in the cleanup block of each test (delete record, but only if it exists). The tests were difficult to maintain and that was a problem.

As we were running against an H2 database, it was quite simple to reset the database between tests. (We did not use Spring’s @Transactional on integration tests because we wanted our tests to run against an application server, not just a Spring application context.)

TL;DR – how do I reset the database between tests?

Step 1: Configure H2 to save to a local file

This saves the database to a location in the file system, and utilizes an H2 feature that allows multiple processes to connect to the same database.

Step 2: Backup and reload database before and after tests

We created a Groovy trait with Spock setup and cleanup methods. If your tests are not all in Spock, or you can’t use Groovy 2.3 traits, you could try some other method for code-reuse, such as static methods or test class inheritance.

And that’s it! Your functional tests will all start with an environment in the same state, regardless of how the previous test ended up. I’ve created a Github repo using Spring Boot, if you’re looking for a fleshed-out example.

About the Author

David Norton profile.

David Norton

Director, Platform Engineering

Passionate about continuous delivery, cloud-native architecture, DevOps, and test-driven development.Passionate about continuous delivery, cloud-native architecture, DevOps, and test-driven development.

  • Experienced in cloud infrastructure technologies such as Terraform, Kubernetes, Docker, AWS, and GCP.
  • Background heavy in enterprise JVM technologies such as Groovy, Spring, Spock, Gradle, JPA, Jenkins.
  • Focus on platform transformation, continuous delivery, building agile teams and high-scale applications.

One thought on “Reset Your H2 Database For A Clean State Between Functional Tests

  1. Marcin Erdmann says:

    Another option would be to use a JUnit rule which are supported by Spock out of the box instead of a trait. It would of course be boring to so if you have a reason to use something as new and shiny as Groovy traits but it would be more portable, i.e. could also be used in JUnit tests and would not require Groovy 2.3.

  2. David Norton says:

    Great point Marcin, thanks!


  3. Mike Miller says:

    Thanks for the article. Can you tell us about how long it takes to run your unit tests, espeically when you are re-loading the entire db between tests?

    How many tests and what’s the clock time?

  4. David Norton says:

    Hi Mike,

    The unit tests ran very fast – definitely under 30 seconds, but as those were unit tests, they did not include use a running database.

    The functional tests (using Selenium to control a browser, in this case) against a running application server, took about 3 minutes for a few hundred test cases. The expensive part was running the browser tests themselves, not reloading the database.

    Thanks for reading,

  5. George Platon says:

    Really good article, it worked exactly that way.

  6. IM says:

    Good suggestion. Had this in mind this for a while. Now i have a structure to test it 🙂 Thanks.

  7. Joe Ronin says:

    I’m not using Spring/Spock/Jeb,etc. but the “AUTOSERVER=true/DROP ALL OBJECTS” approach eliminated non-deterministic behavior in my tests (Gradle build, Groovy tests, using H2). It had been passing locally, but failing intermittently on a Jenkins server using an “AUTOSERVER=false”, “delete the database directory between tests” approach.

  8. Rohit says:

    simply write “spring.jpa.hibernate.ddl-auto=create-drop”
    For testing purpose you can use this, it will always reset the table rather than updating.

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Blog Posts
Using Conftest to Validate Configuration Files
Conftest is a utility within the Open Policy Agent ecosystem that helps simplify writing validation tests against configuration files. In a previous blog post, I wrote about using the Open Policy Agent utility directly to […]
SwiftGen with Image & Color Asset Catalogs
You might remember back in 2015 when iOS 9 was introduced, and we were finally given a way to manage all of our assets in one place with Asset Catalogs. A few years later, support […]
Tracking Original URL Through Authentication
If you read my other post about refreshing AWS tokens, then you probably have a use case for keeping track of the original requested resource while the user goes through authentication so you can route […]
Using Spring Beans in a Kafka Streams ExceptionHandler
There are many things to know before diving into Kafka Streams. If you haven’t already, check out these 5 things as a starting point. Bullet 2 mentions designing for exceptions. Ironically, this seems to be […]