Flyway/Liquibase for Database Structure and DBUnit for Database Inserts?

Question

I have the following scenario for my application:

• 1 Production Server
• 1 Test Server
• n Development Computers

For database migration we use Hibernate Schema Update for the Schema and DBUnit for filling in alle the production data (on all servers/computers). When the schema update is done I generate a new DTD File for the new schema, so I can do a fresh import of the DBUnit XML. The application updates the database at startup with the XML file (only on development and test servers/computers!)

Of course this approach is not optimal and fragile. So I looked at Liquibase and Flyway. Both seem to be great tools, but what I do not get is: How do I migrate the data? In my case, I dump the data of the production system once a week and add it to the applications source control as a DBUnit XML file, so all developers have "fresh" data and the test server has current production data, too.

The problem I see with Liquibase and Flyway is, that there is no solution how to do automated diffs from the database data and generate the migration changes automatically.

So my idea is the following with the following steps:

1. Set Hibernate to validate instead of update.
2. When a STRUCTURAL database change is needed, I add it to the migration script for the major version
3. No database inserts are in the migration script.
4. Generate a new DTD for DBunit based on the new database structure
5. Generate the DBUnit XML from the production database.

Another idea would be to utilize flyways JavaMigration and provide an initial Database Dump based on DBUnit. All other changes for database data will be handled in migration scripts. But still there is the problem: How to make diffs from the current migration script state and the production database state?

It would be awesome if anyone could provide me hints how to handle my scenario :)

Hawaii

Answer 1

If your goal is to use dumps of the PROD database in DEV and TEST environments, I would:

• Configure the DB migration tool to run on application startup (both Flyway and Liquibase support this through their respective APIs)
• Package all the DB structure migrations together with the app
• Dump both data and structure from PROD

This way, when the PROD database is restored to DEV or TEST, the old metadata table of the migration tool is restored as well.

When the app starts, the migration tool will discover that the db structure is outdated and upgrade it to the newest version. Done.

No need to use DBUnit for this.

Dygall

I am not sure how to generate data diff migration scripts with this approach... Could you clarify this? Is there even a way with Flyway to generate a diff-migration script? – Hawaii

As I understood it you do all full PROD dump on a weekly basis. Why do you also need a data diff if you do the full dump anyway? – Dygall

What I wanted to do is creating a migration script with all the NEW data in the production database (also changed data). So essentially, every week a script adds a new flyway migration script to the project (and version control) with all the database diffs. As far as I understood flyway, this is the recommended way(?). The size of my database is a few hundred megabytes. So every full dump checked into version control (when not needed anyway, since there are just a few datasets changed) would be overkill. – Hawaii

Answer 2

The short answer is that all your changes would be done through Liquibase or Flyway.

We use Flyway, with the same prod/test/development setup. We make all db changes (structure or metadata) using Flyway migration scripts, stored in source control. Each time we do a new deployment to an environment, we first run the migration scripts there (using either the command line tool or the maven plugin). The code first goes to development environment, gets integration tested there and keeps going to test and production.

The main thing to watch out for is that Flyway requires a linear versioning to the files, so if two developers check in migrations at the same time, one of them will have to rename theirs.

Alkahest