Managing Database Schemas with Flyway

Categories: Java

Introduction

When implementing an application which persists its data in a relational database, a necessary step is to execute DDL statements against the database to define the necessary tables, sequences, constraints, etc. Various base records (reference data) might also need to be inserted. This needs to be performed for each “installation” of the application.

And as the application evolves over time, upgrades of the application will require matching upgrades of the schema in the database.

This article looks at the Flyway project that provides a solution for managing database schemas. Flyway does have a few proprietary features for which a license is required, but the open-source features are sufficient for most use-cases.

This article is very brief because the Flyway site has good documentation of its own, and the product is very nicely focused on a small (but useful) set of features. See the official documentation for more details.

Basic Concepts

For version 1 of any application, it is easy enough to write SQL scripts to define the necessary tables and records. However when version 2 is released:

  • some installations of version 2 will be new, ie the matching database schema needs to be created
  • but some installations will be upgrades where a database matching version 1 already exists

Flyway’s solution is simple: there should be a sequence of SQL scripts for the application, typically one for each released version. A new installation executes all scripts in order, while an upgrade only executes scripts that are “new” ie have not yet been run against this particular database.

In order to know which scripts have already been run, Flyway creates a single table in the target database for its own “metadata”; it records in this table which scripts have been run (ie which “version” the database is currently at). This implies that a script must never be changed retrospectively after having been included in a release.

Scripts are simply files with a naming scheme where each filename is of form “{version}__{description}”, eg “V1__initial” or “V1_1__newstuff”.

For special cases, a “version update” can be associated with an arbitrary Java class rather than an SQL file, in order to update a database schema or (more likely) modify records in a way that SQL cannot express.

Usage

Flyway is implemented in Java, and:

  • can be used as a standalone application (executable jarfile), and given a directory of SQL scripts to run.
  • can be built into a standalone application (executable jarfile) where the SQL scripts are within the jar
  • can be used by your code as a library, eg your main application can have a commandline-option to run flyway internally

Flyway uses standard JDBC to connect to the target database in order to run SQL scripts.

See the Flyway official documentation for the details.

Spring-boot has support for Flyway; just add Flyway to your application’s classpath and on startup Flyway will be executed to upgrade the database if needed. When no upgrade is needed, the extra time required at startup is minimal, ie not relevant for most use-cases.

Alternatives

Liquibase has very similar features. The primary difference is that Flyway SQL scripts are “native SQL”, ie may contain database-specific commands. Liquibase has invented their own “portable DDL” language which is then mapped to the actual commands for the target database at runtime.

The Flyway approach is superior if you support only one (or maybe two) different relational database types; because the scripts are native they can be read and verified by anyone competent in SQL.

Liquibase is superior if you need to support a large number of different relational database types; writing and reviewing such scripts is harder as it requires knowledge of Liquibase syntax but the scripts only need to be written and tested once.