Clearing all postgres tables for tests

Have you written acceptance tests or integrations tests in Java/Kotlin using Testcontainers Postgresql, and you reuse the container across tests to save time? Then you likely want to clear the database to avoid contamination between tests.

Here is a small code snippet to clear postgres tables (except flyway migrations). It uses Jdbi to execute the SQL.

jdbi.useHandle<Exception> { handle ->
    handle.execute(
        """DO ${'$'}${'$'}DECLARE tablename TEXT;
  BEGIN
      FOR tablename IN (SELECT table_name
                         FROM information_schema.tables
                         WHERE table_schema = 'public'
                           AND table_type = 'BASE TABLE'
                           AND table_catalog = '${testDatabaseName}'
                           AND table_name not like 'flyway%')
      LOOP
          EXECUTE 'TRUNCATE TABLE ' || tablename || ' RESTART IDENTITY CASCADE;';
      END LOOP;
  END${'$'}${'$'};""",
    )
  }

Put this in a function like fun clearAllData() { ... }, and run it in Junit's beforeEach or afterEach.