In the integration test, I am trying to save an entry in a table of the TestContainers Postgres database using SpringData repository.
But I get the error:
org.postgresql.util.PSQLException: ERROR: column "status" is of type custom_status but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
This is because the Proposal table has a custom type which is an enum. In that case, I am able to save the data successfully when using the real postgres database with postgres driver. But when I am connecting to the TestContainers database with org.testcontainers.jdbc.ContainerDatabaseDriver then I get the error.
How can I handle these custom types when using TestContainers in the integration tests?
If I remove the status column from the entity class and sql script then the integration tests work fine with TestContainers Postgres database.
GitHub link:- https://github.com/firstpostt/testcontainers
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)
}
}
application-it-test.yml:
spring:
datasource:
driver-class-name: org.testcontainers.jdbc.ContainerDatabaseDriver
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
locations: 'classpath:db/migration/postgresql'
jpa:
show-sql: true
database-platform: org.hibernate.dialect.PostgreSQLDialect
SQL script:
create type custom_status as enum ('STARTED', 'ENDED');
create table proposal (
proposal_id varchar(50),
amount decimal not null ,
status custom_status not null
);
Entity:
import com.example.demo.CustomStatus
import java.math.BigDecimal
import javax.persistence.*
@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 = "status")
@Enumerated(EnumType.STRING)
val status: CustomStatus
)
Enum:
enum class CustomStatus(val stringValue: String) {
STARTED("000"), ENDED("999")
}
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
import java.math.BigDecimal
@RestController
class HelloRestController @Autowired constructor(val proposalRepository: ProposalRepository) {
@GetMapping("/hello")
fun hello(): ResponseEntity<Proposal>{
var result = proposalRepository.findByIdOrNull("123")
if (result == null) {
result = proposalRepository.save(Proposal("999", BigDecimal.TEN, CustomStatus.STARTED))
}
return ResponseEntity.ok(result)
}
}
Spring data repository class:
import com.example.demo.entity.Proposal
import org.springframework.data.repository.CrudRepository
interface ProposalRepository : CrudRepository<Proposal, String>
application-local.yml which is used for Real database scenario:
spring:
datasource:
driver-class-name: org.postgresql.Driver
type: com.zaxxer.hikari.HikariDataSource
url: jdbc:postgresql://localhost/demo?stringtype=unspecified
username: demo
password: 'demo'
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
