Flyway: Database Versioning

Flyway is an open-source tool database versioning 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.

By default flyway creates table called flyway_schema_history, to maintain it’s meta data.

Your app will continue to work fine. It’s just a MySQL warning.

Prequisite

Flyway is created in Java. Therefore Java 1.8 + is needed as pre-requisite.

Installation

It’s very straight forward. Download Flyway from here. Unzip the file and place it to your choice of location. It too includes MySQL JDBC driver as well. So, you don’t have to download the driver saperately. It is available for number of platform. Download it, as per your requirement.

Configuration

You can integrate flyway into your application or you can run it automatically as part the of the build process or manually from the command line.

Edit conf/flyway.conf configuration file and modify three important parameters.

All the migrations are stored under sql directory.

# JDBC url to use to connect to the database
flyway.url=jdbc:mysql://192.168.150.214:3306/inventory?useSSL=false

# User to use to connect to the database. Flyway will prompt you to enter it if not specified.
flyway.user=flyway_inventory

# Password to use to connect to the database. Flyway will prompt you to enter it if not specified.
flyway.password=Abc@123

Commands

Flyway supports the following basic commands to manage database migrations using the command line.

# Clean: Drops all objects in a configured schema. All database objects are dropped. Of course, 
you should never use clean on any production database.
# ./flyway clean
Flyway Community Edition 5.2.4 by Boxfuse
Database: jdbc:mysql://192.168.110.224:3307/inventory (MySQL 5.7)
Successfully cleaned schema `inventory` (execution time 00:00.022s)

# Baseline: Baselines an existing database, excluding all migrations, including baselineVersion. Baseline helps to start with Flyway in an existing database. 
Newer migrations can then be applied normally.
# ./flyway -baselineVersion=2.0.0 baseline
Flyway Community Edition 5.2.4 by Boxfuse
Database: jdbc:mysql://192.168.110.224:3307/inventory (MySQL 5.7)
Creating Schema History table: `inventory`.`flyway_schema_history`
Successfully baselined schema with version: 2.0.0

# Info: Prints current status/version of a database schema. It prints which migrations are pending, which migrations have been applied, 
what is the status of applied migrations and when they were applied.
# ./flyway info
Flyway Community Edition 5.2.4 by Boxfuse
Database: jdbc:mysql://192.168.110.224:3307/inventory (MySQL 5.7)
Schema version: 2.0.0
+-----------+---------+-----------------------+----------+---------------------+----------+
| Category  | Version | Description           | Type     | Installed On        | State    |
+-----------+---------+-----------------------+----------+---------------------+----------+
|           | 2.0.0   | << Flyway Baseline >> | BASELINE | 2019-03-11 19:26:05 | Baseline |
| Versioned | 2.0.1   | create person table   | SQL      |                     | Pending  |
+-----------+---------+-----------------------+----------+---------------------+----------+

# Migrate: Migrates a database schema to the current version. It scans the classpath for available migrations and applies pending migrations.
# ./flyway migrate
Flyway Community Edition 5.2.4 by Boxfuse
Database: jdbc:mysql://192.168.110.224:3307/inventory (MySQL 5.7)
Successfully validated 2 migrations (execution time 00:00.020s)
Current version of schema `inventory`: 2.0.0
Migrating schema `inventory` to version 2.0.1 - create person table
Successfully applied 1 migration to schema `inventory` (execution time 00:00.262s)

# ./flyway info
Flyway Community Edition 5.2.4 by Boxfuse
Database: jdbc:mysql://192.168.110.224:3307/inventory (MySQL 5.7)
Schema version: 2.0.1
+-----------+---------+-----------------------+----------+---------------------+----------+
| Category  | Version | Description           | Type     | Installed On        | State    |
+-----------+---------+-----------------------+----------+---------------------+----------+
|           | 2.0.0   | << Flyway Baseline >> | BASELINE | 2019-03-11 19:26:05 | Baseline |
| Versioned | 2.0.1   | create person table   | SQL      | 2019-03-11 19:31:27 | Success  |
+-----------+---------+-----------------------+----------+---------------------+----------+

# Validate: Validates current database schema against available migrations.
# ./flyway validate
Flyway Community Edition 5.2.4 by Boxfuse
Database: jdbc:mysql://192.168.110.224:3307/inventory (MySQL 5.7)
Successfully validated 2 migrations (execution time 00:00.020s)

# Repair: Repairs metadata table.

Conclusion

Successfully, setup flyway. Whooo …..

Leave a Reply

Your email address will not be published. Required fields are marked *