how to use EnumCodec in r2dbc-postgresql

Question

I am trying to use EnumCodec from the latest version of r2dbc-postgresql (0.8.4) unsuccessfully, and I wondered if you could help me.

I use also spring-data-r2dbc version 1.1.1.

I took the exact example from the GitHub and created an enum type “my_enum” in my Postgres, and a table “sample_table” which contains ‘name’ (text) and ‘value’ (my_enum).

Then I did as in the example:

SQL:

CREATE TYPE my_enum AS ENUM ('FIRST', 'SECOND');

Java Model:

enum MyEnumType {
  FIRST, SECOND;
}

Codec Registration:

PostgresqlConnectionConfiguration.builder()
.codecRegistrar(EnumCodec.builder().withEnum("my_enum", MyEnumType.class).build());

I use DatabaseClient in order to communicate with the DB. I tried to insert using 2 methods:

databaseClient.insert().into(SampleTable.class)
.using(sampleTable).fetch().rowsUpdated();

or:

databaseClient.insert().into("sample_table")
.value("name", sampleTable.getName())
.value("value", sampleTable.getValue())
.then();

where SampleTable is:

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Table("sample_table")
@JsonIgnoreProperties(ignoreUnknown = true)
@JsonInclude(JsonInclude.Include.NON_NULL)
public class SampleTable implements Serializable {
   private String name;
   @Column("value")
   @JsonProperty("value")
   private MyEnumType value;
}

But I get the same error using both:

column "value" is of type my_enum but expression is of type character varying

Can you please help me understand what I did wrong, or refer me to some working example? I appreciate your help!

Answer 1

Spring Data considers enum values as values to be converted to String by default. You need to register a Converter that retains the type by writing the enum-type as-is.

@WritingConverter
class MyEnumTypeConverter implements Converter<MyEnumType, MyEnumType> {
    @Override
    public MyEnumType convert(MyEnumType source) {
        return source;
    }
}

Next, you need to register the converter. If you're using Spring Data R2DBC's AbstractR2dbcConfiguration, then override getCustomConverters():

class MyConfiguration extends AbstractR2dbcConfiguration {

    @Override
    protected List<Object> getCustomConverters() {
        return Collections.singletonList(new MyEnumTypeConverter());
    }

  // …
}

Alternatively, if you configure DatabaseClient standalone, then you need a bit more of code:

PostgresqlConnectionConfiguration configuration = PostgresqlConnectionConfiguration.builder()
        .codecRegistrar(EnumCodec.builder().withEnum("my_enum", MyEnumType.class).build())
        .host(…)
        .username(…)
        .password(…)
        .database(…).build();

R2dbcDialect dialect = PostgresDialect.INSTANCE;
DefaultReactiveDataAccessStrategy strategy = new DefaultReactiveDataAccessStrategy(dialect, Collections.singletonList(new MyEnumTypeConverter()));

DatabaseClient databaseClient = DatabaseClient.builder()
        .connectionFactory(new PostgresqlConnectionFactory(configuration))
        .dataAccessStrategy(strategy)
        .build();
    

However, there are two bugs in the R2DBC driver that prevent Spring Data from working as expected:

• Row.decode(…) fails for enum type with IllegalArgumentException: 72093 is not a valid object id #301
• EnumCodec decoding fails if the requested value type is Object #302

As temporary workaround, you can duplicate EnumCodec in your codebase and apply the fix from #302 until a new release of R2DBC Postgres is available.

Answer 2

I have tried to use pg enum type and Java Enum class in my sample projects.

1. If you are using DatabaseClient API(in Spring 5.3 core, not use Spring Data R2dbc), register an EnumCodec in PostgresConnectionFactory is enough.

   Check my exmaple.

2. If creating a pg type enum as a column type in the table schema, and Register an EnumCodec in the PostgresConnectionFactory.builder. You need to write custom @ReadingConverter to read the custom enum.

   Check my example here.

3. If you use text-based type(varchar) in the table schema with Java Enum. no need for the extra effort on conversion, check my example here.

The Spring Data R2dbc said if using the driver built-in mechanism to handle enum, you have to register an EnumWriteSupport. But according to my experience, when using Spring Data R2dbc, the write can be handled automatically, but reading converter is required to read enum from Postgres.