Question 395506 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Adding Data to a testcontainer using a function from a repository

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    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);
    }

  2. 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…)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search