How to use hibernate-types JsonStringType with MariaDB?

Question

In a previous project we used the hibernate-types JsonStringType successfully with MySQL 5.7.21. This works (in Kotlin):

@TypeDefs(
    TypeDef(name = "JSON", typeClass = JsonStringType::class)
)
class SomeEntity {
    // ...

    @Type(type = "JSON")
    @Column(nullable = false, columnDefinition = "JSON")
    var data: SomeData = SomeData()
}

In a new project, we want to do the same, but with MariaDB 10.5.4. With MariaDB, we get the following error on schema validation with hbm2ddl=validate:

SchemaManagementException: Schema-validation: wrong column type encountered in column [data] in table [some_entity]; found [longtext (Types#LONGVARCHAR)], but expecting [json (Types#OTHER)]".

As far as I see, hibernate-types doesn't officially support MariaDB. In MariaDB, JSON is just an alias for LONGTEXT with a CHECK (JSON_VALID(column)) constraint.

It works if we use columnDefinition = "LONGTEXT" instead of columnDefinition = "JSON", however, then the CHECK constraint won't be generated with hbm2ddl=create.

Does anyone have an idea how to get the the best of both worlds? I.e. create the check constraint and pass schema validation?

Answer 1

It works if we use columnDefinition = "LONGTEXT" instead of columnDefinition = "JSON", however, then the CHECK constraint won't be generated with hbm2ddl=create.

The hbm2ddl tool is fine to generate an initial migration script that you can manually adjust prior to using it for the very first time.

Afterward, you should use Flyway with migration scripts.

Now, the JsonStringType should use Types.VARCHAR, as indicated by JsonStringSqlTypeDescriptor.

If it doesn't work, it might be because the MariaDB Driver maps the JSON column type to LONGVARCHAR, behind the scenes.