Can Flyway or Liquibase generate an update script instead of updating the database directly?

Question

First, a little background. I have a set of Java applications, some based on JPA, some not. To create my databases I am currently using Hibernates schema export to generate create scripts for those using JPA. Those not using JPA I generate the scripts by hand. These are then run during application installation using ANT. For updates the application installer simply applies update scripts to the database.

To improve the management of database updates I have been looking at Flyway and Liquibase. Both seem to almost do what I want, (aside: I am preferring Flyway at the moment because of all the pre-existing SQL/DDL scripts we have). The issue I can see is that they both update the database directly. This is fine for a lot of installations but not all.

What I would like to do is run Flyway/Liquibase against the database and generate an update script that incorporates all the updates needed to bring the database up to date - including any changes Flyway/Liquibase needs to make to its own tables. This would allow me (or more importantly a database admin) to run the update script outside of the application(s) to update the database. I could then use Flyway/Liquibase within my application purely to verify that the database is up to date.

Is it possible to do this with Flyway or Liquibase or any other tool for that matter?

One of my favourite features of liquibase is it's ability to generate SQL scripts. I use this with paranoid DBA's who deny me access :-) For a demo see: http://stackoverflow.com/questions/8397488/comparing-databases-and-genrating-sql-script-using-liquibase/ — Mark O'Connor, Jan 23 '13 at 23:40

score 12 · Accepted Answer · answered Jan 23 '13 at 15:23

12

Liquibase handles it quite fine. It looks at your database at the current state, finds unapplied changesets and generates an SQL script with update command in sql output mode.

Using a proper database migration tool instead of Hibernate generator is the way to go in any case, sooner or later you'll end up with a situation that Hibernate does not support. For us it was dropping an unique index and replacing it with another. You can also enable hibernate.hbm2ddl.auto=validate to feel safe about the compatibility between database structure and entity beans.

answered Jan 23 '13 at 15:23

Mirko Adari

5,083
1
15
23

Thanks for that. The SQL output feature is quite clear in the documentation - not sure why I missed it. – pillingworth Jan 24 '13 at 08:43
2

Using liquibase-hibernate extension, you can generate changesets directly from changed hibernate entities. – Ondrej Bozek Jun 22 '15 at 15:03

score 7 · Answer 2 · answered Aug 12 '16 at 19:53

We had a similar problem when I worked at a consultancy (Intelliware) so the devs there put together some code and pushed it up to GitHub.

We tried unsuccessfully to get it included into the Flyway core repo.

https://github.com/Intelliware/flyway-script-generator

score 4 · Answer 3 · answered Jan 23 '13 at 15:13

What you want is a schema diff tool. I remember hearing that TOAD has a rather powerful one. Hibernate will also try to generate schema update scripts based on the entities it detects and the database metadata.

However what you need is... to not do that and instead use Flyway to make all your database changes. That is you should turn of Hibernates automatic schema updates and write the schema updates yourself going forward. Every time you want to make a change to the database you have to write a schema update.

Some people capture the SQL output of how hibernates schema update as a way of getting automated schema evolution updates. The problem is that hibernate is typically wrong especially if add a @NotNull column.

Also in terms of your admin I believe Flyway can output based on its schema_version table and the SQL/Java migration scripts the SQL output it will run thus your DBA could run it outside of Flyway (If it doesn't this would be an easy feature to add).

Just to clarify, Flyway doesn't produce a SQL output, it immediately executes against the DB. However, the Flyway Command-line tool allows you to run the same migrations outside of your app and should suit your needs just fine. — Axel Fontaine, Jan 23 '13 at 16:17
Yes but I can see why the DBA might want to see the SQL that Flyway wants to run before it runs it. In theory it seems you could make Flyway attempt make the schema changes and then rollback or just not even execute the commands... right? BTW I do love Flyway ... thank you for making it. — Adam Gent, Jan 23 '13 at 16:50

score 3 · Answer 4 · answered Jan 29 '13 at 18:40

Yes, generating a SQL script is a built-in feature for Liquibase. Another area where Liquibase has an advantage over flyway is it's ability to migrate database code changes (packages, procedures, and functions). I use flyway but I'd love to have those Liquibase features available in flyway.

score 2 · Answer 5 · answered May 19 '18 at 20:07

2

The documentation for Flyway command line tool says that dryRunOutput is a Flyway Pro feature that outputs SQL statements to a specified file.

answered May 19 '18 at 20:07

Noumenon

5,099
4
53
73

Can Flyway or Liquibase generate an update script instead of updating the database directly?

5 Answers5

Linked