Spring boot test using testcontainers postgresql

user_3165644 0
Viewed 7231

I have a Java Spring boot project, heavily using the database (Postgres) for it's repository/data. It is the basic MVC project, controllers are all REST controllers. The project works well (service is up, able to call service via REST clients and all).

Now, I am adding the unit tests to it. I am pretty new to Spring boot and mostly the unit test part. Owing to the CI/CD (build pipeline), I cannot use the persistent/external DB for tests. Hence I need to use in-memory DB.

The initial run (Main class) runs a bunch of DB queries to build up cache while project comes up. So I would need postgres DB for testing (lots of DB functions used).

Basically, I would need to use Testcontainers (postgresql). I am writing a very basic test first to get hold of it.

I have the schema.sql and data.sql stored (to be used only for testing).

src
   |
   main
   test
        |
        resources
                |
                application-test.properties
                schema.sql
                data.sql

Relevant pom.xml

<properties>
    <java.version>11</java.version>
    <testcontainers.version>1.15.1</testcontainers.version>
</properties>

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-cache</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-security</artifactId>
    </dependency>
    <dependency>
        <groupId>io.jsonwebtoken</groupId>
        <artifactId>jjwt</artifactId>
        <version>0.9.1</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-validation</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-devtools</artifactId>
        <scope>runtime</scope>
        <optional>true</optional>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-actuator</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
        <exclusions>
            <exclusion>
                <groupId>org.junit.vintage</groupId>
                <artifactId>junit-vintage-engine</artifactId>
            </exclusion>
        </exclusions>
    </dependency>

    
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>com.fasterxml.jackson.core</groupId>
        <artifactId>jackson-databind</artifactId>
        <version>2.11.2</version>
    </dependency>
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-lang3</artifactId>
        <version>3.11</version>
    </dependency>
    <dependency>
        <groupId>commons-beanutils</groupId>
        <artifactId>commons-beanutils</artifactId>
        <version>1.9.4</version>
    </dependency>
    <dependency>
        <groupId>org.json</groupId>
        <artifactId>json</artifactId>
        <version>20201115</version>
    </dependency>
    

    <dependency>
        <groupId>org.testcontainers</groupId>
        <artifactId>testcontainers</artifactId>
        <version>${testcontainers.version}</version>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.testcontainers</groupId>
        <artifactId>localstack</artifactId>
        <version>${testcontainers.version}</version>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.testcontainers</groupId>
        <artifactId>junit-jupiter</artifactId>
        <version>${testcontainers.version}</version>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.testcontainers</groupId>
        <artifactId>postgresql</artifactId>
        <version>${testcontainers.version}</version>
        <scope>test</scope>
    </dependency>

</dependencies>

My Test class:

@Testcontainers
@Sql(scripts = {"file:src/test/resources/schema.sql","file:src/test/resources/data.sql"})
class ApplicationTests {

    @Container
    static PostgreSQLContainer<?> postgreSQLContainer = new PostgreSQLContainer<>("postgres:12")
            .withUsername("testcontainers")
            .withPassword("testcontainers")
            .withDatabaseName("tescontainers");


    @Test
    void testPostgreSQLModule() throws SQLException {
        try (Connection connection = DriverManager
                .getConnection(postgreSQLContainer.getJdbcUrl(), "testcontainers", "testcontainers");
             PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM table_from_schema")) {
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                while (resultSet.next()) {
                    System.out.println(resultSet.getString("column1"));
                }
            }
        }
    }
}

I am simply trying to test the DB.

However, when I run the test, it fails saying that

org.postgresql.util.PSQLException: ERROR: relation "table_from_schema" does not exist

I tried to debug it, i.e. stopped just inside my Test (testPostgreSQLModule). I can see the docker component with Postgres.

$ docker ps
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS              PORTS                     NAMES
fc3ff1e04ceb        postgres:12                 "docker-entrypoint.s…"   16 seconds ago      Up 15 seconds

But when I login to it and run psql, I see the DB (tescontainers) is created, however it does not have any schema (tables/functions).

tescontainers=# \dt
Did not find any relations.
tescontainers=#

Basically, my files are not being run.

Does @SQL annotation on class level does not work with Testcontainers initialization as in my case?

What is needed here so both my initial scripts run?

I tried using .withInitScript, and it runs. However, I have lots of data to initialize and the file is too large (and would grow), so I separate DDL (schema) and Inserts (data). Now, my issue is how to run multiple init files (schema.sql, data.sql) using "withInitScript" ? So I tries @SQL annotation, but it does not seems to work.

---UPDATE/EDIT----

To give the context clear, I am looking for below. If anyone can guide please?

  1. All profile (dev/ist/uat/prod) should be using their respective persistence DB (from their application.env.properties).
  2. Only for the test, I need the in-memory DB, but cannot use H2 (and similar) as I have lots of DB related tests and need Postgres (functions , etc). So, trying out Testcontainers.
  3. When the application boots up, it fetches some data from respective DB (based on env) to prepare the initial cache and other methods will use it while servicing any rest calls. Hence, for Test (only) I need a new in-memory DB with all schema/data (which I can provide via SQL files) , so that while test, the boot up should use that test DB and corresponding tests wil work based on that initial data.
  4. So I need a way to bring up test DB (in-memory/testcontainers) whenever Test runs, and pass multiple SQL files for initialization of Test DB (before any test runs). Any idea on what is the best approach?
testcontainers spring-boot postgresql unit-testing java
2 Answers

I guess you missed the following

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>test</scope>
</dependency>

test-dependency. (Or remove the <scope/> at all.)

Table not found errors often indicate a mismatch of the schema and model and the available driver.

Also your test class seems to be spring-boot independent. How do you run it? You could benefit of auto-configuration, like described here: https://www.baeldung.com/spring-boot-testcontainers-integration-test (but I guess you don't like that ;))

user_4195810

The error is telling you no such table exists

org.postgresql.util.PSQLException: ERROR: relation "table_from_schema" does not exist

It seems that the connection to the DB is established, but the query you're attempting is not working.

I had a quick look and from what I could see the table table_from_schema is not present in the standard postgres schema.

However select * from pg_tables; may work. Try accessing a different table and see if this resolves the issue for you temporarily, so you can confirm the issue is selecting from a table name that doesn't exist rather than something more obscure.

If you are able to, you can put a break point in after the docker container has spun up and connect using a database client. Once you're connected you can then try inspect the database manually, testing your SQL commands before you copy-paste them to your codebase.

If you want the SQL scripts to run in your docker container, you need to mount the test/resources dir to the container's /docker-entrypoint-initdb.d dir using a volume mapping for the container. Any *.sh and *.sql scripts will then be run. You may need to restructure your sql scripts so you have one root script that calls scripts in a directory lower down. This is especially true if there's any ordering requirement between the two.

See https://hub.docker.com/_/postgres "Initialization scripts"

Mounting into your container with testcontainers is as simple as:

String pathToFile = "<project-root-dir>/src/test/resources";
new GenericContainer(...)
        .withFileSystemBind(pathToFile, "/docker-entrypoint-initdb.d", BindMode.READ_ONLY)

See https://www.testcontainers.org/features/files/

user_76190340
Related