Hibernate automatic SQL migrate script creation on schema updates for use with flyway

Question

We have a Java Spring Boot application, using hibernate and a PostgreSQL database and would like to use flyway for versioning. We already figured out how to use flyway itself and configure hibernate to create sql files, containing the databse setup. We didn't figure out yet, how to make hibernate create minimal sql files describing the diff between the old and the new schema, which would be used as a migration script. On top of it, we did not figure out if there is a way to automatize the whole migration process.

We read through numerous documentations and stackoverflow questions. None of them quite explained how to set up our requested behavior. This question was closest to what we want to achieve, but explaining that it is not possible to accomplish such behavior with flyway + hibernate. Allthough the question is quite old (3 years), we had hopes that it might work now?

Our current spring config regarding hibernate looks as following:

spring:
  jpa:
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQL95Dialect
        hbm2ddl:
          delimiter: ;
      javax:
        persistence:
          schema-generation:
            create-source: metadata
            create-database-schemas: true
            scripts:
              action: create
              create-target: src/main/resources/ddl_hibernate_creation.sql

Which produces a schema creation sql just fine. Though this is not sufficent when upgrading the schema.

As previously explained, we look out to find a way to at least semi automatize the whole migration process (hibernate should at least create a proper "diff" file which can be applied to production/development by hand).

Hemslo Wang Hemslo Wang · Accepted Answer · 2019-09-16T12:22:44

You can use update for you scripts action to generate a diff script. It will compare the schema generated from code with the one in the database, so make sure to apply all existing migrations before generate new ones. Also, it won't detect all the changes, so still need to do reviews yourself. Better to also generate the create script to check the diff.

To make it semi-auto, you can put this config in a different profile. Run with the new profile to generate the migration. Copy the script to your expected place.

Sample config:

spring:
  profiles: migration
  jpa:
    properties:
      javax:
        persistence:
          schema-generation:
            create-source: metadata
            scripts:
              action: update
              create-target: src/main/resources/ddl_hibernate_migration.sql

Hibernate automatic SQL migrate script creation on schema updates for use with flyway

1 Answers