Database Migration Tools and Vendor Neutrality

Enterprises commonly standardise on a database vendor.

Vibe coding a new product, we started off with a raw PL/SQL schema, while using Liquibase as the database migration / schema change management tool.

‘we’ here is a diad: me and an LLM

I decided to switch to PostgreSQL for development, and had to migrate to an XML defined schema.

Still ended up with CLOB in column type definitions, as we originally defined the schema for Oracle, causing a mismatch between Hibernate and Postgres.

Liquibase converted the CLOB to TEXT type for Postgres (as it doesn’t support CLOB), and TEXT is the idiomatic database type for unlimited-length strings, which translates to VARCHAR for Hibernate.

The problem is the Hibernate/JPA entity field being annotated with @Lob, which leads it to expect the JDBC type OID (for CLOB) in the database.

Changing this to @Column(name = "<>", columnDefinition = "text") would tell Hibernate to expect the logical type TEXT, instead of Clob in the database. But Oracle does not have a TEXT type, so we would lose our neutrality again.

So instead of a DB-specific DDL hint using columnDefinition, we need a Hibernate-level abstraction.

@JdbcTypeCode(SqlTypes.LONGVARCHAR)
@Column(name = "metadata_json")
private String metadataJson;

This lets us continue having Clob in the Liquibase definition, while supporting Postgres and Oracle at the same time.

Liquibase decides the physical column type.
Hibernate decides how Java talks to that column.

So Oracle will get a CLOB, and MySQL a LONGTEXT, and Postgres a TEXT with the same definition.