How to clean database tables after each integration test when using Spring Boot and Liquibase?

Question

I have a side project were I'm using Spring Boot, Liquibase and Postgres.

I have the following sequence of tests:

test1();
test2();
test3();
test4();

In those four tests I'm creating the same entity. As I'm not removing the records from the table after each test case, I'm getting the following exception: org.springframework.dao.DataIntegrityViolationException

I want to solve this problem with the following constraints:

1. I don't want to use the @repository to clean the database.
2. I don't want to kill the database and create it on each test case because I'm using TestContainers and doing that would increase the time it takes to complete the tests.

In short: How can I remove the records from one or more tables after each test case without 1) using the @repository of each entity and 2) killing and starting the database container on each test case?

Answer 1

The simplest way I found to do this was the following:

1. Inject a JdbcTemplate instance

@Autowired
private JdbcTemplate jdbcTemplate;

2. Use the class JdbcTestUtils to delete the records from the tables you need to.

JdbcTestUtils.deleteFromTables(jdbcTemplate, "table1", "table2", "table3");

3. Call this line in the method annotated with @After or @AfterEach in your test class:

@AfterEach
void tearDown() throws DatabaseException {
    JdbcTestUtils.deleteFromTables(jdbcTemplate, "table1", "table2", "table3");
}

I found this approach in this blog post: Easy Integration Testing With Testcontainers

Answer 2

Annotate your test class with @DataJpaTest. From the documentation:

By default, tests annotated with @DataJpaTest are transactional and roll back at the end of each test. They also use an embedded in-memory database (replacing any explicit or usually auto-configured DataSource).

For example using Junit4:

@RunWith(SpringRunner.class)
@DataJpaTest
public class MyTest { 
//...
}

Using Junit5:

@DataJpaTest
public class MyTest { 
//...
}

Answer 3

You could use @Transactional on your test methods. That way, each test method will run inside its own transaction bracket and will be rolled back before the next test method will run.

Of course, this only works if you are not doing anything weird with manual transaction management, and it is reliant on some Spring Boot autoconfiguration magic, so it may not be possible in every use case, but it is generally a highly performant and very simple approach to isolating test cases.

Answer 4

i think this is the most effecient way for postgreSQL. You can make same thing for other db. Just find how to restart tables sequence and execute it

@Autowired
private JdbcTemplate jdbcTemplate;

@AfterEach
public void execute() {
    jdbcTemplate.execute("TRUNCATE TABLE users" );
    jdbcTemplate.execute("ALTER SEQUENCE users_id_seq RESTART");
}

Answer 5

My personal preference would be:

private static final String CLEAN_TABLES_SQL[] = {
    "delete from table1",
    "delete from table2",
    "delete from table3"
};

@After
public void tearDown() {
    for (String query : CLEAN_TABLES_SQL)
    {
        getJdbcTemplate().execute(query);
    }
}

To be able to adopt this approach, you would need to extend the class with DaoSupport, and set the DataSource in the constructor.

public class Test extends NamedParameterJdbcDaoSupport

public Test(DataSource dataSource)
{
    setDataSource(dataSource);
}

Answer 6

Another alternative not posted here is, if your tests extends AbstractTransactionalTestNGSpringContextTests from spring, you can directly use the method deleteFromTables already included on this parent class:

@AfterClass
public void deleteTournament() {
     deleteFromTables("table1", "table2");
     deleteFromTables("table3");
}

Note that here, you put the real SQL table name and not the Java Entity name. And you must respect dependencies between tables (as foreign keys). That means that you must remove the tables in order: On the example, table3 is deleted after table1, table2that makes sense if table3 has a Foreign Key that points to one to the two other tables.