How to orchestrate database migrations by integrating Flyway with an online schema change tool

Database migrations play a significant part when it comes to offering product enhancements. While it’s common knowledge that Spring-boot provides out-of-the-box integrations with Flyway, a database version management tool, its default functionality is more geared toward running SQL-based database migrations in a web application startup. 

When the requirement follows making small adjustments to the table schema, the default functionality might prove to be of great help. When it comes to running custom database migrations owing to an extremely large volume of data, our relentless process of trial and error helped us identify that integrating Online Schema Change can help overcome ‘MySQL Innodb’ limitations – which result from failing to execute data definition language that can bypass writers being blocked. 

We also faced a similar issue with Freshdesk when we tried to perform enhancements in Spring-boot Microservices, which offer database migrations and execute the ALTER command. Needless to say, it was a cumbersome process, which finally led us to the best way forward.

Database migrations: Integrating online schema change (OSC) tool with spring-backed Flyway hook

To work through this predicament, we looked into a gamut of open source tools that can run MySQL DDL statements without blocking writes in the production environment. These tools, which included pt-online-schema-change, GitHub Gh-ost, as well as Facebook-OSC, were finally integrated with Flyway.

This image shows how online schema change tools are integrated with Flyway

In this blog, we shall delve into one open-source tool, which is the pt-online-schema-change tool, to perform an end-to-end integration with Spring-backed Flyway. This integration can also be achieved with other tools.

Constructing custom data migrations

Flyway supports MySQL migration (.sql) file as a default option. However, to execute custom migrations, we can use our own migration files (which include json|csv|yaml, and others) and custom attributes. In this instance, we chose to go with the JSON format.

Filename Convention: ‘V<yyyyMMddHHmmss>__<alter-table-description>.json’ 

Here’s an example.

 

In the JSON file displayed above, the perconaOptions attribute provides percona specific properties and respective values as per our system infrastructure and load. These properties help to run specific migration files in a controlled environment. Along with these properties, developers can also leverage properties related to database connections from the spring application.properties file.

Implementing custom Flyway hooks to execute JSON based database migrations

For those that need more than what the SQL and Java-based migrations offer, we also have the possibility to implement Flyway contracts i.e.  MigrationResolver coupled with a custom MigrationExecutor. These can be used for executing custom migration files, such as json|csv|yaml, etc.

By using the skipDefaultResolvers flyway property, these custom resolvers can also be used to completely replace the built-in ones (by default, the application runs custom resolvers in addition to built-in ones).


Intercepting spring-backed Flyway to include custom percona resolvers

Spring provides out-of-the-box integration with Flyway in which all flyway configuration properties are overwritten and available as spring data migration properties. However, Spring doesn’t provide the functionality to include Custom MigrationResolver as of now (which Flyway does). So, we need to intercept FlywayConfiguration to achieve the same.

Here’s the code snippet for ‘BootFlywayConfiguration.java’

Detailing database migrations using application.properties.
spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration
#spring.flyway.locations=classpath:db/script
#spring.flyway.skipDefaultResolvers=false
spring.flyway.baselineVersion=1
spring.flyway.baselineOnMigrate=true

Running Flyway data migrations on a spring application startup. Here’s the code snippet for DbMigrationApplication.java

Result: The following is an image displaying the application log.

This is how the flyway_schema_history  table looks once all migrations (including SQL and JSON migration files) are executed in a given order.

Database migrations: Flying ahead

The beauty of integrating the Online Schema Change Tool with Flyway manifests in two ways: This integration can be easily developed without making changes to the existing services, and other services can easily plug into it and start using this feature from current migrations. Leveraging this integration allows us to easily plug any open-source Online Schema change tool with Flyway to run any data definition language (DDL) statement without blocking writes or being worried about its data size in the production environment. Depending on the needs of the user, Flyway can be integrated with any of the online schema change (OSC) tools to process and migrate large volumes of data.

Related Posts