Fixing data pollution between tests

[Youans]

Hi there thanks for such a great lib

We have been using H2 for our test suite and we are planning to migrate to TestContainer only problem is that we have some test cases especially the ones that involving @Async blocks within that when executed it pollutes the database records so we used to backup the database in a @BeforeEach and then restore it back in an @AfterEach

Doing so in H2 was pretty easy and fast using SCRIPT TO scriptPath in @BeforeEach and DROP ALL OBJECTS, RUNSCRIPT FROM scriptPath in @AfterEach

I was trying to do the same idea some stuff like
@BeforeEach > postgreSQLContainer.execInContainer("pg_dump", "-U" , "test" , "-w" , "-Fc","-d", "test", "-f", "/tmp/backup");

@AfterEach > postgreSQLContainer.execInContainer("pg_restore","-U", "test", "-w","--clean", "--dbname=test", "/tmp/backup");

but was left facing locking problems and eliminating these locks will terminate the test context

I also tried separate container per test but it takes alot of time

Any idea how to overcome such a problem

Note, This is not a bug in test container but it might be a feature request

[kiview]

Hey @Youans, this is a great question and I just transferred it from issues to GitHub discussions. Let's try to come up with some ideas on how to best solve this for PostgreSQL. And who knows, maybe the community will have other good suggestions?

I don't completely understand why your approach does not work, with which technology are you testing? And what do you mean with test context in this case?

In the past, I personally used https://github.com/fastmonkeys/stellar, also in conjunction with Testcontainers, but it seems to not be actively maintained anymore.

Another good alternative can be to use Flyway and run a flyway.clean() and flyway.migrate() before each test. Might still be expensive, depending on your use case, but worth a try. You can also try to clean up data in a domain-dependent way, rather than the whole schema, but this can be error-prone, depending on what you do.

[tnleeuw]

I'm not sure about the entire context and so I'm not sure about the best answer to this question.

• If there is a TestContainer per test instead of per test-run, won't it have a clean database on each test? Yes that means starting a new container for every test so it'll have performance overhead
• Why do you get issues with locks? Is that due to parallel test execution? What if all tests are executed strictly sequentially?
  Perhaps you'd need a test-db instance per thread?
• In the past I've dealt with tests polluting the database by ensuring that each test case uses guaranteed unique data by generating data in the test-setup with new random IDs etc, inserting that, and then doing my tests against that.
• I've never used Flyway myself but Liquibase. You can use Liquibase inside your code / tests as well to create your schema and by specifying a context, you can use it to populate with some test-data as well.
  However, if you already have problems with locks etc then I'm afraid that using either Flyway or Liquibase or anything similar, you'll still have these problems with locks.

[Youans]

Sorry for late reply, Thanks for your great notes
For the stack I use Spring Boot Test & JUnit Jupiter 5 and Liquibase for database migration

I managed to make my approach work but it was not an easy road

But just to summarize the problem,

I was trying to restore dump completely [structure + data] doing so requires to drop current opened database but this was not an option since I faced tons of
org.postgresql.util.PSQLException: FATAL: terminating connection due to administrator command
and can not drop current opened database errors so this was a no go option

Luckily,
There is an option to backup data only in postgres but when I tried to restore I faced the 1st problem the database locks on some tables , because the the pg_restore command was blocked by these locked tables which were used by the current transaction [running by the test] so it was technically waiting for the current transaction to commit and it was not committing because the @AfterEach block was not finished and it was not finished because of the pg_restore command was executing so it was a deadlock, So I needed to pragmatically commit current opened transaction first before running the restore command after doing so the restore command ran but then 2nd problem showed up, some foreign key constraint errors luckily postgres offer a flag to use when restoring --disable-triggers which silent these errors so this fixed the problem, Then faced 3rd problem data duplication because simply data was already there in table so I needed to clear objects in database and it was not a straight forward command but after then restoring did succeeded finally!

Here is a complete code in case anyone step on this problem in the future, I added some comments read them carefully

`

import org.springframework.jdbc.core.JdbcTemplate;
import javax.annotation.PostConstruct;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.Test;
import org.springframework.test.context.jdbc.Sql;
import org.springframework.test.context.jdbc.SqlConfig;
import static org.springframework.test.context.jdbc.SqlConfig.TransactionMode.ISOLATED;

....

// Reason for @PostConstruct to ensure that it will run before each test
//  and reason using this over @BeforeEach is that the @Sql runs *before* @BeforeEach causing data to pollute again before
//  creating the dump, @PostConstruct on the other hand will run before each test case but also before @Sql annotation
//  TODO move this somewhere else where it will execute once after all database migration is done
//  @throws IOException
//  @throws InterruptedException
@PostConstruct
public void backupDatabase() throws IOException, InterruptedException {
    postgreSQLContainer
        .execInContainer("pg_dump", "-U" , "test" , "-w" , "--data-only", "-Fc","-d", "test", "-f", "/tmp/backup");
}


@Test
@Sql(
    scripts = {"run-some-test-case-specific-script.sql"},
    config = @SqlConfig(transactionMode = ISOLATED)

)
void testSomeLogicThatWillPolluteDatabase() {
    // do any @Async stuff
}

@AfterEach
void restoreDatabase() throws IOException, InterruptedException {
    // Clear all objects in database
    jdbcTemplate.queryForList("select 'truncate table \"' || tablename || '\" cascade;' from pg_tables where schemaname='public';\n", String.class)
        .forEach(jdbcTemplate::update);

    // Programmatically commit transaction to avoid data locking when restoring
    TestTransaction.flagForCommit();
    TestTransaction.end();

    // Restore database from dump in /tmp folder
    postgreSQLContainer
        .execInContainer("pg_restore","-U", "test", "-w", "--data-only", "--disable-triggers", "--dbname=test", "/tmp/backup");
}

`

FYI this was done using Spring Boot v2.2.6.RELEASE & Postgres:9.6.24

[kiview]

Since you are @PostConstruct here, I assume in your setup you will get a new Spring Context for every test. This will likely slow down things a lot, so something to be aware of.

But then again, this pg_dump and pg_restore is also quite heavyweight, so I think it is aligned there.

Nevertheless, thanks a lot for sharing your solution 🙌

[MallocForever]

The way we handled this in my previous team, we first created a regular postegresql database and populated all of the tables with the required data. Then we went to the filesystem and got all of the table files and created a new docker image with those files using postgresql as base image. By copying over the schema files, that would initialize our database. Finally we used that image in our tests, that way each test had a fresh dataset to use no bothering with transactions and what not. The database would startup with all of the data already there and the tests just had to do their testing against it. Worked flawlessly and very quick, much faster than using insert into statements in a Before method call etc (edited)