Hi, I’m Thorben Janssen from thoughts-on-java.org. Today, I want to give you a quick introduction to version-based database migration with Flyway. 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 changes. One of these tools is Liquibase which I explained in one of my previous videos. I will add the link to it to the description below. Another one is Flyway which I want to show you now. 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. 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, For example you read information from a BLOB and store it in a new set of columns, you can do that with Java. Let’s stick to the most common approach and use the following SQL statements to create a small database. This is 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. I store these SQL statements in a file called V1__create_database. The file name follows Flyway’s default naming convention: The letter V followed by the version number, 2 underscores and a short description. 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. That’s all you need to do to define your first database migration. You now just have to trigger it. 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 video, I want to show you the command line client and the Java integration. The command line client is easy to use. You just need to download the latest version from https://flywaydb.org, And I will add the link to it to the description 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, conf and sql. In its default configuration, Flyway processes all SQL files located in the sql folder. So, you should put you V1__create_database.sql file there. And the conf folder contains the flyway.conf configuration file. 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 and 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. 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. 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. 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. 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 use a connection pool and, therefore, provide the required information as Strings. OK, you’re almost done. The only thing that’s missing is the migration script. 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. Let’s quickly summarize this video and don’t forget to download your cheat sheet with the most important details of this video. 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 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. OK, that’s it for today. If you want to learn more about Hibernate, you should join the free Thoughts on Java Library. It gives you free access to a lot of member-only content like a cheat for this video and an ebook about using native queries with JPA and Hibernate. I’ll add the link to the video description below. And if you like today’s video, please give it a thumbs up and subscribe below. Bye!
