Adding Data to a testcontainer using a function from a repository

Question

I've set up a testcontainer for use during integration testing. Whilst it works and the test data is upserted to the test container, I cannot accurately check that my repository function, using JOOq, actually upserts any data at all because it seems to go nowehere.

Here is the code for my test-data.sql file:

CREATE TABLE IF NOT EXISTS users
        (uuid UUID NOT NULL, first_name VARCHAR(255),
         last_name VARCHAR(255), email_address VARCHAR(255));
INSERT INTO users (uuid, first_name, last_name, email_address)
        VALUES (etc)

here is the code for my UsersRepositoryTest.java file:

package users;

import cache.RedisBackedCache;
import com.example.config.JooqTestConfig;
import org.example.Application;
import org.jooq.DSLContext;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Order;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.flyway.FlywayProperties;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;
import org.springframework.test.context.DynamicPropertyRegistry;
import org.springframework.test.context.DynamicPropertySource;
import org.springframework.test.context.junit.jupiter.SpringExtension;
import org.testcontainers.containers.GenericContainer;
import org.testcontainers.containers.PostgreSQLContainer;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;
import org.testcontainers.utility.DockerImageName;

import java.util.List;
import java.util.UUID;

import static org.jooq.codegen.maven.example.Tables.USERS;
import static org.junit.jupiter.api.Assertions.*;


@SpringBootTest(classes = {Application.class, JooqTestConfig.class})
@Testcontainers
@ExtendWith(SpringExtension.class)
@ActiveProfiles("test")
public class UsersRepositoryTest {

    private UsersRepository usersRepository;

    @Autowired
    private DSLContext dslContext;

    @Container
    private static final PostgreSQLContainer<?> postgreSQLContainer = new PostgreSQLContainer<>("postgres:11.1")
            .withDatabaseName("integration-tests-db").withUsername("username").withPassword("password")
            .withInitScript("db/test-data.sql");

    static {
        postgreSQLContainer.start();
    }

    UsersResource user = new UsersResource(UUID.randomUUID(), "Tom", "Campbell", "[email protected]");


    @BeforeEach
    void setUp() {
        usersRepository = new UsersRepository(dslContext);
    }

    private UsersResource upsertedUser(String emailAddress) {

        return dslContext.select(USERS.asterisk())
                .from(USERS)
                .where(USERS.EMAIL_ADDRESS.eq(emailAddress))
                .fetchAnyInto(UsersResource.class);
    }


    @Test
    void testConnectionToDatabase() {
        Assertions.assertNotNull(usersRepository);
    }


    @Test
    public void addUserSuccess(){
        usersRepository.upsertUsers(user);
        List<UsersResource> users = usersRepository.getAllUsers();

        assertTrue(users.contains(user));

    }

    @Test
    void addUserAlreadyExists(){
        usersRepository.upsertUsers(user);
        Throwable exception = assertThrows(IllegalStateException.class, () -> usersRepository.upsertUsers(user));
        assertEquals("User could not be created", exception.getMessage());

    }

