[FIXED] Table not found in integration tests when using TestContainers with Flyway

Issue

I am trying to connect to Postgres database instance of TestContainers but I get the error org.postgresql.util.PSQLException: ERROR: relation "proposal" does not exist in Spring data repository class "ProposalRepository" when running the "HelloRestControllerTest"

The flyway is enabled in the config.

I created a demo project with just the basic stuff.

HelloRestController:

import com.example.demo.entity.Proposal
import com.example.demo.repo.ProposalRepository
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.data.repository.findByIdOrNull
import org.springframework.http.ResponseEntity
import org.springframework.web.bind.annotation.GetMapping
import org.springframework.web.bind.annotation.RestController

@RestController
class HelloRestController @Autowired constructor(val proposalRepository: ProposalRepository) {

    @GetMapping("/hello")
    fun hello(): ResponseEntity<Proposal>{
        val result = proposalRepository.findByIdOrNull("123") //error is thrown here.
        return ResponseEntity.ok(result)
    }
}

ProposalRepository:

import com.example.demo.entity.Proposal
import org.springframework.data.repository.CrudRepository

interface ProposalRepository : CrudRepository<Proposal, String>

Entity:

import java.math.BigDecimal
import javax.persistence.Column
import javax.persistence.Entity
import javax.persistence.Table
import javax.persistence.Id

@Entity
@Table(name = "proposal")
data class Proposal(

  @Id
  @Column(name = "proposal_id")
  val proposalId: String,

  @Column(name = "amount", nullable = true)
  val amount: BigDecimal?,

  @Column(name = "user_id")
  val userId: String? = null
)

Flyway SQL script:

create table proposal (
      proposal_id                           varchar(50),
      amount                                decimal not null ,
      user_id                               varchar(35)
);

application-it-test.yml in src/test/resources folder:

spring:
  datasource:
    #driver-class-name: org.postgresql.Driver
    type: com.zaxxer.hikari.HikariDataSource
    url: jdbc:tc:postgresql:11.6:///databasename
    hikari:
      max-lifetime: 500000
      connection-timeout: 300000
      idle-timeout: 600000
      maximum-pool-size: 5
      minimum-idle: 1
  flyway:
    enabled: true
    url: ${spring.datasource.url}
    locations: 'classpath:db/migration/postgresql'
    table: FLY_VERSION
  jpa:
    show-sql: true
    database-platform: org.hibernate.dialect.PostgreSQLDialect

