[FIXED] Why are postgresql tables empty although filled when starting Testcontainer?

Issue

I am using Testcontainers 1.15.2 (using postgres image 13.2-alpine) with Spring Boot 2.4.3. The Testcontainer is started using an init script which starts with a type definition, a table creation and insert values into it. I even perform a COMMIT; at the end but did not define a schema or so. When I start the Spring Boot app the console output shows me that the init script was executed successfully. When I execute a SELECT * FROM the result is empty. So...why are the postgresql tables empty although I did inserts before?

CREATE TYPE Erklaerungstyp AS ENUM ('AAAAA', 'BBBBB', 'CCCCC', 'DDDDD');
CREATE TYPE Geschlecht AS ENUM ('D', 'F', 'M');

DROP TABLE IF EXISTS Anschrift;
CREATE TABLE Anschrift (
  a_id          SERIAL PRIMARY KEY,
  Zusatz        VARCHAR(255),
  Strasse       VARCHAR(30) NOT NULL,
  Hausnummer    VARCHAR(30) NOT NULL,
  plz           VARCHAR(5) NOT NULL,
  Ort           VARCHAR(80) NOT NULL,
  Bundesland    VARCHAR(20),
  Land          VARCHAR(20) NOT NULL,
  create_Date   DATE NOT NULL,
  modify_Date   DATE
);
INSERT INTO Anschrift VALUES (1, null, 'Musterstrasse', '13M', '12345', 'Berlin', 'Berlin', 'Deutschland', '2001-09-28');
INSERT INTO Anschrift VALUES (2, 'bei Müller', 'Musterweg', '1-3', '54321', 'Musterhausen', 'Muster-Hausen', 'Deutschland', '2002-03-11');

DROP TABLE IF EXISTS Person;
CREATE TABLE Person (
  ep_id                 SERIAL PRIMARY KEY,
  Geschlecht            Geschlecht,
  Vorname               VARCHAR(30) NOT NULL,
  Familienname          VARCHAR(30) NOT NULL,
  Geburtsname           VARCHAR(30) NOT NULL,
  Titel                 VARCHAR(10),
  Geburtsdatum          Date NOT NULL,
  Geburtsort            VARCHAR(30),
  Anschrift             INTEGER REFERENCES Anschrift(a_id),
  Email                 VARCHAR(80),
  Telefon               VARCHAR(20),
  Versichertennummer    VARCHAR(15) NOT NULL,
  create_Date           DATE  NOT NULL,
  modify_Date           DATE
);
INSERT INTO Person VALUES (1, 'M', 'Max', 'Mustermann', 'Mustermann', 'Dipl.-Inf.', '01.01.1901', 'Berlin', 1, 'Max.Mustermann@max.de',
'0111 12 34 56 789', 'X000Y111Z999', '2001-09-28');

COMMIT;

I instantiate the Testcontainer in an abstract superclass for tests to be used in all inheriting subclassing tests:

@ActiveProfiles("test")
@Testcontainers
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
public abstract class AbstractApplicationIT {

    final static DockerImageName POSTGRES_IMAGE = DockerImageName.parse("postgres:13.2-alpine");

    @Container
    public static PostgreSQLContainer<?> postgreSQLContainer = new PostgreSQLContainer<>(POSTGRES_IMAGE);

    @Test
    public void contextLoads() {
    }

}

In a subclass I do:

@Transactional
@TestInstance(TestInstance.Lifecycle.PER_CLASS)
public class XxxIT extends AbstractApplicationIT {

    @Value("${spring.datasource.password}")
    private String password;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.dbname}")
    private String dbName;
    @Value("${spring.datasource.initScript}")
    private String initScript;

    @Autowired
    private AnschriftJpaDao dao;

    @Autowired
    private XxxService xxxService;

    @BeforeAll
    public void setup() {
        postgreSQLContainer = new PostgreSQLContainer<>(POSTGRES_IMAGE)
                .withDatabaseName(dbName)
                .withUsername(username)
                .withPassword(password)
                .withInitScript(initScript);
        postgreSQLContainer.start();
    }

    @Test
    public void checkDbContainerIsAlive() {
        assertThat(this.dao.findAll()).isNotNull();
    }
}

...and the test is green but when I do

@Test
public void anschrift_can_be_found() {
    assertThat(this.dao.findAll().size() == 1);
    List<Anschrift> anschriftList = this.dao.findAll();
    System.out.println(anschriftList.size());
}

...the test is green but anschriftList is empty. Why?

And if I use Anschriften PK as a FK in Person entity...I get a LazyLoadingException although specifying fetch = FetchType.EAGER in the relationship definitions. Why?

Solution

In my application-test.yaml I defined

jpa:
  hibernate:
    ddl-auto: create-drop

as I found this on the internet.

-> Outcommenting this line leads to filled tables.

Answered By - du-it

Answer Checked By - Willingham (PHPFixing Volunteer)