Utilize Jenkins & Flyway for Migration and Database Versioning — Across Database Migration
Continue from the previous article “Utilize Jenkins & Flyway for Migration and Database Versioning — Self Migration”. We had just utilized Jenkins with the Flyway plugin to do Self Migration on the cloud. In this story, we will enhance Jenkins’s job to do database migration across servers, on purpose to upgrade the database versioning until the destination instance has the same version as the source instance in an easy way. Such an example to migrating the DEV database to the SIT database. Indeed, this migration only available to migrate the database with the same database and schema.
In this article, assume that you have already installed these:
1 Jenkins
2 Database, with open remote configuration (check supported DB here)
3 Flyway plugin
4 Self migration job, some Jenkins configuration (read this)
- Create Flyway Instance for Another Database
First, click “Manage Jenkins > Global Tool Configuration”, scroll down on the Flyway section, add a new Flyway instance there for another database that will be used as the destination instance.
- Run Flyway Initiator
Open Jenkins click the Jenkins item that used to initialize the Flyway instance, then click “Configure”. Scroll down until the Build section, then change the Invoke Flyway form, change InstallationName and Database URL into brand new Flyway instance that needs to be initialized. Click the “Save” button to save it.
On the sidebar, click “Build Now”. after the process completed, click on “Build History > Console Output”. The picture below describes that Jenkins able to connect with the database instance and show the schema is empty and no migration file found.
In the next step, we will make a configuration to utilize Jenkins that able to migrate the database across servers.
- Create a Jenkins item — Flyway Migration.
Click “New Item” on the left sidebar. Put your migration item name, click Freestyle Project, and click the “OK” button.
Feel free to put some text in the “Description” text area. A simple description will help you a lot when you maintenance many Jenkins items in the future.
Scroll down to the Build section then create five build step, like these :
- Invoke Flyway — source Flyway instance with “info” command
- Execute Shell — put command to copy SQL file from source to destination
- Invoke Flyway — destination Flyway instance with “info” command
- Invoke Flyway — destination Flyway instance with “migrate” command
- Invoke Flyway — destination Flyway instance with “validate” command
On the Invoke Flyway, make sure to put InstallationName, Command, Database URL, and Credentials correctly.
In the “Execute Shell” step. Put this command.
cp -n ${JENKINS_HOME}/tools/sp.sd.flywayrunner.installation.FlywayInstallation/45.76.159.142__db_A__schema_sc-tutorial/sql/* ${JENKINS_HOME}/tools/sp.sd.flywayrunner.installation.FlywayInstallation/66.42.54.178__db_B__schema_sc-tutorial/sql/cp -n : to copy the files but don’t overwrite an existing file.
${JENKINS_HOME} : call an environment variable of Jenkins home directory
/tools/sp.sd.flywayrunner.installation.FlywayInstallation : the Flyway instance directory
/45.76.159.142__db_A__schema_sc-tutorial/sql/* : the SQL directory of source Flyway instance, * suffix to refer all file inside to it.
/66.42.54.178__db_B__schema_sc-tutorial/sql/ : the SQL directory of destinated Flyway instance
By using this build step, Jenkins will automatically copy all new SQL files in the source Flyway instance into the destined Flyway instance.
- Run migration
Before doing run migration. It would be nice to check the source and destination database by doing some queries.
the two pictures above, show the current condition of the source and destination database just before run across database migration.
Click “Build Now” on the left sidebar to run it. To see the console output, click on the left side in the Build History section, click on the latest build as you can see in the example below.
With these console outputs, the database migration works well. Afterward, we can check the result by query the destinated database, then compare it.
In this tutorial, we able to do across database migration by using Jenkins and Flyway. Hopefully, it can help you to decide an alternative to do database migration with different servers, also pay attention to the database versioning without using any ORMs.
