Getting Started with Flyway and Version-Based Database Migration
Take your skills to the next level!
The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.
Updating a database seems easy as long as you’re the only developer on the team and don’t have to support multiple systems. But that changes dramatically when your team implements multiple features in parallel, uses different test databases and runs the application on one or more production servers. Updating all these databases, keeping track of all executed update operations and merging the changes of your co-workers quickly becomes an issue.
But that doesn’t have to be the case. We already solved these problems for our Java code. There are several tools, like git, CVS or svn, to version your code, merge independent or conflicting changes and store everything in a repository.
So, wouldn’t it be a good idea to apply the same approach to your database?
That’s the general concept of version-based database migration. You define multiple versions of your database. In most cases, it’s one database version for each code release. And then you specify the changes that need to be applied to migrate your database from one version to the next. A typical example for that is the SQL script you’re currently sharing with your co-workers and which you might execute manually after the deployment.
As you can see, you’re doing most of the work already, even if you don’t have an automated, version-based database migration process. So, you just need a tool that detects and applies the required database changes. One of these tools is Liquibase which I explained in some of my previous posts. Another one is Flyway which I want to show you now.
What is Flyway and how does it work?
Flyway is an open-source tool, licensed under Apache License 2.0, 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. You can then run the migration from a command line client or automatically as part of your build process or integrated into your Java application.
The good thing about this process is that Flyway detects the required update operations and executes them. So, you don’t need to know which SQL update statements need to be performed to update your current database. You and your co-workers just define the update operations to migrate the database from one version to the next. And Flyway detects the current version and performs the necessary update operations to get the database to the latest version.
To be able to do that, Flyway uses a metadata table to document the current database version and all executed updates. By default, this table is called SCHEMA_VERSION.
OK, enough theory. Let’s implement the first automated database migration with Flyway.
Define your first migration
As described earlier, you can use SQL scripts or Java classes to define your database migration. In most cases, you should define all migration steps in an SQL script. But if you need to implement complex migrations, e.g., read information from a BLOB and store it in a new set of columns, you can do that in Java.
Let’s stick to the most common approach and use the following SQL statements to create a small database. It’s a script for a PostgreSQL database that creates a custom database function, a book table and Hibernate’s default sequence hibernate_sequence. Please be aware that SQL scripts are often database-specific. You, therefore, might need multiple SQL scripts, if you need to support multiple databases.
CREATE OR REPLACE FUNCTION calculate(IN x double precision, IN y double precision, OUT sum double precision) RETURNS double precision AS $BODY$ BEGIN sum = x + y; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100;
ALTER FUNCTION calculate(double precision, double precision) OWNER TO postgres;
CREATE TABLE book (id bigint NOT NULL, publishingdate date, title character varying(255), price double precision, version integer, CONSTRAINT book_pkey PRIMARY KEY (id));
CREATE SEQUENCE hibernate_sequence INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1;
I store these SQL statements in a file called V1__create_database. The file name follows Flyway’s default naming convention: V<VERSION>__<DESCRIPTION>.sql. So, this file contains the SQL statements for database version 1 and Flyway will store it with the description “create_database” in the SCHEMA_VERSION table.
OK, you’re done. That’s all you need to do to define your first database migration. You now just have to trigger it.
Perform the database migration
You can integrate Flyway into your application, or you can run it automatically as part of your build process or manually from the command line. In this post, I want to show you the command line client and the Java integration.
Flyway command line client
The command line client is easy to use. You just need to download the latest version from https://flywaydb.org and extract the archive to your local file system. After that, you should find the following files and folders on your hard drive.
In this guide, you just need the 2 selected folders.
In its default configuration, Flyway processes all SQL files located in the sql folder. So, you should copy you V1__create_database.sql file there.
And the conf folder contains the flyway.conf configuration files. That file contains lots of comments which document all configuration parameters in great detail. So, I will only point you to the most important ones. These are:
- flyway.url
- flyway.user
- flyway.password
The flyway.url parameter defines the JDBC url which Flyway shall use to connect to the database. For most databases, Flyway will detect the required JDBC driver based on the flyway.url. So, you don’t need to provide it.
The parameters flyway.user and flyway.password are optional. The command line client will prompt you for the user information, if you don’t provide them in the configuration file.
And here is the configuration that I use for this example. I tell Flyway to connect to my PostgreSQL database recipes on localhost:5433 and to use the user postgres with the password postgres.
flyway.url=jdbc:postgresql://localhost:5433/recipes
flyway.user=postgres
flyway.password=postgres
OK, so let’s run Flyway and initialize the database to version 1. You can do that by calling the command line client with the migrate command.
C:\dev\wrk\Flyway\flyway-4.2.0>flyway migrate
Flyway 4.2.0 by Boxfuse
Database: jdbc:postgresql://localhost:5433/recipes (PostgreSQL 9.6)
Successfully validated 1 migration (execution time 00:00.038s)
Current version of schema "public": << Empty Schema >>
Migrating schema "public" to version 1 - create database
Successfully applied 1 migration to schema "public" (execution time 00:00.076s).
And you’re done. As you can see in the log output, Flyway found an empty database and applied the migration script for version 1.
Running the migration process from the command line is OK if your operations team updates your application manually. But for all automated deployments or applications that your customers host themselves, you might prefer an integrated migration that updates the database at application startup.
Integrating Flyway into your Java application
Flyway is implemented in Java and extremely easy to integrate. You just have to add the flyway-core jar file to your project. If you’re using Maven, you can do that with the following dependency.
<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-core</artifactId>
<version>${flyway.version}</version>
</dependency>
And after you’ve done that, you can trigger the Flyway database migration from your Java code.
You can do that by creating a new Flyway instance, configuring the datasource and calling the migrate() method. You can either call the setDataSource method with a DataSource object or provide the JDBC connection, username and password as Strings. In general, I recommend to provide a DataSource object because you can easily get it from your connection pool. But in my simple test environment, I don’t have that and, therefore, provide the required information as Strings.
Flyway flyway = new Flyway();
flyway.setDataSource("jdbc:postgresql://localhost:5433/recipes", "postgres", "postgres");
flyway.migrate();
OK, you’re almost done. The only thing that’s missing are the migration scripts. I’m reusing the SQL script from the previous example and copy it to the src/main/resources/db/migration folder. Similar to the command line client, Flyway’s API integration checks all files in that folder and uses them to update the database if necessary.
That’s it! You implemented your first database migration with Flyway. For all future updates of your application, you now just need to create an SQL script and drop it to the sql folder of the command line client or the src/main/resources/db/migration directory of your Java project.
OK, let’s quickly summarize this post and don’t forget to download your cheat sheet with the most important details of this post.
Summary
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. 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.
