Automatic generation of migration SQL for Flyway

Question

Is it possible for new Flyway migrations to be generated by JPA/Hibernate's automatic schema generation when a new model / field etc. are added via Java code.

It would be useful to capture the auto-generated SQL and save it directly to a new Flyway migration, for review / editing / committing to a project repository.

Thank you in advance for any assistance or enlightenment you can offer.

Some relevant discussions here; http://stackoverflow.com/questions/18536256/combine-hibernates-automatic-schema-creation-and-database-versioning — JamieB, Apr 06 '16 at 07:58
Thank you @JamieB, we ended up just dealing with it as a chore. Crafting the SQL for each migration by hand. Still evaluation between flyway and maybe Liquibase. — ocodo, Apr 06 '16 at 08:05

score 18 · Answer 1 · answered Jun 11 '21 at 10:47

18

If your IDE of choice is IntelliJ IDEA, I'd recommend using the JPA Buddy plugin to do this. It can generate Flyway migrations by comparing your Java model to the target DB.

You can use it to keep your evolving model and your SQL scripts in sync. Also, it can create the init script if your DB is empty.

Once you have it installed and have Flyway as your Maven/Gradle dependency, you can generate a migration like this:

answered Jun 11 '21 at 10:47

Andrey Oganesyan

497
5
6

plus one! which tool do you use for GIF screen recording? – shashwat May 19 '23 at 03:06

score 7 · Accepted Answer · answered Nov 06 '18 at 18:09

7

Flyway doesn't have built-in support for diff, I use liquidbase within a maven spring boot project and changelogs can be created from JPA/hibernate changes by using:

mvn liquibase:diff

All of the options for liquibase diff are located here: http://www.liquibase.org/documentation/maven/maven_diff.html

answered Nov 06 '18 at 18:09

danzdoran

281
2
7

Thanks this is a what I ended up doing. Honestly I'd forgotten about this question. – ocodo Nov 10 '18 at 10:29

Haroldo_OK · Answer 3 · 2022-09-26T14:16:52.120

If you want to generate the update SQL automatically, you can ask Hibernate to do so; just add the lines below to your Spring Boot configuration:

spring.jpa.properties.javax.persistence.schema-generation.create-source=metadata
spring.jpa.properties.javax.persistence.schema-generation.scripts.action=update
spring.jpa.properties.javax.persistence.schema-generation.scripts.create-target=update.sql

When you execute the application, this will generate a file named update.sql on the root of your project. Now, you can just copy and paste them into your Flyway migration.

This was adapted from this other answer: https://stackoverflow.com/a/36966419/679240 ; it is basically the same logic, except that one wants to generate a database creation script, while I needed an update script, instead.

BTW, if you want to replace the names of the foreign keys on the script with more readable ones, you could use this regex: ^(alter table .*?)(\w+)(\s+add constraint )\w+( foreign key $)(.*?)($.*) with this replacement: $1$2$3fk_$2__$5$4$5$6; this will change the names of the FKs in the script to fk_name_of_the_table__name_of_the_field.

Automatic generation of migration SQL for Flyway

3 Answers3

Linked