    @Test
    void updateUser(){
        usersRepository.upsertUsers(user);

        UUID userUUID = dslContext.select(USERS.UUID)
                .from(USERS)
                .where(USERS.EMAIL_ADDRESS.eq(user.emailAddress))
                .fetchSingleInto(UUID.class);

        UsersResource updatedUser = new UsersResource(userUUID
        usersRepository.updateUserName(updatedUser);
        assertEquals(upsertedUser.uuid, updatedUser.uuid);
        assertEquals(upsertedUser.firstName, updatedUser.firstName);
        assertEquals(upsertedUser.lastName, updatedUser.lastName);
        assertEquals(upsertedUser.emailAddress, updatedUser.emailAddress);
    }

    @Test
    void deleteUserSuccess(){
        usersRepository.upsertUsers(user);

        UUID userUUID = dslContext.select(USERS.UUID)
                .from(USERS)
                .where(USERS.EMAIL_ADDRESS.eq(user.emailAddress))
                .fetchSingleInto(UUID.class);

        usersRepository.deleteUser(userUUID);
        boolean userRecordExists = dslContext.fetchExists(USERS, USERS.EMAIL_ADDRESS.eq(user.emailAddress));
        assertFalse(userRecordExists);
    }

}

and finally, here is the pom file:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-3.9.7.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>gown-qub-app</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>22</maven.compiler.source>
        <maven.compiler.target>22</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.3.0</version>
    </parent>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jooq</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-logging</artifactId>
            <version>3.2.4</version>
        </dependency>

        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>5.8.1</version>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>1.19.8</version>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>postgresql</artifactId>
            <version>1.19.3</version>
        </dependency>

        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>2.2.224</version>
        </dependency>
        <dependency>
            <groupId>org.jooq</groupId>
            <artifactId>jooq-postgres-extensions</artifactId>
            <version>3.19.9</version>
        </dependency>
        <dependency>
            <groupId>org.jooq</groupId>
            <artifactId>jooq-codegen-maven</artifactId>
            <version>3.19.9</version>
        </dependency>
        <dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <version>2.7.1</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-testcontainers</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.platform</groupId>
            <artifactId>junit-platform-commons</artifactId>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
        </dependency>

        <dependency>
            <groupId>redis.clients</groupId>
            <artifactId>jedis</artifactId>
            <version>4.0.1</version>
        </dependency>
    </dependencies>


    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>3.3.0</version>
                <executions>
                    <execution>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
            <plugin>

                <groupId>org.jooq</groupId>
                <artifactId>jooq-codegen-maven</artifactId>
                <version>3.19.9</version>

                <executions>
                    <execution>
                        <goals>
                            <goal>generate</goal>
                        </goals>
                    </execution>
                </executions>

                <dependencies>
                    <dependency>
                        <groupId>org.postgresql</groupId>
                        <artifactId>postgresql</artifactId>
                        <version>42.6.0</version>
                    </dependency>
                </dependencies>

                <configuration>
                    <jdbc>
                        <driver>org.postgresql.Driver</driver>
                        <url>jdbc:postgresql:postgres</url>
                    </jdbc>

                    <generator>
                        <database>
                            <name>org.jooq.meta.postgres.PostgresDatabase</name>
                            <includes>.*</includes>
                            <excludes></excludes>
                            <inputSchema>public</inputSchema>
                        </database>
                        <target>
                            <packageName>org.jooq.codegen.maven.example</packageName>
                            <directory>target/generated-sources/jooq</directory>
                        </target>
                    </generator>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-jar-plugin</artifactId>
                <version>3.4.1</version>
                <configuration>
                    <archive>
                        <manifest>
                            <addClasspath>true</addClasspath>
                            <classpathPrefix>lib/</classpathPrefix>
                            <mainClass>org.example.Application</mainClass>
                        </manifest>
                    </archive>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>22</source>
                    <target>22</target>
                    <release>22</release>
                    <compilerArgs>--enable-preview</compilerArgs>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.testcontainers</groupId>
                <artifactId>testcontainers-jooq-codegen-maven-plugin</artifactId>
                <dependencies>
                    <dependency>
                        <groupId>org.testcontainers</groupId>
                        <artifactId>postgresql</artifactId>
                        <version>1.19.3</version>
                    </dependency>
                    <dependency>
                        <groupId>org.postgresql</groupId>
                        <artifactId>postgresql</artifactId>
                        <version>42.6.0</version>
                    </dependency>
                </dependencies>
                <executions>
                    <execution>
                        <id>generate-jooq-sources</id>
                        <goals>
                            <goal>generate</goal>
                        </goals>
                        <phase>generate-sources</phase>
                        <configuration>
                            <database>
                                <type>POSTGRES</type>
                                <containerImage>postgres:15.3-alpine</containerImage>
                            </database>
                            <flyway>
                                <locations>
                                    filesystem:src/main/resources/db/migration
                                </locations>
                            </flyway>
                            <jooq>
                                <generator>
                                    <database>
                                        <includes>.*</includes>
                                        <excludes>flyway_schema_history</excludes>
                                        <inputSchema>public</inputSchema>
                                    </database>
                                    <target>
                                        <packageName>example.micronaut.jooq</packageName>
                                        <directory>target/generated-sources/jooq</directory>
                                    </target>
                                </generator>
                            </jooq>
                        </configuration>
                    </execution>
                </executions>
            </plugin>

