How to Tear Down or Empty HSQLDB Database After Test

1. Overview

When writing integration tests, maintaining a clean database state between tests is crucial for ensuring reproducible results and avoiding unintended side effects. HSQLDB (HyperSQL Database) is a lightweight, in-memory database that is often used for testing purposes due to its speed and simplicity.

In this article, we’ll explore various methods to tear down or empty a HSQLDB database in Spring Boot after tests have run. While our focus is on HSQLDB, the concepts broadly apply to other database types, too.

For test isolation, we’ve used the @DirtiesContext annotation because we’re simulating different approaches, and we want to ensure that each approach is tested in isolation to avoid any side effects between them.

2. Methods

We’ll explore five approaches we can use to tear down a HSQLDB database, namely:

• Transaction management with Spring’s @Transactional annotation
• Application properties configuration
• Executing queries using JdbcTemplate
• Using JdbcTestUtils for cleanup
• Custom SQL scripts

2.1. Transaction Management With the @Transactional Annotation

When tests are annotated with @Transactional, Spring’s transaction management is activated. This means each test method runs within its own transaction and automatically rolls back any changes made during the test:

@SpringBootTest
@ActiveProfiles("hsql")
@Transactional
@DirtiesContext
public class TransactionalIntegrationTest {

    @Autowired
    private CustomerRepository customerRepository;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @BeforeEach
    void setUp() {
        String insertSql = "INSERT INTO customers (name, email) VALUES (?, ?)";
        Customer customer = new Customer("John", "[email protected]");
        jdbcTemplate.update(insertSql, customer.getName(), customer.getEmail());
    }

    @Test
    void givenCustomer_whenSaved_thenReturnSameCustomer() throws Exception {
        Customer customer = new Customer("Doe", "[email protected]");
        Customer savedCustomer = customerRepository.save(customer);
        Assertions.assertEquals(customer, savedCustomer);
    }
   // ... integration tests
}

This method is fast and efficient because it doesn’t require manual cleanup.

2.2. Application Properties Configuration

In this method, we have a simple setup where we assign a preferred option to the property spring.jpa.hibernate.ddl-auto. There are different values available, but create and create-drop are often the most useful for cleanup.

The create option drops and recreates the schema at the start of the session, while create-drop creates the schema at the start and drops it at the end.

In our code example, we added the @DirtiesContext annotation to each test method to ensure that the schema creation and dropping process is triggered for every test. However, with great power comes great responsibility; we must use these options wisely, or we might accidentally clear our production database:

@SpringBootTest
@ActiveProfiles("hsql")
@TestPropertySource(properties = { "spring.jpa.hibernate.ddl-auto=create-drop" })
public class PropertiesIntegrationTest {

    @Autowired
    private CustomerRepository customerRepository;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @BeforeEach
    void setUp() {
        String insertSql = "INSERT INTO customers (name, email) VALUES (?, ?)";
        Customer customer = new Customer("John", "[email protected]");
        jdbcTemplate.update(insertSql, customer.getName(), customer.getEmail());
    }

    @Test
    @DirtiesContext
    void givenCustomer_whenSaved_thenReturnSameCustomer() throws Exception {
        Customer customer = new Customer("Doe", "[email protected]");
        Customer savedCustomer = customerRepository.save(customer);
        Assertions.assertEquals(customer, savedCustomer);
    }

    // ... integration tests
}

This method has the advantage of being simple to configure with no need for custom code in test classes, and it’s especially useful for tests that require persistent data. However, it offers less fine-grained control compared to other methods involving custom code.

We can set this property either in the test class or assign it to the entire profile, but we must be cautious, as it will affect all tests within the same profile, which might not always be desirable.

2.3. Executing Queries Using JdbcTemplate

In this method, we use the JdbcTemplate API, which allows us to execute various queries. Specifically, we can use it to truncate tables after each test or after all tests at once. This method is simple and straightforward, as we only need to write the query, and it preserves the table structure:

@SpringBootTest
@ActiveProfiles("hsql")
@DirtiesContext
class JdbcTemplateIntegrationTest {

    @Autowired
    private CustomerRepository customerRepository;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @AfterEach
    void tearDown() {
        this.jdbcTemplate.execute("TRUNCATE TABLE customers");
    }
    
    @Test
    void givenCustomer_whenSaved_thenReturnSameCustomer() throws Exception {
        Customer customer = new Customer("Doe", "[email protected]");
        Customer savedCustomer = customerRepository.save(customer);
        Assertions.assertEquals(customer, savedCustomer);
    }
    // ... integration tests
}

However, this method requires manually listing or writing queries for all tables, and it may be slower when dealing with a large number of tables.

2.4. Using JdbcTestUtils for Cleanup

In this method, the JdbcTestUtils class from the Spring Boot framework provides more control over the database cleanup process. It allows us to execute arbitrary SQL statements to delete data from specific tables.

While it’s similar to JdbcTemplate, there are key differences, particularly in terms of syntax simplicity, as one line of code can handle multiple tables. It’s also flexible, but limited to deleting all rows from tables:

@SpringBootTest
@ActiveProfiles("hsql")
@DirtiesContext
public class JdbcTestUtilsIntegrationTest {

    @Autowired
    private CustomerRepository customerRepository;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @AfterEach
    void tearDown() {
        JdbcTestUtils.deleteFromTables(jdbcTemplate, "customers");
    }
    
    @Test
    void givenCustomer_whenSaved_thenReturnSameCustomer() throws Exception {
        Customer customer = new Customer("Doe", "[email protected]");
        Customer savedCustomer = customerRepository.save(customer);
        Assertions.assertEquals(customer, savedCustomer);
    }

    // ... integration tests
}

However, this method has its drawbacks. The delete operation is slower for large databases compared to truncate, and it doesn’t handle referential integrity constraints, which could lead to issues in databases with foreign key relationships.

2.5. Custom SQL Scripts

This method uses SQL scripts to clean up the database, which can be especially useful for more complex setups. It’s both flexible and customizable, as it can handle intricate cleanup scenarios while keeping database logic separate from test code.

In Spring Boot 3, the SQL script can be placed at the class level to execute after each method or after all test methods. For example, let’s look at one way to configure it:

@SpringBootTest
@ActiveProfiles("hsql")
@DirtiesContext
@Sql(scripts = "/cleanup.sql", executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD)
public class SqlScriptIntegrationTest {

    @Autowired
    private CustomerRepository customerRepository;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @BeforeEach
    void setUp() {
        String insertSql = "INSERT INTO customers (name, email) VALUES (?, ?)";
        Customer customer = new Customer("John", "[email protected]");
        jdbcTemplate.update(insertSql, customer.getName(), customer.getEmail());
    }

    @Test
    void givenCustomer_whenSaved_thenReturnSameCustomer() throws Exception {
        Customer customer = new Customer("Doe", "[email protected]");
        Customer savedCustomer = customerRepository.save(customer);
        Assertions.assertEquals(customer, savedCustomer);
    }

It can also be applied to individual test methods, as shown in the example below, where we place it on the last method to clean up after all tests:

@Test
@Sql(scripts = "/cleanup.sql", executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD)
void givenExistingCustomer_whenFoundByName_thenReturnCustomer() throws Exception {
    // ... integration test
}

However, this method does require maintaining separate SQL files, which may be overkill for simpler use cases.

3. Conclusion

Tearing down the database after each test is a fundamental concept in software testing. By following the methods outlined in this article, we can ensure that our tests remain isolated and produce reliable results each time they are run.

While each method we discussed here has its own advantages and drawbacks, it’s important to choose the approach that best fits our project’s specific requirements.