R2DBC and enum (PostgreSQL)

Question

Update 15/08/2020: Looks like Enum support was added on Jun 16. R2DBC commit.

Does H2DBC support PostgreSQL enums? I checked they git page but it doesn't mention anything about it. If it does, how enums could be used (INSERT, SELECT)?
Lets say PostgreSQL enum

CREATE TYPE mood AS ENUM ('UNKNOWN', 'HAPPY', 'SAD', ...);

Java class

@Data
public class Person {
    private String name;
    private Mood mood;
    // ...

    enum Mood{ UNKNOWN, HAPPY, SAD, ...}
}

I tried:

        // insert
        var person = ...;
        client.insert()
                .table("people")
                .using(person)
                .then()
                .subscribe(System.out::println);

        // select
        var query = "SELECT * FROM people";
        client.execute(query)
                .as(Person.class)
                .fetch().all()
                .subscribe(System.out::println);

But I'm getting error messages:

# on insert
 WARN [reactor-tcp-epoll-1] (Loggers.java:294) - Error: SEVERITY_LOCALIZED=ERROR, SEVERITY_NON_LOCALIZED=ERROR, CODE=42804, MESSAGE=column "mood" is of type mood but expression is of type character varying, HINT=You will need to rewrite or cast the expression., POSITION=61, FILE=parse_target.c, LINE=591, ROUTINE=transformAssignedExpr
# on select
ERROR [reactor-tcp-epoll-1] (Loggers.java:319) - [id: 0x8581acdb, L:/127.0.0.1:39726 ! R:127.0.0.1/127.0.0.1:5432] Error was received while reading the incoming data. The connection will be closed.
reactor.core.Exceptions$ErrorCallbackNotImplemented: org.springframework.data.mapping.MappingException: Could not read property private ...

I found similar post but without luck to solve my problem.. maybe I was applying it wrong..
Any help or tips are welcome.

Answer 1

Tested with org.springframework.data:spring-data-r2dbc:1.0.0.RELEASE and io.r2dbc:r2dbc-postgresql:0.8.1.RELEASE.

Kotlin version.

1. Define a enum class

   enum class Mood {
       UNKNOWN,
       HAPPY,
       SAD
   }
   

2. Create a custom codec

   class MoodCodec(private val allocator: ByteBufAllocator) :  Codec<Mood> {
       override fun canEncodeNull(type: Class<*>): Boolean = false
   
       override fun canEncode(value: Any): Boolean = value is Mood
   
       override fun encode(value: Any): Parameter {
           return Parameter(Format.FORMAT_TEXT, oid) {
               ByteBufUtils.encode(allocator, (value as Mood).name)
           }
       }
   
       override fun canDecode(dataType: Int, format: Format, type: Class<*>): Boolean = dataType == oid
   
       override fun decode(buffer: ByteBuf?, dataType: Int, format: Format, type: Class<out Mood>): Mood? {
           buffer ?: return null
           return Mood.valueOf(ByteBufUtils.decode(buffer))
       }
   
       override fun type(): Class<*> = Mood::class.java
   
       override fun encodeNull(): Parameter =
           Parameter(Format.FORMAT_TEXT, oid, Parameter.NULL_VALUE)
   
       companion object {
           // Get form `select oid from pg_type where typname = 'mood'`
           private const val oid = YOUR_ENUM_OID
       }
   }
   

3. Registe the codec

   You may need change runtimeOnly("io.r2dbc:r2dbc-postgresql") to implementation("io.r2dbc:r2dbc-postgresql")

   @Configuration
   @EnableR2dbcRepositories
   class AppConfig : AbstractR2dbcConfiguration() {
       override fun connectionFactory(): ConnectionFactory = PostgresqlConnectionConfiguration.builder()
           .port(5432) // Add your config here.
           .codecRegistrar { _, allocator, registry ->
               registry.addFirst(MoodCodec(allocator))
               Mono.empty()
           }.build()
           .let { PostgresqlConnectionFactory(it) }
   }
   

Answer 2

I used the below for Spring boot 2.6.4 + r2dbc-postgresql 0.8.11 by adding a customizer rather than creating the connection factory myself.

Thanks @Hantsy for pointing EnumCodec out. I added it to a customizer therefore it can play nicely with existing autoconfigure procedure. Also, the spring-data keeps converting my enum to string until I added the converter.

Hopefully these can provide a little help to others.

1. Register EnumCodec to builder customizer as extensions

   It is possible to register multiple enum, just repeat the withEnum() call.

  /**
   * Use the customizer to add EnumCodec to R2DBC
   */
  @Bean
  public ConnectionFactoryOptionsBuilderCustomizer connectionFactoryOptionsBuilderCustomizer() {
    return builder -> {
      builder.option(Option.valueOf("extensions"),
                     List.of(EnumCodec.builder()
                       .withEnum("enum_foo", FooEnum.class)
                       .withRegistrationPriority(RegistrationPriority.FIRST)
                       .build()));

      logger.info("Adding enum to R2DBC postgresql extensions: {}", builder);
    };
  }

2. Implement spring data converter by extending EnumWriteSupport

public class FooWritingConverter extends EnumWriteSupport<Foo> {
}

3. Register converters so that spring data won't always convert enum to string.

   This step is a slightly enhanced version of R2dbcDataAutoConfiguration in spring-boot-autoconfigure project.

  /**
   * Register converter to make sure Spring data treat enum correctly
   */
  @Bean
  public R2dbcCustomConversions r2dbcCustomConversions(DatabaseClient databaseClient) {
    logger.info("Apply R2DBC custom conversions");
    R2dbcDialect dialect = DialectResolver.getDialect(databaseClient.getConnectionFactory());
    List<Object> converters = new ArrayList<>(dialect.getConverters());
    converters.addAll(R2dbcCustomConversions.STORE_CONVERTERS);
    return new R2dbcCustomConversions(
      CustomConversions.StoreConversions.of(dialect.getSimpleTypeHolder(), converters),
      List.of(
        new FooWritingConverter()
      ));
  }

Step 1 and 3 can be added to your application class or any other valid configuration.

Answer 3

Check my article about Postgres specific features supported in R2dbc.

There are two options.

1. use custom Postgres enum type and Java enum type, and register EnumCodec in the connection factory builder.

2. use a textual type as data type(such as varchar), and Java Enum type, Spring data r2dbc will convert them directly.