First8 staat voor vakmanschap. Al onze collega’s zijn een groot aanhanger van Open Source en in het bijzonder het Java-platform. Wij zijn gespecialiseerd in het pragmatisch ontwikkelen van bedrijfskritische Java toepassingen waarbij integratie van systemen, hoge eisen aan beveiliging en veel transacties een belangrijke rol spelen. Op deze pagina vind je onze blogs.

Intro to Flyway – a database migration tool

Flyway is an open source database migration tool developed by Boxfuse. Using Flyway you can execute plain SQL database migrations. Flyway makes it simple to keep track of the state of your database through versioning across different environments (DTAP). Next to that it becomes easy to rebuild your database from scratch using Flyway. This means that not only is your code versioned, your database schema is versioned. This is very pleasant for Continuous Delivery/Integration.

You can place the SQL scripts in your workspace and configure the path in Flyway. After that you are all set and Flyway will automatically keep track of the scripts en migrates where needed. Flyway automatically validates if already executed scripts still match the latest version in your workspace. Meaning, if you change something in an already executed SQL script, Flyway detects this and the migration will fail.

There are alternatives to Flyway, for example Liquibase. It is definitely worth it to compare the available tools for your specific project. In this blog post we will focus on Flyway specifically.

 

How does Flyway execute migrations?

Flyway looks for the metadata table named ‘schema_version’ in your database. If this table is missing it will create this table in the database. Flyway uses this table to keep track of the state of your database. Next, Flyway scans the configured classpath for migrations. These migrations can be written in SQL or Java. After that the migrations are sorted by version number. Next, Flyway walks through the versions and uses the metadata table to see which scripts were already executed. It also checks the checksum of already executed scripts with the metadata table to validate that there has been no changes in already executed scripts. If something has changed in an already executed script Flyway will throw an exception which causes the migration to fail. Versions that have not been executed will be executed and for each version a row will be added to the metadata table ‘schema_version’. For a complete decsription take a look at https://flywaydb.org/getstarted/how.

Since Flyway keeps track of the state of your database in the database self you can be certain that the ‘administrative’ tasks are executed correctly, unlike some other database management tools (which keep track of the database state in a file in your workspace/VCS).

 

Version numbers

The version numbers for SQL scripts can be assigned by prefixing the file name with a prefix in the format: ‘V[versie]__’ for the initial schema this could be: ‘V1.0.0__initial_schema.sql’ or ‘V1_0_0__initial_schema.sql’ (both a dot or an underscore can be used as seperators for major, minor, patch).  In the metadata table the version will be (in both cases) 1.0.0 and the description will be ‘initial schema’.

 

Going back in time

Flyway does not offer support for reverting an executed version. Once a migration has been executed it can become difficult to go back one or multiple versions.

 

Supported DBMSs

Flyway offers support for a wide range of databases. For a complete overview of the supported databases see the Flyway site: https://flywaydb.org/documentation/

 

Supported usage

Flyway offers support to be configured and run from command-line, Java API, Maven goals, Gradle buildscripts, Ant and SBT.

 

Supported usage

In the next post we’ll look at the basic usage of Flyway: we will create a simple application, set up configuration for Flyway and execute a couple of migrations.

Sources