Flyway is an open-source tool that helps you implement automated and version-based database migrations. It allows you to define the required update operations in an SQL script or as Java code. It keeps track of all applied migrations and the current database version so that it can detect and execute the required migration steps to update your database to the latest version. Flyway provides you several options to run the database migration. You can trigger it as part of your build process, use a command line client or integrate it into your Java application. In this post, I will give you an overview of Flyway and the steps required to integrate it in your project.
Many software projects are using relational databases as an important part of their technology stack. This typically requires the handling of database migrations. Reasons to perform migrations on the database are manifold. A few examples:
- New features require new sets of database tables, views and indexes.
- Bug fixes require changes to existing database objects.
- Performance problems require new indexes for certain database tables.
Many projects still rely on manually applied sql scripts. And sometimes not even that (a quick sql statement here or there to fix a problem). And soon many questions arise:
- What state is the database in on this machine?
- Has this script already been applied or not?
- Has the quick fix in production been applied in test afterwards?
- How do you set up a new database instance?
Database migrations are a great way to regain control of this mess. They allow you to:
- Recreate a database from scratch
- Make it clear at all times what state a database is in
- Migrate in a deterministic way from your current version of the database to a newer one
Welcome to Flyway, database migrations made easy.
The easiest scenario is when you point Flyway to an empty database.
- It will try to locate its schema history table. As the database is empty, Flyway won’t find it and will create it instead.
- You now have a database with a single empty table called flyway_schema_history by default. This table will be used to track the state of the database.
- Immediately afterwards Flyway will begin scanning the filesystem or the classpath of the application for migrations. They can be written in either Sql or Java.
- The migrations are then sorted based on their version number and applied in order.
- As each migration gets applied, the schema history table is updated accordingly.
With the metadata and the initial state in place, we can now talk about migrating to newer versions.
- Flyway will once again scan the filesystem or the classpath of the application for migrations. The migrations are checked against the schema history table. If their version number is lower or equal to the one of the version marked as current, they are ignored.
- The remaining migrations are the pending migrations: available, but not applied.
- They are then sorted by version number and executed in order.
- The schema history table is updated accordingly.
And that’s it! Every time the need to evolve the database arises, whether structure (DDL) or reference data (DML), simply create a new migration with a version number higher than the current one. The next time Flyway starts, it will find it and upgrade the database accordingly.
Ready to get started? This brief tutorial will teach how to get up and running with the Flyway Command-line tool. It will take you through the steps on how to configure it and how to write and execute your first few database migrations.
The installation of Flyway Command-line Tool is really straightforward. Download the latest package from the Flyway download page and extract it to a preferable location on the target machine. Make sure to either create a soft-link to the
flyway-executable or add the installation directory to your
On a Mac however you can also use Homebrew to install Flyway. This is more convenient and will create the required softlink to the
/usr/local/bin/ right away.
$ brew install flyway Updating Homebrew... ==> Downloading https://search.maven.org/remotecontent?filepath=org/flywaydb/flyway-commandline/5.2.4/flyway-commandline-5.2.4.tar.gz ######################################################################## 100.0% ? /usr/local/Cellar/flyway/5.2.4: 32 files, 23.6MB, built in 1 minute 28 seconds
We are good to go if we can execute
flyway from anywhere on the command-line. This should print out a quite extensive usage message showing that the command has been found and was executed successfully.
Let’s create a new directory for our simple project and jump into our new directory and create two sub-directories
$ mkdir flyway-tutorial $ cd flyway-tutorial $ mkdir conf $ mkdir sql
Once that is done, configure Flyway by creating a new file
/conf/flyway.conf like this:
flyway.url=jdbc:h2:file:./testdb flyway.user=SA flyway.password= flyway.locations=filesystem:sql
Now create a first migration in the
/sql directory called
create table PERSON ( ID int not null, NAME varchar(100) not null );
It’s now time to execute Flyway to migrate your database:
$ flyway migrate
If all went well, you should see the following output:
Flyway Community Edition 5.2.4 by Boxfuse Database: jdbc:h2:file:./testdb (H2 1.4) Successfully validated 1 migration (execution time 00:00.026s) Creating Schema History table: "PUBLIC"."flyway_schema_history" Current version of schema "PUBLIC": << Empty Schema >> Migrating schema "PUBLIC" to version 1 - Create person table Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.178s)
If you now add a second migration to the
/sql directory called
insert into PERSON (ID, NAME) values (1, 'Alice'); insert into PERSON (ID, NAME) values (2, 'Bob'); insert into PERSON (ID, NAME) values (3, 'Carol');
and execute it by issuing:
$ flyway migrate
You now get:
Flyway Community Edition 5.2.4 by Boxfuse Database: jdbc:h2:file:./testdb (H2 1.4) Successfully validated 2 migrations (execution time 00:00.058s) Current version of schema "PUBLIC": 1 Migrating schema "PUBLIC" to version 2 - Add people Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.045s)
OK, now we want to add a new column
age to the
person table. It might be tempting to go to the first sql and edit the create statement to add the new column like this:
create table PERSON ( ID int not null, NAME varchar(100) not null, AGE number not null );
and execute it by issuing:
$ flyway migrate
Flyway will prevent this with an error message in the output.
Flyway Community Edition 5.2.4 by Boxfuse Database: jdbc:h2:file:./testdb (H2 1.4) ERROR: Validate failed: Migration checksum mismatch for migration version 1 -> Applied to database : 1715188512 -> Resolved locally : 176315836
The rule of thumb for Flyway is
Never change a Migration once it has been applied to the database!
The reason for this is simple: If this would not be the case we cannot be sure that two installations – with the same set of Migrations – are really resulting in the same database state.
So the ideal step would be to add another sql script
V3__Add_age_column.sql to add the new column.
ALTER TABLE PERSON ADD COLUMN AGE NUMBER;
Execute it by issuing the flyway migrate command.
$ flyway migrate Flyway Community Edition 5.2.4 by Boxfuse Database: jdbc:h2:file:./testdb (H2 1.4) Successfully validated 3 migrations (execution time 00:00.062s) Current version of schema "PUBLIC": 2 Migrating schema "PUBLIC" to version 3 - Add age column Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.057s)
So this is hopefully enough to get started with Flyway. In this brief tutorial we saw how to install the Flyway Command-line tool, configure it so it can talk to our database and write our first couple of migrations. These migrations were then successfully found and executed by Flyway.
The goal of an automated, version-based database migration is to create your database migration scripts together with your Java code and to execute them automatically when you deploy a new version of your application. You can implement such a process with Flyway. I highly recommend you to it this in your projects.