Integration Tests using a Database and Spring Boot

Author unknown

Integration tests are crucial part in testing most applications. In my experience, some projects even go against the famous pyramid diagram and have more of these, than unit tests. For instance – web systems, where some framework, like Spring Boot, frees us from the burden of implementing input validation, role-based access control, etc. and consequently, good amount of unit testing is done inside the library itself.

When I work on a REST API, I often find that most of acceptance tests, checking various use cases and scenarios, are of integration type. In his article – A Guide to Robust Unit and Integration Tests with JUnit – Joshua Hayden describes how JUnit can be used for writing and running tests. I would like to further it and add more details about testing multiple components of the system.

I like a quote from article by James O Coplien – “Create system tests with good feature coverage (not code coverage) — remembering that proper response to bad inputs or other unanticipated conditions is part of your feature set.”

Let’s assume our project is A REST API for online education system, where user can register, find a course, enroll and submit homework. It would be difficult to test all features of our app without integration tests, as in most cases it would require creating and modifying multiple resources, hence, database access. For instance, to test enrollment, we would need to have a registered and activated user and existing course. In my opinion, mocking a persistence layer for this would be only a waste of time. So, let’s dive in:

To run tests against a database, we have to overcome several challenges:

  • We need a separate DB and corresponding configuration
  • A database should be easy to build from scratch without manual intervention
  • We need isolation for each test or test suite, so they won’t pollute data for other tests.

For the sake of example, I will try to solve them using Spring Boot, however the same idea can be used with other frameworks and technologies.


In-Memory Database vs Production

I’ve often received advice to use in-memory databases for my integration tests, but I’m still very skeptical about it. Even though all popular databases follow some standards, they have lots of vendor-specific features. This is what makes writing a fully portable app difficult. For instance, not only in-memory H2 database might act differently than MySQL, but two versions of MySQL might give different results in some cases. It’s error-prone to consider every detail or a deprecated feature, so, for my tests I always prefer to select exact vendor and version, which is used in production environment. (Container technologies, like Docker, make our lives easy in this decision).

Let’s move on with a real database here and decide on configuring it: If we start up a docker container on every `test` command and destroy it afterwards, we can get away with one DB, but if we also need a persistent database for manual testing, which must not be cleared on every test run, then we’ll need an additional temporary DB. In Spring Boot, we can either create a test/resources/ file with connection url of such database or use a test profile. This might vary among frameworks, but a key thing is to solve the task without manual intervention or commenting out stuff for running tests.


Rebuilding a Database

To build a database from scratch, one can have a SQL script of structure exported from a DB. But I prefer to go with a nice method, called Database Migrations. This enables us to have a versioning of a database and application code in sync. It also makes rebuilding a DB very easy.
You can also learn about database migrations from this article: Database Migrations: Turning Caterpillars into Butterflies


Isolated Tests

Test isolation is good for several reasons: they can be run in parallel and in case database is updated, they can be safe from breaking each others’ data. On the internet you might find a suggestion to use transactions for such tests, which won’t be committed and will be rolled back after test completion. In Spring boot this can be achieved by adding @Transactional annotation on a test method.

I don’t like this solution, because if we actually use transactions inside our business logic, we’ll end up with nested transactions, which might have unexpected results in corner cases. Also I find it very helpful that by running some tests I can quickly populate database with various scenarios and continue manual testing or debugging using persisted data.

Generally, I prefer to do it this way: Dropping and rebuilding the database before running each test is an expensive operation. It might be better to rebuild it with migrations only once before starting testing, so migration scripts will also be tested. Then, I use plain sql to truncate tables before a test.

One of the ways for achieving this:

Add a configuration class, which will run once before all tests and reset migrations there (Flyway is a plugin for database migrations.)

public class TestConfig {
	public FlywayMigrationStrategy clean() {
		return flyway -> {

Add sql script of truncate as an annotation in a base class for integration tests. This will be run before each test method.

@Sql(executionPhase = ExecutionPhase.BEFORE_TEST_METHOD, 
	scripts = "classpath:ge/demo/api/sql/beforeTest.sql")

Sometimes, it might be reasonable to group several tests in a test suite and reset database only before a suit and not a single test.



Leave a Reply

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