How to extends PostgreSQL dialect to add support for some custom column type? No type mapping for org.hibernate.type.SqlTypes code: 1111 (OTHER)

I am writing integration tests and encountered with a problem. When I launch the app, everything works fine. However, when running the tests, I get an exception:

org.hibernate.MappingException: Unable to determine SQL type name for column 'id' of table 'some_table' because there is no type mapping for org.hibernate.type.SqlTypes code: 1111 (OTHER)

Some entity uses the following mapping for identifiers:

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", unique = true, nullable = false)
@Access(AccessType.PROPERTY)
@Type(value = SomeUUIDType.class, parameters = @Parameter(name = "column", value = "id"))
private UUID id;

Class SomeUUIDType just extends MutableType from Hypersistence and was added for some reasons (I don't know why - it's just there):

import io.hypersistence.utils.hibernate.type.MutableType;
import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.internal.util.IndexedConsumer;
import org.hibernate.metamodel.mapping.JdbcMapping;
import org.hibernate.type.descriptor.java.UUIDJavaType;
import org.hibernate.type.descriptor.jdbc.UUIDJdbcType;
import org.hibernate.usertype.EnhancedUserType;
import org.hibernate.usertype.ParameterizedType;

import java.util.Properties;
import java.util.UUID;

public class SomeUUIDType extends MutableType<UUID, UUIDJdbcType, UUIDJavaType> implements ParameterizedType, EnhancedUserType<UUID> {

    public SomeUUIDType() {
        super(UUID.class, UUIDJdbcType.INSTANCE, UUIDJavaType.INSTANCE);
    }

    @Override
    public UUID fromStringValue(CharSequence seq) throws HibernateException {
        return UUID.fromString(seq.toString());
    }

    @Override
    public <X, Y> int forEachDisassembledJdbcValue(
            Object value,
            int offset,
            X x,
            Y y,
            JdbcValuesBiConsumer<X, Y> consumer,
            SharedSessionContractImplementor session
    ) {
        return 0;
    }

    @Override
    public int forEachJdbcType(int offset, IndexedConsumer<JdbcMapping> indexedConsumer) {
        return 0;
    }

    @Override
    public void setParameterValues(Properties params) {
    }

    @Override
    public String toSqlLiteral(UUID value) {
        return value.toString();
    }

    @Override
    public String toString(UUID value) throws HibernateException {
        return value.toString();
    }
}

I write some elementary tests to start with. Here I create an entity and try to save it:

@Testcontainers
@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@EntityScan(basePackages = "some.package")
@EnableAutoConfiguration
@TestPropertySource("/test-application.properties")
public class SomeTest {

    @Container
    private static final PostgreSQLContainer<?> testContainer = new PostgreSQLContainer<>("postgres:latest");

    @Autowired
    private ApplicationContext context;
    @Autowired
    private TestEntityManager em;


    @DynamicPropertySource
    private static void overrideProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", testContainer::getJdbcUrl);
        registry.add("spring.datasource.username", testContainer::getUsername);
        registry.add("spring.datasource.password", testContainer::getPassword);
        registry.add("spring.datasource.driverClassName", testContainer::getDriverClassName);
    }

    @Test
    void test() {
        assertTrue(testContainer.isCreated());
        assertTrue(testContainer.isRunning());

        assertNotNull(context);
        assertNotNull(em);

        SomeEntity entity = new SomeEntity();
        entity.setId(UUID.randomUUID());
        em.persistAndFlush(entity);
    }
}

My test-application.properties:

spring.datasource.url=
spring.datasource.driverClassName=
spring.datasource.username=
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
spring.datasource.initialization-mode=always
spring.h2.console.enabled=true
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql = true

pom.xml includes the following dependencies:

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <version>3.2.1</version>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.testcontainers</groupId>
        <artifactId>testcontainers</artifactId>
        <version>1.19.3</version>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.testcontainers</groupId>
        <artifactId>postgresql</artifactId>
        <version>1.19.3</version>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.6.0</version>
        <scope>runtime</scope>
    </dependency>

I searched for information and found that it is necessary to define a custom dialect. Something like this:

public class CustomPostgreSQLDialect extends PostgreSQLDialect {

    @Override
    protected void registerColumnTypes(TypeContributions typeContributions, ServiceRegistry serviceRegistry) {
        super.registerColumnTypes(typeContributions, serviceRegistry);
        
        DdlTypeRegistry ddlTypeRegistry = typeContributions.getTypeConfiguration().getDdlTypeRegistry();        
        ddlTypeRegistry.addDescriptor(Types.OTHER, ....);
    }
}

Then just plug it in:

spring.jpa.properties.hibernate.dialect=some.package.CustomPostgreSQLDialect

But what exactly I need to override here is unclear to me. And is that the reason?

I actually just write tests and can't change existing code. I suppose it is possible to configure a custom dialect to prevent an exception.

After looking into this, it seems like issue is related to letting Hibernate create the table. With Flyway, there are no errors.

I had some difficulties with your test class, hence I show all of my code used in tests.

src/test/resources/db/migration/V1__Create_Tables.sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE some_entity
(
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4()
);

Dependencies With newer versions of Spring Boot, use testRuntimeOnly("org.flywaydb:flyway-database-postgresql")

dependencies {
    implementation("org.springframework.boot:spring-boot-starter-data-jpa")
    implementation("io.hypersistence:hypersistence-utils-hibernate-63:3.9.0")
    testRuntimeOnly("org.postgresql:postgresql")
    testRuntimeOnly("org.junit.platform:junit-platform-launcher")
    testImplementation("org.flywaydb:flyway-core:9.0.0")
    testImplementation("org.springframework.boot:spring-boot-starter-test")
    testImplementation("org.springframework.boot:spring-boot-testcontainers")
    testImplementation("org.testcontainers:postgresql")
}

application-test.yml

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/test_db
    username: test
    password: test
    driver-class-name: org.postgresql.Driver

  jpa:
    hibernate.ddl-auto: none
    show-sql: true

  flyway:
    enabled: true
    locations: classpath:db/migration

PostgresTestContainerConfig

import org.springframework.boot.test.context.TestConfiguration;
import org.springframework.boot.testcontainers.service.connection.ServiceConnection;
import org.springframework.context.annotation.Bean;
import org.testcontainers.containers.PostgreSQLContainer;

@TestConfiguration
public class PostgresTestContainerConfig {

    @Bean
    @ServiceConnection
    public PostgreSQLContainer<?> postgreSQLContainer() {
        return new PostgreSQLContainer<>("postgres:15-alpine");
    }
}

SomeEntityRepository

import org.springframework.data.repository.CrudRepository;

import java.util.UUID;

public interface SomeEntityRepository extends CrudRepository<SomeEntity, UUID> { }

SomeTest

replace = AutoConfigureTestDatabase.Replace.NONE can be removed with Spring Boot 3.4.0.

import no.myapp.database.PostgresTestContainerConfig;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.boot.test.autoconfigure.orm.jpa.TestEntityManager;
import org.springframework.context.annotation.Import;

import static org.junit.jupiter.api.Assertions.assertEquals;

@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@Import(PostgresTestContainerConfig.class)
public class SomeTest {

    @Autowired
    TestEntityManager em;

    @Autowired
    SomeEntityRepository sut;

    @Test
    void testSave() {
        SomeEntity someEntity = new SomeEntity();
        sut.save(someEntity);

        assertEquals(1, getItemCountInDb());
    }

    long getItemCountInDb() {
        return (long) em.getEntityManager()
                .createQuery("SELECT COUNT(1) FROM SomeEntity")
                .getSingleResult();
    }
}