In this blog, I’m going to give overview of flyway db & show you how to use Flyway db migration for best practices in Java project.

Overview:

Flyway is a convenient database versioning as well as migration tool. 

• Flyway is an open-source database migration tool that supports simplicity and convention over configuration.
• It enables developers to apply version control practices to the database.
• It migrates the database on application setup.
• It integrates with Maven. But one of its biggest assets is the ability to run both SQL migration scripts and Java migration scripts.

What problems we are facing currently without flyway:

Currently we are not doing anything for database. Many projects still rely on manually applied sql scripts. Due to that 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 migration is 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
• Checking the current version of the database and by applying new migrations automatically before the rest of the application starts.
• Migrate in a deterministic way from your current version of the database to a newer one.

Commonly faced issues with flyway

• Flyway is strict when it comes to migration file changes. It’s not easy to change a migration after it’s been checked in. Changing a file’s content or name can cause a migration failure on every machine with a previous version of the file.
• Even if a simple change needs to be made — such as adjusting a field name — a new migration script is usually required. Like If I add updated script into already executed file then migration will fail. We need to create separate file for this.
• When team members are working in parallel on different branches, version numbers can clash.

Why use Flyway?

• Simple – Easy to setup, simple to master. Flyway lets you regain control of your database migrations with pleasure and plain sql.
• Migrate from any version (incl. an empty database) to the latest version of the schema
• Problems with the database schema mismatch solved completely. It useful when we don’t have a single production database, but every client has their own DB instance. Managing DB versions in such circumstances with flyway is very helpful.
• Focused – Flyway migrates your database, so you don’t have to worry about it anymore.
• Powerful -Flyway migrate your database on application start-up.
• flyway_schema_history table through which it keeps track of various information regarding the applied scripts: when it was applied, by whom it was applied, description of the migration applied, checksum etc. The content looks like:

Problem without flyway

Below is the code integration structure:

• Version control is good tool.
• We have reproducible builds and continuous integration.
• We have well defined release and deployment processes.

But what about the database? We don’t have idea about exact state of database.

In above example we have code versions. But don’t have database version. Hence to keep track of database version we use flyway db tool.

How Flyway works

This is how Flyway points 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.

Then we 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.

After that Flyway will begin scanning the file system 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

Below is diagrammatic representation of how it works.

Flyway also follows the naming convention. Below are rules for file name:

V<VERSION>__<DESCRIPTION>.<FORMAT>

# Where <VERSION> is a numeric value that can contain a point (“.”) or an underscore (“_”) for example 3, 3.1, 3_1 are all valid for the version. (Not that if an underscore is used, it would be turned into a dot “.” at runtime by Flyway).

# the double underscore, __ is what is used to separate the   version number from the description.

# <DESCRIPTION> is a short description of what the scripts contained in the file is about.

# For the <FORMAT>, you can have either sql or java depending on the method being used to supply the migrations.

E.g.: V1.1__Create_Table.sql

V2019.12.04.1200__Insert_MasterData.sql

We should use these tools to achieve

• version control for all database artifacts
• auditing database model change
• to get everybody in the team their own database deployment
• prevent deployments where the database is out of sync with the application
• create new environments
• get developers continuously integrate their database code

Other tools for migration:

• Liquibase – For version control
• Evolve (database migration) – For CI,  migration & version control & used for .Net
• DbPatch – For version control 

Configuration steps: XML based & Java based configuration

Note: If application is xml based then used xml configuration. If it is java based then use java configuration.

1.  Add below dependency in pom.xml

<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-core</artifactId>
<version>${Flyway.version}</version>
</dependency>

2.  Configuring Flyway to integrate into spring’s Container

The next steps would be the configuration needed to have spring pick up Flyway as a managed bean and have it play nicely with other beans like the entity manager factory.

XML configuration:

<!-- Flyway configuration -->
<beanid="migrationVersion"class="org.flywaydb.core.api.MigrationVersion"factory-method="fromVersion">
<constructor-argvalue="1.0"/>
</bean>

<bean id="flyway"class="org.flywaydb.core.Flyway"init-method="migrate"depends-on="dataSource,log4jInitialization">
<property name="dataSource"ref="dataSource"/>
<property name="cleanDisabled"value="true"/>
<property name="baselineOnMigrate"value="true"/>
<property name="placeholderPrefix"value="@{"/>
<property name="placeholderSuffix"value="}"/>
<property name="baselineVersion"ref="migrationVersion"/>
<property name="locations"
value="classpath:/database/migrations/sql/initial,classpath:/database/migrations/sql/updates"/>
<property name="outOfOrder"value="true"/>
<property name="validateOnMigrate"value="true"/>
</bean>

Java Configuration:

@Configuration
public class AppConfig {

@Bean
@DependsOn("dataSource")
public Flyway flyway() {
    Flyway flyway = new Flyway();
    flyway.setDataSource(dataSource());
    flyway.setCleanDisabled(true);
    flyway.setBaselineOnMigrate(true);
    flyway.setPlaceholderPrefix("@{");
    flyway.setPlaceholderSuffix("}");
    flyway.setBaselineVersion(MigrationVersion.fromVersion("1.0"));
    flyway.setLocations(
"classpath:/database/migrations/sql/initial",  "classpath:/database/migrations/sql/updates");
    flyway.setOutOfOrder(true);
    flyway.setValidateOnMigrate(true);
    flyway.migrate();
    return flyway;
  }

@Bean
  public DataSource dataSource() {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName(driverClassName);
    dataSource.setUrl(url);
    dataSource.setUsername(username);
    dataSource.setPassword(password);
    return dataSource;
  }
}

Some additional things to take note of in the configurations:
1. The init method is used to initialize flyway. (Which was done using the init-method property in xml and initMethod property of the @Bean annotation in JavaConfig)

2. After initialize flyway the migrate() method is responsible for performing the migration logic: that is finding the migration scripts, applying them and keeping a tab of successful migrations.

3. The baseLineOnMigrate is used for the first time and no flyway_schema_history table exists. It instructs Flyway that before the migration scripts are applied, it should create a migration entry within the flyway_schema_history table which would serve as the baseline, and thus the available migration script would only be applied if their version is higher than the baseline version.

4. The dataSource is the reference of the datasource bean that you provide to the entityManagerFactory bean.

5. The last item of interest in the configuration is the location through which you specify where Flyway should find the migration scripts.

Summary:

Flyway is a flexible and powerful database migration tool. 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.

References:

https://flywaydb.org/

https://flywaydb.org/documentation/faq

https://dzone.com/articles/database-versioning-with-flyway-and-java