java - 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.

question from:https://stackoverflow.com/questions/65947435/is-there-a-way-to-drop-all-tables-or-truncate-inside-a-postgres-testcontainer

Answer 1

Create a template test database with the name test_template.

After each test,

1. disconnect all sessions from the test database (not required with PostgreSQL v13):

   SELECT pg_terminate_backend(pid)
   FROM pg_stat_activity
   WHERE datname = 'test';
   

2. drop the database with

   DROP DATABASE test;
   

   In v13, use the additional FORCE option.

3. 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.