Kotlin Micronaut Data with r2dbc fails while using Postgres jsonb type

Question

I am trying to set up Micronaut Data r2dbc with Postgres but it fails with


    io.r2dbc.postgresql.ExceptionFactory$PostgresqlBadGrammarException: [42804] column blob is of type jsonb but expression is of type character varying

micronautVersion=3.5.1 kotlinVersion=1.6.10 javaVersion=17

build.gradle.kts


    val coroutinesVersion = "1.6.0"
    dependencies {
        kapt("io.micronaut.data:micronaut-data-processor")
        // kapt("io.micronaut:micronaut-http-validation")
        // implementation("io.micronaut:micronaut-http-client")
        implementation("io.micronaut:micronaut-jackson-databind")
        implementation("io.micronaut.data:micronaut-data-r2dbc")
        implementation("io.micronaut.kotlin:micronaut-kotlin-runtime")
        implementation("jakarta.annotation:jakarta.annotation-api")
        implementation("org.jetbrains.kotlin:kotlin-reflect:${kotlinVersion}")
        implementation("org.jetbrains.kotlin:kotlin-stdlib-jdk8:${kotlinVersion}")
        implementation("org.jetbrains.kotlinx:kotlinx-coroutines-reactive:${coroutinesVersion}")
        implementation("org.jetbrains.kotlinx:kotlinx-coroutines-jdk8:${coroutinesVersion}")
        runtimeOnly("ch.qos.logback:logback-classic")
        runtimeOnly("org.postgresql:r2dbc-postgresql")
        implementation("io.micronaut:micronaut-validation")
        implementation("io.micronaut:micronaut-runtime")
        implementation("io.micronaut:micronaut-jackson-databind")
        runtimeOnly("com.fasterxml.jackson.module:jackson-module-kotlin")
    }

Postgres schema


    CREATE TABLE entity (
        entity_id          TEXT PRIMARY KEY,
        entity_type        TEXT NOT NULL,
        blob               JSONB NOT NULL,
        CREATED_DATE       TIMESTAMP(3) DEFAULT now() NOT NULL
    );

Corresponding Kotlin data class


    @MappedEntity
    data class Entity(
      @field:Id val entityId: String,
      val entityType: String,
      @TypeDef(type = DataType.JSON) val blob: Any,
      val createdDate: Instant
    )

The test that fails


    @Test
    fun testInsert() = runBlocking {
        val now = Instant.now()
        val entity = Entity("entity-uuid-1", "test-entity-type", "{}", now)
        Assertions.assertEquals(entity, entityRepository.save(entity))
    }

The complete project is here

It looks like either it's missing a required dependency to convert string to json or need a custom converter for this. Can someone please help.

jay · Accepted Answer · 2022-06-20 15:50:05Z

1

I got this working by using

    implementation("io.r2dbc:r2dbc-postgresql:0.8.12.RELEASE")

instead of

    implementation("org.postgresql:r2dbc-postgresql")

The data class was updated to use io.r2dbc.postgresql.codec.Json instead of Any

    @MappedEntity
    data class Entity(
      @field:Id val entityId: String,
      val entityType: String,
      @TypeDef(type = DataType.JSON) val blob: Json,
      val createdDate: Instant
    )

answered Jun 20, 2022 at 15:50

jay

433 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Denis · Accepted Answer · 2022-06-25 08:12:02Z

1

EDIT: It looks like I was mistaken and the actual problem is with the mapping:

Instead of:

@TypeDef(type = DataType.JSON)

It should be:

@field:TypeDef(type = DataType.JSON)

It looks like R2DBC Postgres driver doesn’t allow a String to be used for JSONB, which is strange because JDBC is fine. You might want to try creating AttributeConverter to convert it into byte[].

edited Jun 25, 2022 at 8:12

answered Jun 12, 2022 at 6:04

Denis

6134 silver badges8 bronze badges

2 Comments

jay Over a year ago

Are you referring to any documentation that says R2DBC Postgres driver doesn't allow String to be used for JSONB?

Denis Over a year ago

No, but it’s obvious from the error. You might want to submit an issue at the Postgres R2dbc driver.

Collectives™ on Stack Overflow

Kotlin Micronaut Data with r2dbc fails while using Postgres jsonb type

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related