BT

Facilitating the Spread of Knowledge and Innovation in Professional Software Development

Write for InfoQ

Topics

Choose your language

InfoQ Homepage News Flyway 3.1 Extends Schema Evolution to DB2 z/OS, Redshift and Vertica

Flyway 3.1 Extends Schema Evolution to DB2 z/OS, Redshift and Vertica

Bookmarks

Last week Boxfuse announced the availability of a new release of Flyway, its open source database migration tool. Highlight is the addition of IBM DB2 z/OS, Amazon Redshift and HP Vertica to Flyway's list of supported database management systems, a move that expands the coverage of the "big data and big iron space". Along with the added databases and bug fixes, the new release features SQL callbacks and packaged DB drivers for ease of use.

The new SQL-based callbacks simplify the notion of Java callbacks for pre and post processing introduced in Flyway 3.0. Hooks can now be implemented in SQL without resorting to Java. Non-Java projects also benefit from the inclusion of eight important JDBC drivers into the package, simplifying the exceedingly popular command-line tool, making it the most favored way to use Flyway.

With Flyway, migrations can be executed using different workflows. The command-line tool enables manual execution from the shell and integrates easily into deployment automation. JVM-based projects can use the Java API to migrate the application automatically on application startup. This is particularly useful to ensure the schema always stays in sync with the application or when there is no shell access to the database, such as on a mobile client. Alternatively migrations can be triggered using accompanying build tool plugins for maven, gradle, ant and sbt.

Flyway developers have created a schema evolution tool that is "easy to setup and simple to master." Focusing on controlling the execution of plain SQL migrations, Flyway was designed to roll-forward only and prescribes that migrations be incremental delta SQL scripts structured in a versioned file naming scheme. When using the API, apart from the database-specific JDBC driver and the Flyway JAR no other dependent libraries need to be deployed. For more advanced scenarios like dealing with LOBs and complex data transformation, Flyway also provides Java-based migrations.

Database migration or more precisely schema evolution is an essential technique for evolving schema-based (e.g. SQL) databases automatically in a Continuous Delivery setting. As database tables and other objects evolve incrementally, these changes need to be propagated through the deployment pipeline. DB migration tools such as Flyway manage a log of already installed migrations and automatically execute necessary migrations, thereby ensuring consistent schemas across stages and environments. Alternatives to Flyway include LiquiBase that uses an abstract XML migration file syntax and DBmaestro, a full database version control system.

InfoQ spoke to the main Flyway author and Boxfuse founder Axel Fontaine.

InfoQ: Axel, what are the main themes introduced beginning with Flyway 3.0?

Axel: The 3.x series is based around three big themes: Mobile, Non-JVM users and Extensibility. Mobile is about responding to the professionalization of Android development and the need for better tools to manage the SQLite database on device. Non-JVM users, especially in the Node and PHP space, have increasingly started using the command-line client. Flyway now makes life easier for them by bundling both JDBC drivers and templates for JDBC URLs. Extensibility is aimed at power users, by offering them lifecycle hooks and an SPI for resolving and executing migrations. On top of all that, Flyway also added support for MariaDB, Redshift, Vertica and DB2 z/OS.

InfoQ: How do you want to evolve Flyway in the future?

Axel: The philosophy has always been bug fixes first, new features second. The features on the immediate roadmap are bundling a private JRE with the command-line client and integration with native package managers, better support for managing complex database objects like procedures or packages, and a dry run mode that outputs to file.

InfoQ: In 2010, what did motivate you to start "yet another DB migration tool"?

Axel: It was never really the goal to start a new tool. I was looking for a solution for database migrations and had a certain idea in mind of what it should look like: simple to use yet powerful, allowing me to exploit the full power of the database, and with an API to migrate on application startup. I evaluated the market, but none of the existing solutions were close enough to fit the bill. So I had no choice but take the matter in my own hands.

InfoQ: Other implementations of database migration tools adopt a more semantic approach or provide automatic diffs from two states. Why did you decide against these approaches?

Axel: From an architectural perspective, I have always been a believer in contract-first. When you interact with a database, the schema is your contract, so that should be your starting point, not the code. There is also much more to database migrations than tables and columns. You should be able to leverage the full power of your database when defining indexes, triggers, materialized views and the many other useful tools a modern RDBMS provides you. Last but not least, such diffs break down on things like renames, but also in modern workflows like Continuous Delivery with Zero Downtime, where you want to apply the principles of Expand & Contract instead of performing direct changes.

InfoQ: Wouldn't auto downward migrations be a practical feature?

Axel: While it sounds great on paper, in practice it turns out it neither works nor do you need it. In development, it's just as easy to clean and recreate your DB from scratch. In production, you should follow the Expand & Contract principles and write backward-compatible migrations that work with all versions of the code currently deployed. In case of a failed migration, besides the obvious problems with destructive changes, your downward migration will not be able to help you anyway, as it assumes the whole migration needs to be undone, whereas it might have failed half-way. And for a fast restore, you are much better off with the snapshot technology of your underlying storage layer anyway.

InfoQ: Are you planning to release a commercial variant?

Axel: Flyway in its current form will remain free and open-source. While we do offer the possibility to influence the roadmap by sponsoring the development of features and bug fixes, our main commercial focus is Boxfuse, where we strive to bring Flyway's simple and lightweight approach to immutable infrastructure, letting you go from code to minimal immutable hardened machine image in seconds and deploy it unchanged locally and on AWS.

Rate this Article

Adoption
Style

BT