        </plugins>
    </build>


</project>

The function 'upsertUsers' looks like this:

    public void upsertUsers(UsersResource users) {
        boolean emailExists = dslContext.fetchExists(
                dslContext.selectOne().from(USERS).where(USERS.EMAIL_ADDRESS.eq(users.emailAddress))
        );

        try {
            if(emailExists) {
                throw new IllegalStateException("This user already exists");
            } else {
                dslContext.insertInto(USERS)
                        .set(USERS.UUID, UUID.randomUUID())
                        .set(USERS.FIRST_NAME, users.firstName)
                        .set(USERS.LAST_NAME, users.lastName)
                        .set(USERS.EMAIL_ADDRESS, users.emailAddress)
                        .onConflict(USERS.EMAIL_ADDRESS)
                        .doNothing()
                        .execute();
            }

        } catch(Exception e) {
//            logger.info("User could not be created", e);
        }

I believe it's because my JOOq and then testcontainers don't align therefore it cannot be checked.

I created the users table through the dbeaver UI.

What does upsertUsers() do? Please update your question with its code. — Lukas Eder, Commented Jul 2 at 9:28

Lukas Eder · Accepted Answer · 2024-07-02 14:23:10Z

0

So, your observed problem is:

because it seems to go nowhere.

And the query you wrote is using:

.onConflict(USERS.EMAIL_ADDRESS)
.doNothing()

As in ON CONFLICT .. DO NOTHING

I'd say, everything works as expected? (Except you should probably use doUpdate() instead...)

answered Jul 2 at 14:23

Lukas Eder

218k133 gold badges711 silver badges1.6k bronze badges

The biggest problem I am having is whilst it works - it does not utilise my testcontainer. So anythign that is upserted will be added to the actual local database and not the container created upon startup
– tcampbell649
Commented Jul 2 at 14:30

Add a comment |

tcampbell649 · Accepted Answer · 2024-07-02 14:35:14Z

0

The issue was that there was no dynamic property source given. I believe that the spring datasource still pointed to my local database as opposed to the testcontainer I had set up. Using this code fixed the issue:

    @DynamicPropertySource
    static void setProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", postgreSQLContainer::getJdbcUrl);
        registry.add("spring.datasource.username", postgreSQLContainer::getUsername);
        registry.add("spring.datasource.password", postgreSQLContainer::getPassword);
    }

answered Jul 2 at 14:35

tcampbell649

212 bronze badges

You can also use the @ServiceConnection annotation on the @Container field to wire your tests to the Testcontainers database. This is more concise than explicitly mapping fields with @DynamicPropertySource. See docs.spring.io/spring-boot/reference/testing/…
– Scott Frederick
Commented Jul 2 at 16:00

Add a comment |

Collectives™ on Stack Overflow

Adding Data to a testcontainer using a function from a repository

2 Answers 2

Not the answer you're looking for? Browse other questions tagged
java
postgresql
spring-boot
testing
testcontainers
or ask your own question.

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Not the answer you're looking for? Browse other questions tagged javapostgresqlspring-boottestingtestcontainers or ask your own question.

Related

Not the answer you're looking for? Browse other questions tagged
java
postgresql
spring-boot
testing
testcontainers
or ask your own question.