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

Question

I have a side project where 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?

Gens

I found this on the networks, it is working for me @SQL Annotation you could have a file with all statements needed for cleanup and @Sql annotation on the required tests – Tullusus

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

Gens

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 { 
//...
}

Stralka

Probably better than my answer. – Durstin

@user7655213, I disagree and think your approach should be the first thing to do, declare the tests as @Transactional before anything else. If that doesn't work, then try other alternatives. @DataJpaTest is specifically for tests focusing in JPA functionality only, as specified by the docs: docs.spring.io/spring-boot/docs/current/api/org/springframework/… – Maun

@Ricci feel free to post your answer, it might be helpful for others. – Stralka

This article from reflectoring.io gives some great extra info about the @DataJpaTest annotation (alongside more information regarding "Testing JPA Queries with Spring Boot and @DataJpaTest" – Suppurate

Don't use Transactional in tests – Guanabana

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.

Durstin

Actually, this is not recommended in several blogs like this dev.to/henrykeys/don-t-use-transactional-in-tests-40eb – Nares

Answer 4

I think this is the most efficient way for PostgreSQL. You can do the same thing for others. 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");
}

Gerontology

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);
}

Nijinsky

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.

Aegaeon

Answer 7

Depending on the size of your database you could also consider to drop the database after each test and re-build it to initial condition. Run this with AfterEach or call the method manually within the test when needed. This will not kill postgres container. I have a 'relatively' big complex database and this added ~100ms to each test (but again, this varies). Do note, that this may require you to have @Transactional on the test class which is undesirable.

    // After each integration test, we restore database to prestige condition with default data loaded from sql files
    // Integration test data is loaded from: db/changelog/db.changelog-integtest.xml
    // db.changelog-integtest.xml also loads domain data from: db/changelog/db.changelog-master.xml
    @AfterEach
    public void restoreDatabaseToInitialCondition() throws Exception {
        // Setup connection
        Connection c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgres", "postgres", "postgres");
        JdbcConnection jdbcConnection = new JdbcConnection(c);

        // Initialize Liquibase
        Liquibase liquibase = new liquibase.Liquibase("db/changelog/db.changelog-integtest.xml", new ClassLoaderResourceAccessor(), jdbcConnection);

        // Refresh the database to the latest state
        liquibase.dropAll();
        liquibase.update(new Contexts());

        // Commit the transaction
        c.commit();

        // Close the Liquibase and connection instances to avoid next test staring before operations are complete in the container
        liquibase.close();
        c.close();
    }

Beecham