Utilize Jenkins & Flyway for Migration and Database Versioning — Self Migration
Before reading this story, would you mind asking these questions:
Are you currently working with a different database environment?
Are you still doing queries between environments manually?
Do you need to record each query statement?
If YES, this story is worthy for you to read as study material. You can migrate the database seamlessly and can have a database versioning summary, by using Jenkins and plugin Flyway. This guide can be done on a single database as self migration or migrate between databases.
- Self Migration
By putting your SQL query in a specific directory, Jenkins will self migrate it fast. Easy to utilize and record database versions on every build.
2. Database Migration Across Servers
This utility can do migrate from one database into another database, seamless!. It’s very compatible to do database migrations between two environments.
In this story, assume that you have already installed these:
1 Jenkins
2 Database, with open remote configuration (check supported DB here)
- Install plugin Flyway
Open “Manage Jenkins > Manage Plugins”. Click on the “Available” tab, then search “flyway” on filter input. Make sure to install “Flyway Runner”.
- Create Flyway instance
Open “Manage Jenkins > Global Tool Configuration”. Scroll down until finding the Flyway section, click the “Flyway installations” button. Write instance name, click “Installed automatically”, choose Flyway version, then Save it.
By creating a new Flyway instance, Jenkins will provide a dedicated directory for SQL files as a migration object.
- Create Jenkins item — Flyway initiator
This initiator step only runs once for the new Flyway instance. This step is used to create the instance directory automatically. Furthermore, we can put the SQL files in the directory.
Create “New Item”. Enter the name and choose “Freestyle Project”, click the “OK” button. Feel free to write on the description text box.
Scroll down until the Build section. Click “Add Build Step > Invoke Flyway”.
InstallationName: Choose the Flyway instance; it will collect the migration SQL files there.
Command: Flyway’s command such as ‘migrate’ to migrate database, ‘info’ to write down database versioning log, and ‘validate’ to validate the database versioning log with instance SQL files.
Database URL: Your database URL.
Credentials: Your database credential.
Click “Build Now” on the left sidebar to run it.
Then you can see the build history at the bottom of the sidebar. Click on it to see the result.
This console output describes that Jenkins was able to communicate with the database, entirely. Afterwards, you can reuse this item by change the Invoke Flyway configuration.
- Upload SQL file
Before run Self Migrate, we have to upload the SQL file into the destinated Flyway instance by using FTP or SFTP. To get your Jenkins base directory, open “Manage Jenkins > System Information”, search for “JENKINS_HOME”, it will show you the place where Jenkins installed.
Upload your SQL file into {JENKINS_HOME}/tools/sp.sd.flywayrunner.installation.FlywayInstallation/{your Jenkins instance name}/sql. As you can see
- Create Jenkins item — Self Migrate
In this item, it will do self migrate to the selected Flyway instance. Make sure to choose the right instance that has been uploaded SQL file before.
Create “New Item”. Enter the name and choose “Freestyle Project”, click the “OK” button. Feel free to write on the description text box.
Scroll down until the Build section. Click “Add Build Step > Invoke Flyway”. For Self Migration, we need to create 3 Build Steps. Make sure to add with this sequence “Info — Migrate — Validate”. Use the previous configuration (refer to flyway initiator) on each sequence.
For this item, I would like to recommend each Flyway instance only has a self migrate item. Also, do self migrate for your first staging environment tier. It will make you easier to maintenance.
- Run self migration
First, I like to check the schema before run Self Migration. As we can see there is no table there. Open the self-migration item, click the “Build Now” button.
Afterwards, click item history then “Console output”. As we can see, this console output describes that each process was finished successfully. In the first step, it shows the database versioning there is a pending SQL file. Second, the migration process logs and last the validation step that confirms the self migration process validated.
That’s how Self Migration’s work. It’s quite structural for the first time, but it will easier for you to do database migration and versioning. For the next story, I would like to tell you how to do database migration across servers. By creating some Flyway instances and items, Jenkins will able to migrate from the source database into the destined database. it’s relatively easy, joyful, and hopefully will be useful for your further development.
