Database Refactoring Toolkit: Flyway vs. Liquibase

In this article we’ll talk about Flyway and Liquibase , the two most popular Java-based tools for database refactoring. The purpose of the article is to compare these tools and find out which of them is better to use when.

Flyway

The Flyway concept is centered around six different commands to support automated refactoring and versioning of the database. These commands can be executed from the command line, during the build process (produced using Maven or Gradle) or directly from Java code using API calls. When executing these commands, you need to provide the connection parameters to the database (url, username, password) that you want to refactor.

The main command is called migrateand performs the function that contains the whole essence of database refactoring: it scans a special folder with sql scripts (each of which has a version number in the file name) and checks which ones have already been applied to the target database. Then it executes those that have not yet been applied to this database. In the event of inconsistencies, for example, if a script that has already been applied has changed since it was applied, Flyway interrupts its work with an error message.

A unique feature of Flyway is that migration scripts can be not only in SQL format, but also in the form of Java code. The second option allows you to implement dynamic migrations with complex logic. However, the Java approach should be used with caution, since such migration scripts are usually hard to debug if something goes wrong with them.

In addition to the main command migrate, Flyway has additional commands that facilitate the process of database refactoring.

The command infoshows all available migration scripts from the specified folder and notes which ones have already been used and which ones will only be applied to the target database.

To check whether the scripts applied to the database have changed, use the command validate. It is useful for us to know whether the script already applied from the folder has been changed since it was applied to the database, because this may mean that the script has been applied to different databases in different versions, and this can cause problems.

If you think that scripts should be used despite the failure shown by the validate command, you can run the repair command. It will reset the database table used by Flyway to record which scripts have already been applied (by default, this table is called SCHEMA_VERSION).

And last but not least, the command cleancompletely clears the selected schema (as you understand, this command should be used only for test databases).

Liquibase

Liquibase uses a different approach to the implementation of database refactoring. Unlike Flyway, which only supports migration scripts in SQL and Java formats, Liquibase allows you to abstract from SQL and thus eliminate the database refactoring to its specific implementation.

Instead of SQL scripts, Liquibase supports migration scripts in XML, YAML and JSON formats. In these scripts, you define changes in the database at the abstraction level. For each change, Liquibase has a corresponding element in XML, YAML and JSON. For example, a change that creates a new database table in YAML format looks like this:

createTable:
  tableName: Customer
  columns:
  - column:
      name: name
      type: varchar(255)
  - column:
      name: address
      type: varchar(255)

Type changes add column, create indexor alter tableothers, are similar.
During operation, Liquibase automatically applies all scripts that have not yet been applied, and, like Flyway, saves their metadata to a special database table. Like Flyway, Liquibase can be called from the command line of the build tools or directly through its Java API.

When to use them?

Both Flyway and Liquibase support all the functions required for professional refactoring and versioning of the database, so you will always know which version of the database schema you are dealing with and whether it is compatible with your software version. Both tools are integrated with Maven and Gradle and into the Spring Boot ecosystem, so that database refactoring can be fully automated.

Flyway uses SQL to determine database changes, so you can customize SQL scripts so that they work efficiently with a specific type of database on your project, for example, from Oracle or PostgreSQL. Liquibase, on the other hand, introduces an additional level of abstraction using XML, YAML or JSON to determine database changes. Thus, Liquibase is better suited for software that needs to be installed in different environments with different types of database servers. However, if you need complete control over your SQL, your choice is Flyway, as it allows you to modify the database using fully custom SQL or even using Java code.

The catch with both tools is that they are supported by one person (from the translator: according to the author) , and not by a large team. This may have a negative impact on the future development of both bodies, but this is not necessary. At the time of this writing, activity in the Flyway GitHub repository is higher than in the Liquibase repository .