We are planning to Migrate Flyway Scripts to Liquibase for our DB2 database. We wanted to check, few things based on your experience:
1. If there is any automation tool that takes care of migrating scripts from Flyway to Liquibase changesets. As there are thousands of scripts from historical stand point that we want to migrate, any automated tool, would be our preferred choice.
2. How Release Management is taken care with Liquibase between Development Environment, User Acceptance Environment to Production Environments. With Flyway, we used to maintain JARs in nexus, for example, which were deployed and scripts were executed on appropriate databases.
3. Also for objects and syntax not supported by Liquibase generateChangeLog command: Tablespace, Procedures, Grants, Triggers, Packages, how do you manage them along with change sets?
With questions, as you can see, we are very new to Liquibase, so any pointers to practical articles, documentation, tools will help.
I don’t know of any tools to automatically migrate your existing scripts. The best way to re-use them will probably depend a lot of what your scripts look like, whether they are SQL, java etc. I’m also not sure what you need as far as knowing what changes had been applied to some databases and not others or if all your databases are in a fairly consistent state. If your changes are SQL, you may be able to just point liquibase to the files with an and liquibase will automatically run each sql file as a changeSet. If they are java-based that may be more complex.
Alternately, you could just apply all the migration scripts to a database to get the database to a known “correct” state and then run generateChangeLog to generate the corresponding changeSets. I’ll answer your related #3 questions below, but generateChangeLog can be a good way to kickstart an existing project on Liquibase. With your generated changeLog, you can use changeLogSync to mark all your historical/already executed changeSets as ran on your various databases and then you can continue using Liquibase in a more standard manner.
Liquibase really leaves deployment up to you. It just needs to be given a reference to a directory or jar that has a changelog file in it and a database that you want to run “update” against. Whether you keep that in a separate jar in nexus or put the changelog files in your application artifact or whatever depends on what works best for you.
I tend to suggest that people not rely on generateChangeLog for their day-to-day changeSet creation but instead just add the changeSets themselves. The changelog format is designed to be human readable/writable and can be managed in multiple formats based on what you are most comfortable working with (xml, json, yaml or sql). For me, I like just writing the changeSets as I need them. It is an easy cycle of “add a changeSet, run liquibase update against your local database, update code to use the new database state, repeat”. Liquibase can describe any database change you want as a changeSet and when you are writing them yourself you are sure that they are describing what you expect and want.
GenerateChangelog/diffChangeLog work well as a way to jumpstart a project with an existing database into the changelog, and while it can be used to enable a process where you make change directly to the database and then attempt to capture those changes you can run into problems. Like you mentioned, some object types are not supported and so you have to remember to manually add changSets for them. It also can’t understands renames vs. drops/adds so you have to fix those as well. If you are wanting to use a diff-based process, I’d recommend thinking of the generated changeSets as a rough draft that you would want to go through and clean up before actually committing them to your source code repository.
Thanks Nathan for your detail answer. Please find below some of my comments:
1. For #1 - As our orgnization's objective is to migrate flyway SQL scripts to Liquibase ChangeSet, the primary input for this migration is SQL Scripts that are already saved in Flyway. The historic information has to be retained, so if a script is run to add a column in 2013 and then another script was written in 2015 to delete the same column, then we need to maintain these steps in liquibase for historical references. With this, to get the correct 'known state' is also out of question, because historical steps will not be recovered with this approach.
2. Thanks for clarification on this, this is really helpful.
3. As it is a migration kind of requirement to migrate Scripts from Flyway to Liquibase, we GenerateChangelog/diffChangeLog would not fully satisfy the non supported objects and relations from SQLs. But as you have pointed out that creating change set ourselves would make sense to resolve these gaps.
We would have no choice it seems, but to proceed with manually migrating individual scripts from Flyway to Liquibase one by one using temporary database for execution of flyway script and then using GenerateChangelog on that changed DB and modify change sets generated to support non supported objects.
