Flyway Command Line Migration (2024)

What is Flyway?

Flyway is an open source database migration software. It has Migrate, Clean, Info, Validate, Undo, Baseline, and Repair seven basic commands, SQL (database-specific syntax (such as PL/SQL, T-SQL, etc.) or Java migrations are supported (for advanced data transformations or dealing with LOBs).

Spring Boot integrates with Flyway, one of the most extensively used database migration tools, to make database migrations easier.

Flyway is a tool that allows you to version control incremental changes to your database such that you can easily and completely migrate to a new version.

In this tutorial, we will learn how to download Flyway Command Line Client to handle database migrations.

Download Flyway Command Line Client Tool

The Flyway Command Line Client Tool is user-friendly. The latest version can be downloaded from https://flywaydb.org/download.

And then downloaded archive can be extracted into your local file system.

We only need to edit files from 2 folders i.e conf and sql folder from the extracted archive.

Database Support

Flyway supports below Database as mentioned in the flyway.conf file.

# Aurora MySQL
# Aurora PostgreSQL
# CockroachDB
# DB2*
# Derby
# Firebird
# H2
# HSQLDB
# Informix*
# MariaDB
# MySQL
# Oracle
# Oracle (TNS)**
# PostgreSQL
# SAP HANA*
# Snowflake*
# SQL Server
# SQLite
# Sybase ASE
# Redshift*

Database Connection Details

The conf folder has configuration file flyway.conf, edit the file and update with with your database connection details.

flyway.url=jdbc:mysql://localhost/techgeeknextFlywaydb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
flyway.user=root
flyway.password=root

SQL Migration Scripts

Flyway handles all SQL files from the sql folder default configuration. You should therefore copy the .sql database files in this folder.

- V[VERSION_NUMBER__[NAME].sql is a naming standard that must be followed by all migration scripts. To understand more about the naming convention, refer the Official Flyway Documentation.

Create Employee Table Schema

Now, inside the sql folder, create a new file named V1__init.sql and add the following sql scripts:

CREATE TABLE employees (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  name varchar(100) NOT NULL,
  role varchar(50) NOT NULL,
  project varchar(50) DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY UK_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create a new version2 migration script V2__employeedata.sql under same folder sql to watch how Flyway automatically migrates the database to the new version.

INSERT INTO employees(name, role, project) VALUES('techgeeknext-user1', 'Developer', 'Flyway-Project1');
INSERT INTO employees(name, role, project) VALUES('techgeeknext-user2', 'SME', 'Flyway-Project2');

We have created migration script under sql folder as shown below:

flyway migrate

Now we will execute the flyway migrate command.
Run flyway info command to see the all database changes.
Also, we can notice two tables created in Mysql database: one for employees and another for the fly database named flyway_schema_history.
In the table, it keeps track of the current migration's version, script file name, and checksum, among other things.

If you make changes to V1__init.sql after the migration is complete, Flyway will complain that the checksums don't match.