pom.xml:

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>11</java.version>
        <kotlin.version>1.6.21</kotlin.version>
        <spring-cloud.version>2021.0.3</spring-cloud.version>
        <testcontainers.version>1.17.3</testcontainers.version>
    </properties>
    <dependencies>
        <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>com.fasterxml.jackson.module</groupId>
            <artifactId>jackson-module-kotlin</artifactId>
        </dependency>
        <dependency>
            <groupId>org.jetbrains.kotlin</groupId>
            <artifactId>kotlin-reflect</artifactId>
        </dependency>
        <dependency>
            <groupId>org.jetbrains.kotlin</groupId>
            <artifactId>kotlin-stdlib-jdk8</artifactId>
        </dependency>

        <dependency>
            <groupId>io.rest-assured</groupId>
            <artifactId>json-path</artifactId>
            <scope>test</scope>
            <exclusions>
                <!-- excluded because REST assured 4.3.0+ expects Groovy 3+ (https://github.com/rest-assured/rest-assured/issues/1283) -->
                <exclusion>
                    <groupId>org.codehaus.groovy</groupId>
                    <artifactId>groovy-json</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>io.rest-assured</groupId>
            <artifactId>rest-assured</artifactId>
            <scope>test</scope>
            <exclusions>
                <!-- excluded because REST assured 4.3.0+ expects Groovy 3+ (https://github.com/rest-assured/rest-assured/issues/1283) -->
                <exclusion>
                    <groupId>org.codehaus.groovy</groupId>
                    <artifactId>groovy</artifactId>
                </exclusion>
                <!-- excluded because REST assured 4.3.0+ expects Groovy 3+ (https://github.com/rest-assured/rest-assured/issues/1283) -->
                <exclusion>
                    <groupId>org.codehaus.groovy</groupId>
                    <artifactId>groovy-xml</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-contract-stub-runner</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>junit-jupiter</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>postgresql</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-dependencies</artifactId>
                <version>${spring-cloud.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
            <dependency>
                <groupId>org.testcontainers</groupId>
                <artifactId>testcontainers-bom</artifactId>
                <version>${testcontainers.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <sourceDirectory>${project.basedir}/src/main/kotlin</sourceDirectory>
        <testSourceDirectory>${project.basedir}/src/test/kotlin</testSourceDirectory>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>org.jetbrains.kotlin</groupId>
                <artifactId>kotlin-maven-plugin</artifactId>
                <configuration>
                    <args>
                        <arg>-Xjsr305=strict</arg>
                    </args>
                    <compilerPlugins>
                        <plugin>spring</plugin>
                        <plugin>jpa</plugin>
                    </compilerPlugins>
                </configuration>
                <dependencies>
                    <dependency>
                        <groupId>org.jetbrains.kotlin</groupId>
                        <artifactId>kotlin-maven-allopen</artifactId>
                        <version>${kotlin.version}</version>
                    </dependency>
                    <dependency>
                        <groupId>org.jetbrains.kotlin</groupId>
                        <artifactId>kotlin-maven-noarg</artifactId>
                        <version>${kotlin.version}</version>
                    </dependency>
                </dependencies>
            </plugin>
        </plugins>
    </build>

</project>

HelloRestControllerTest:

import io.restassured.RestAssured.given
import io.restassured.http.ContentType
import org.junit.jupiter.api.Test
import org.junit.runner.RunWith
import org.springframework.boot.test.context.SpringBootTest
import org.springframework.boot.test.web.server.LocalServerPort
import org.springframework.cloud.contract.wiremock.AutoConfigureWireMock
import org.springframework.test.context.ActiveProfiles
import org.springframework.test.context.DynamicPropertyRegistry
import org.springframework.test.context.DynamicPropertySource
import org.springframework.test.context.junit4.SpringRunner
import org.testcontainers.containers.PostgreSQLContainer
import org.testcontainers.junit.jupiter.Container
import org.testcontainers.junit.jupiter.Testcontainers

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@ActiveProfiles("it-test")
@AutoConfigureWireMock(port = 1234)
@RunWith(SpringRunner::class)
@Testcontainers
class HelloRestControllerTest {
    @LocalServerPort
    final val portNumber = 0

    val baseUrl = "http://localhost:$portNumber"


    companion object {
        @Container
        var postgreSQL: PostgreSQLContainer<*> = PostgreSQLContainer("postgres:11.6")

        @DynamicPropertySource
        fun postgreSQLProperties(registry: DynamicPropertyRegistry) {
            registry.add("spring.datasource.username") { postgreSQL.username }
            registry.add("spring.datasource.password") { postgreSQL.password }
        }
    }

    @Test
    fun test() {
        val helloResponse = given()
            .contentType(ContentType.JSON)
            .get("$baseUrl$portNumber/hello")
            .andReturn()
        println(helloResponse.body)
    }
}

The code and configuration looks fine so I am not able to figure out what is the problem. What could be the issue here?

EDIT:

If I add @Sql annotation in HelloRestControllerTest as shown below, I do not see any error. It looks like Flyway is not able to run the scripts to the right database or something. Not sure why?

@Sql(scripts = ["classpath:/db/migration/postgresql/V01__PROPOSAL.sql"])

Solution

change the application-it-test.yml as shown below:

spring:
  datasource:
    #driver-class-name: org.postgresql.Driver
    type: com.zaxxer.hikari.HikariDataSource
    url: jdbc:tc:postgresql:11.6:///databasename?stringtype=unspecified
    hikari:
      max-lifetime: 500000
      connection-timeout: 300000
      idle-timeout: 600000
      maximum-pool-size: 5
      minimum-idle: 1
  flyway:
    enabled: true
    locations: 'classpath:db/migration/postgresql'
  jpa:
    show-sql: true
    database-platform: org.hibernate.dialect.PostgreSQLDialect

under flyway variable in yml file you only need to mention the enable and locations values. Everything else like url, schemas, table, etc should be removed. Otherwise Flyway does not work together with TestContainers in integration test.

The change needs to be done in both the test yml file and real application's yml file as well.

Answered By - firstpostcommenter
Answer Checked By - Mary Flores (JavaFixing Volunteer)