Flyway Integration in a Mule Application

Flyway Introduction

Flyway is an open-source database migration tool that strongly favors simplicity and convention over configuration. It is based on 7 basic commands: Migrate, Clean, Info, Validate, Undo, Baseline, and Repair.

Migrations can be written in SQL (database-specific syntax — such as PL/SQL, T-SQL — is supported) or Java (for advanced data transformations or dealing with LOBs). With Flyway, all changes to the database are called migrations. Migrations can be either versioned or repeatable.

Versioned Migrations

The most common type of migration is a versioned migration. Each versioned migration has a version, description, and checksum. The version must be unique. The description is purely informative to remember what each migration does. The checksum detects accidental changes. Versioned migrations are applied in order once.

Versioned migrations are typically used for:

• Creating/altering/dropping tables/indexes/foreign keys/enums/UDTs/…
• Reference data updates
• User data corrections

Here is an example:

Repeatable Migrations

Repeatable migrations have a description and a checksum, but no version. Instead of being run just once, they are (re-)applied every time their checksum changes.

This is useful for managing database objects whose definition can then be maintained simply in a single file in version control. They are typically used for:

• (Re-)creating views/procedures/functions/packages/…
• Bulk reference data reinserts

Here is an example:

SQL-based Migrations

Migrations are commonly written in SQL. This makes it easy to get started and leverage any existing scripts, tools, and skills. You have access to the full set of capabilities of your database and eliminates the need to understand any intermediate translation layer.

SQL-based migrations are typically used for:

• DDL changes (CREATE/ALTER/DROP statements for TABLES,VIEWS,TRIGGERS,SEQUENCES,…)
• Simple reference data changes (CRUD in reference data tables)
• Simple bulk data changes (CRUD in regular data tables)

Naming

In order to be picked up by Flyway, SQL migrations must comply with the following naming pattern:

The file name consists of the following parts:

• Prefix: V for versioned (configurable), U for undo (configurable) and R for repeatable migrations (configurable)
• Version: Version with dots or underscores separate as many parts as you like (Not for repeatable migrations)
• Separator: __ (two underscores) (configurable)
• Description: Underscores or spaces separate the words
• Suffix: .sql (configurable)

Flyway Integration with MuleSoft

• Create Project: Go to Anypoint Studio and create a Mule project.
• Application Properties: Create an application.properties file under the src/main/resources folder. Application property will contain DB credentials and DB migration script locations.

• Folder: Create a folder db in src/main/resources. Also, create a subfolder migration under db.
• SQL Migration Script: Create a script file V1__create_accounts_table.sql in the migration folder and add the below script.

• Configuration Properties: Go to Mule config and add configuration properties from the Mule palette. Add application.properties in global configuration.

• Spring Config: Create beans.xml in src/main/resources. Add the contents below in spring config:

• Spring Module: Go to Mule palette again, add spring module and choose beans.xml in the configuration.

• Pom Dependency: Add the dependencies below for Flyway and PostgreSQL database:

• POM Shared Library: Add the library below in shared libraries in the Mule Maven Plugin:

• Spring Beans: Edit beans.xml and add below beans for datasource and Flyway.

• Database Config: Go to Mule palette and add the database module. Go to global elements and add database configuration, with datasource reference connection.

• HTTP Listener: Create an HTTP listener and add a select database operation.

• Run Application: Run the Mule application and you will see the logs for Flyway script execution.

• Verify Tables: Check the tables created in the database. Also, you can check the entries in flyway_schema_history table for one script executions.

16. Verify Endpoint: Now you can hit the endpoint for getting account details. It will return account results from the DB.

GitHub Repo: https://github.com/shyamrajprasad/mulesoft-flyway-integration

In this article, we have learned how to integrate the Flyway DB migration tool in the Mule application in order to execute all of the scripts from a folder.

References: https://flywaydb.org/documentation/