Prevent JPA from changing DB column data type

Question

I have a PostgreSQL DB and I use flyway in the spring boot application to create the following table and enum:

CREATE EXTENSION citext;

CREATE TYPE metadata_type AS ENUM ('STRING', 'DATE', 'INTEGER', 'DOUBLE');

CREATE TABLE metadata
(
  id         BIGSERIAL PRIMARY KEY,
  name       CITEXT        NOT NULL,
  type       metadata_type NOT NULL,
  created_at TIMESTAMP     NOT NULL,
  updated_at TIMESTAMP     NOT NULL
);

ALTER TABLE metadata
ADD CONSTRAINT metadata_unique_name UNIQUE (name);

After flyway runs and the table is created, the table definition looks as follows:

Then I create the JPA entity for this table:

@Entity
public class Metadata {
  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE)
  private Long id;

  @Column(nullable = false, unique = true)
  private String name;

  @Column(nullable = false)
  @Enumerated(EnumType.STRING)
  private MetadataType type;

  @CreatedDate
  @Column(updatable = false)
  private LocalDateTime createdAt;

  @LastModifiedDate
  private LocalDateTime updatedAt;
}

And after I start the application, the table definition changes to the following:

As you can see, the data types of the columns "name" and "type" both change to varchar. I can live with the enum changing to varchar. But CITEXT must not change to varchar! That breaks the uniqueness constraint, as now both "test" and "Test" can be used simultaneously.

How can I prevent JPA from changing the data types? Or what class should I use instead of String for CITEXT?

My workaround is to use varchar instead of CITEXT and create an index as follows:

CREATE UNIQUE INDEX metadata_idx_unique_name ON metadata (LOWER(name));

But maybe there is a way to use CITEXT.

Answer 1

I've also encountered this problem, here is what helped me:

1. Disable JPA schema management

Since you're already using Flyway for schema management, you don't need Hibernate to alter the database structure. You can tell JPA to leave the schema alone by setting this property in your application.properties:

spring.jpa.hibernate.ddl-auto=none

This will prevent Hibernate from trying to modify the schema when your application starts.

2. Use schema validation instead of update

If you still want JPA to check if the schema is correct but not modify it, you can set the schema generation strategy to validate. This way, Hibernate will verify the schema matches the entity definitions without trying to change anything:

spring.jpa.hibernate.ddl-auto=validate