Cleaning up database tables after each integration test method with Spring Boot and Kotlin

In this article, I'll explain how to clear database tables after each integration test in a Spring Boot application using Kotlin.

I usually prefer to write integration tests that perform end-to-end processing (e.g., flow from a client making a REST POST/PUT or GraphQL mutation up to the database access layer and back out). I’m not a big fan of mocking parts of the system for integration tests.

Of course “integration tests” mean different things to different people but I won’t get into that.

One issue that I’ve faced when writing my integration tests with Spring Boot 2 (that I’m still discovering) is that it wasn’t very straightforward to clean up the in-memory database after each test method/class.

It was pretty easy when I wrote tests using @DataJpaTest, since I only needed to add @Transactional, but @DataJpaTest only loads specific parts of the application in order to test the data access layer.

What I wanted and what I’m using now is @SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT), which does what I want: starts the closest thing to the real application. Actually this feels like what I previously obtained (a lot more painfully) using Arquillian.

With @SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT), you must forget about @Transactional in test classes. This is clearly stated in Spring Boot’s documentation:

If your test is @Transactional, it will rollback the transaction at the end of each test method by default. However, as using this arrangement with either RANDOM_PORT or DEFINED_PORT implicitly provides a real servlet environment, HTTP client and server will run in separate threads, thus separate transactions. Any transaction initiated on the server won’t rollback in this case.

Thus, indeed, there’s no easy way to roll back the changes...

The simplest thing that could work would be to perform the cleanup manually at the end of each test method/class, but that is really cumbersome and error-prone. What I want instead is something easy that does the cleanup for me.

Another option could be to add DbUnit and Spring DbUnit to my project and use @DatabaseSetup (cfr example project) which uses DatabaseOperation.CLEAN_INSERT.

But for now I don’t want to add DbUnit to my project.

Another option that I’ll look at later on is this one. I like the approach but I’m using JUnit 5 and I don’t have time to go about and learn what JUnit rules have become. Although, since that’s also interesting, I’ll come back to it in a future article =)

So here’s what I’ve ended up doing this evening. It’s the very first iteration, so indeed there are tons of things to improve there! ;-)

First I’ve defined a test profile that I activate in my tests using:

@ActiveProfiles("test")

Then I took (a lot of) inspiration from the following article and adapted the code to avoid depending on Hibernate:

As you can see it’s a simple Spring service that uses the JPA metamodel to go about and find the table names through reflection (by getting the name from the @Table annotation on the entities).

With that in place, I’ve created a base class for my integration tests where I inject and use the DatabaseCleanupService after each test method:

Using the above is pretty simple and foolproof: inherit from that class and the database will be cleaned after each test method has completed.

Of course this is far from perfect and will probably break for more complex models/scenarios, but it fits my current (i.e., basic) needs.

What I plan to improve now is to avoid the "is-a" relationship and providing more flexibility regarding when/what to clean up. This should be pretty straightforward to implement now :)

That's it for today! ✨

About Sébastien

Hello everyone! I'm Sébastien Dubois. I'm an author, founder, and CTO. I write books and articles about software development & IT, personal knowledge management, personal organization, and productivity. I also craft lovely digital products 🚀

If you've enjoyed this article and want to read more like this, then subscribe to my newsletter, check out my PKM Library and my collection of books about software development 🔥.

You can follow me on Twitter 🐦

If you want to discuss, then don't hesitate to join one of my communities: the Software Crafters community, the Personal Knowledge Management community, and the focusd Productivity community