Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Mule and Flyway DB Migration Tool Integration

DZone's Guide to

Mule and Flyway DB Migration Tool Integration

Learn how to integrate these two technologies so your data can fly from one database to another. Hope it doesn't get tired along the way!

· Integration Zone ·
Free Resource

How to Transform Your Business in the Digital Age: Learn how organizations are re-architecting their integration strategy with data-driven app integration for true digital transformation.

I worked on integrating Flyway DB with a Spring framework application in 2014 and I loved it. It made my life easy and simple for the next 2 years. 

I had a similar experience with, Liquidbase. One of the advantages that I really loved was that it supported XML format along with SQL scripts.

DB migration is much important when you are starting your Mule project, as you are going to deal with databases for a long period of time as well as a lot of DB schema changes.

In this post, I will continue with my previous project where we picked up properties from specific environment properties (Mule: Load Properties as per the Environment (With Default Properties File)).

The code for this article is available on GitHub.

1. Download the project from the GitHub location.

2. Open the project in Anypoint Studio -> import->Anypoint Studio->pom base project.

The project structure will look like what I've got below:

Image title

1. In this, if you observe carefully, you will see that the DB migration scripts are kept under resources/db/migration with specific format as V1__initial_Schema.sql.

If you want to have another schema, or update an existing schema, then add new schema file like: V1.1__update_Schema.sql or V2__update_schema.sql.

Flyway's file naming convention can be found on their site, but, also, we can change the default file naming convention by overriding this property of the flyway class.

2. Also, if we provide environment argument (i.e. Dmule.env=dev) properties are picked up from the dev.properties file.

Image title

Now, you can add as many properties files as your environment will allow. While running, just provide the environment name in the argument value.

We will be using dbcp basicdDataSource, as it has more advantages over MySQL or Oracle data source. The following are some nice resources on why DBCP data sources are good to use:

  1. https://stackoverflow.com/questions/17945137/spring-drivermanagerdatasource-vs-apache-basicdatasource

  2. https://www.journaldev.com/2509/java-datasource-jdbc-datasource-example

  3. https://mytechrepo.wordpress.com/2015/02/17/drivermanagerdatasource-vs-basicdatasource/

Image title

Also, flyway configuration is provided in this code.

Assumption - MySQL database is installed on your local machine with a root username and root password.

Once the application is imported, run it as a Mule application-> provide the environment argument (Dmule.env) so it will pick dev.properties file values.

Once your application has run successfully, go to the MySQL Workbench and you will see that a demo table has been created with one record inserted automatically. That's the magic of the Flyway DB migration tool.

Also, the Flyway DB migration tool maintains a schema_version file to validate DB migration files that have been newly added to your code, and to show the status of each DB migration file.

If MySQL Workbench is not available, then once the application is built successfully, go tothe  browser and run, http://localhost:8081/hello

Result:

Hello World from Mule DB. [{id=100}]

If you have any questions or feedback, please let me know. Thanks!

Make your mark on the industry’s leading annual report. Fill out the State of API Integration 2019 Survey and receive $25 to the Cloud Elements store.

Topics:
mulesoft ,flyway ,mule ,integration ,database migration

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}