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).

score 2 · Answer 1 · answered Sep 16 '19 at 12:22

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

Thanks for your answer! I would accept it, but as we changed over to liquibase we don't need flyway anymore and thus I can't verify this solution :/ In case this answer helps others which can confirm it works, I'll happily accept this answer :) — Migsi, Sep 18 '19 at 09:19

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

1 Answers1