I am currently setting up integration test suite for one the RESTful CRUD apis and the frameworks I use put some limitations.

Stack: Java 21, Testcontainers, Liquibase, R2DBC with Spring

I want my integration tests to be independent, fast and clean, so no Spin up a new container per each test.

Some of the options I could find online on how I can handle:

1. Do not cleanup DB tables between test methods but use randomised data
2. Make each test method Transactional (can't use it out of the box with R2DBC)
3. Spin up a single container and create new database per each test method
4. Create dump before test method and restore it after
5. ....

Right now I am spinning up a single container per test class, my init/cleanup methods look like following:

@BeforeEach
void initEntities() {
    databaseClient.sql("""
                    INSERT INTO .........
                    """)
            .then()
            .subscribe();
}

@AfterEach
void cleanupEntities() {
    databaseClient.sql("TRUNCATE <tables> RESTART IDENTITY CASCADE")
            .then()
            .subscribe();
}

which theoretically works fine. Couple of things I am concerned about are:

1. I insert test data in the test class itself. Would it be better to extract such pieces into .sql scripts and refer these files instead? Where do you declare test data? It will grow for sure and is going to be hard to maintain.
2. As we are using PostgreSQL, I believe TRUNCATE RESTART IDENTITY CASCADE is Postgre-specific and may not be supported by other database systems. Is there a way to make cleanup agnostic of the DB system?

Any better ways to implement integration test suite? Code examples are welcomed. Thanks