Is there a way to drop all tables or truncate inside a postgres testcontainer

Question

I'm looking for a way to keep my component tests self contained. So to achieve this behavior, in some of the tests I need to have a 'clean database' or at least a 'clean table'. I still couldn't find a way to do this inside a testcontainer. So here is what I've tried so far: My container setup class:

public class PostgreSqlTestContainer implements QuarkusTestResourceLifecycleManager {

public static final PostgreSQLContainer<?> POSTGRES = new PostgreSQLContainer<>("postgres:alpine");

@Override
public Map<String, String> start() {
    POSTGRES.start();         
    return some_db_config_as_per_doc;
}

@Override
public void stop() {        
    POSTGRES.stop();
}

Here is the tests class:

@QuarkusTest
@QuarkusTestResource(PostgreSqlTestContainer.class)
class UserResourcesTest {

  @Test
  scenario_one(){
     // create a new user
     // do some stuff (@POST.. check HTTP == 201) 
  }

  @Test
  scenario_two(){
     // create new user
     // do some stuff (@POST.. check HTTP == 201) (pass)
     // look for all users on database
     // do more stuff (@GET..  check HTTP == 200) (pass)
     // assert that only 1 user was found
     // since scenario_one should not interfere with scenario_two (fail)
  }

}

The second scenario fails since some 'dirty' from the first test was still on the db container. I've tried to stop/start the container for each test. (very, very slow process, and sometimes I get an error, and very slow again).

    @BeforeEach
    void setup(){
        PostgreSqlTestContainer.POSTGRES.stop();
        PostgreSqlTestContainer.POSTGRES.start();
    }

Also tried to truncate the table / drop the whole db:

    @Inject
    EntityManager entityManager;

    @BeforeEach
    private void rollBack(){
        truncate();
    }

    void truncate(){
      Query nativeQuery = entityManager.createNativeQuery("DROP DATABASE IF EXISTS db_name");
      nativeQuery.executeUpdate();
    }

I'm looking for any workaround for this problem, I just want to somehow use a @BeforeEach to clean up the DB before each test. I mean, all I want is a clean environment for each test.

score 1 · Answer 1 · answered Aug 24 '23 at 20:02

Assuming you’re using Panache and Active Record pattern, you can do this:

@BeforeEach
@Transactional
public void cleanUp() {
   MyEntity.deleteAll();
}

If you are using repositories then simply inject your repository and call the deleteAll() method.

And there is also @TestTransaction available for unit tests. This will simply rollback any changes to the db after the test has finished.

Btw, I’m typing from my phone, so apologies for any syntax errors.

Laurenz Albe · Answer 2 · 2021-01-29T13:03:04.003

0

Create a template test database with the name test_template.

After each test,

disconnect all sessions from the test database (not required with PostgreSQL v13):
```
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'test';
```
drop the database with
```
DROP DATABASE test;
```
In v13, use the additional FORCE option.

create a new test database with

CREATE DATABASE test TEMPLATE test_template;

Note: you have to enable autocommit in the JDBC driver for CREATE DATABASE and DROP DATABASE to work.

edited Jan 29 '21 at 13:03

answered Jan 29 '21 at 08:28

Laurenz Albe

209,280
17
206
263

Can you provide some example? – Johnnes Souza Jan 29 '21 at 12:50
I did, didn't I? – Laurenz Albe Jan 29 '21 at 12:54
By using the same way the doing ATM (`createNativeQuery("DROP DATABASE db_name");` I'm getting an error: Enable to execute update/delete query statement. – Johnnes Souza Jan 29 '21 at 12:59
Do you get a PostgreSQL error message along with that meaningless message, perhaps as a nested exception? Did you enable autocommit? – Laurenz Albe Jan 29 '21 at 13:02
I meant, 'Unable to execute...' not enable... hehe – Johnnes Souza Jan 29 '21 at 13:05

score 0 · Answer 3 · answered Aug 27 '23 at 17:30

Discard changes made during a test

Truncate Tables: Just truncate all tables instead of dropping the entire database. It is quicker too and probably the best option in your case.

Code Example:

// Code Example on how to truncate tables
@BeforeEach
void cleanDatabase() {
    entityManager.createNativeQuery("TRUNCATE TABLE user_table CASCADE").executeUpdate();
    // Here you can add more tables if you want to
}

Transaction Rollback:
I guess the most common option to ensure that changes made during a test are discarded is this one. You basically start a transaction and the very start of the test and roll it back when its finished.

Code Example:

// Example Code of a Transaction Rollback

@Inject
UserTransaction userTransaction;

@BeforeEach
void beginTransaction() throws Exception {
    userTransaction.begin();
}

@AfterEach
void rollbackTransaction() throws Exception {
    userTransaction.rollback();
}

With this approach you are also faster than dropping and recreating the whole database or tables. However this will only work if your tests are transactionally safe and you are not testing non-transactional features.

Liquibase or Flyway:
This only works if you are using a migration tool like Liquibase or Flyway. You can basically reset the database to a specific migration or baseline before each test.

Code Example:

// Code Example with Flyway

@Inject
Flyway flyway

@BeforeEach
void resetDatabase(){
    // This method cleans the database.
    flyway.clean();
    // This method applies all migrations from the start.
    flyway.migrate();
}

If you have any questions feel free to ask.

score -1 · Answer 4 · edited Aug 26 '23 at 21:34

You can try with this:

Remove the static clause from the container definition. It will force it to create the container on every start() execution.

PostgreSQLContainer<?> POSTGRES ....
You can also remove the stop in the @beforeach I think it is not necesary

You can create the tables when you start the container providing the schema SQL:

 PostgreSQLContainer<?> postgresContainer = new PostgreSQLContainer< ("postgres:14-alpine")
           .withDatabaseName("db")
           .withUsername("username")
           .withPassword("password")
           .withCopyFileToContainer(
                MountableFile.forClasspathResource("/schema.sql"),
                "/docker-entrypoint-initdb.d/"
           );

And if you want to insert some data in depending of your test method you can do something like this and call it in the beginning of your test:

 private void loadData(String scriptPath) {
     try {
         postgresContainer.copyFileToContainer(
              MountableFile.forClasspathResource(scriptPath),
              "/data/import.sql"
         );
         postgresContainer.execInContainer(
              "psql",
              "-U",
              postgresContainer.getUsername(),
              "-d",
              postgresContainer.getDatabaseName(),
              "-f",
              "/data/import.sql"
        );
    } catch (IOException e) {
        throw new RuntimeException(e);
    } catch (InterruptedException e) {
        throw new RuntimeException(e);
    }
}

Is there a way to drop all tables or truncate inside a postgres testcontainer

4 Answers4

Discard changes made during a test