Flyway Database migrations with Commandline Tool

In this blog post I will demonstrate how to do database migrations using Flyway command line tool.

In my one of previous blog post , I have explained about what is database migration and how you can perform database migrations in spring boot application with flyway.

This blog post I will demonstrate how to do database migrations using command line tool provided by Flyway

The Flyway command-line utility is a standalone release of the Flyway software. It is designed for customers who want to migrate their databases from the command line without having to incorporate Flyway into their programmes or install a build tool. It operates on Windows, macOS, and Linux.

The Flyway CLI allows you to execute database migrations with Flyway from the command line. This is useful for testing and debugging large migrations, as well as providing a way to generate migrations without needing to run the full build.

Using Flyway command-line tool specially helpful when database administrators are managing the database migrations.

Download and installation

You can download windows version of command line tool from here.

command line tool is also available for Mac and Linux. You can download software from flyway website

Once downloaded, extract the folder inside and directory to the `PATH` to make the `flyway` command available from anywhere on the system.

Directory structure

flyway-9.0.4 
│
└───conf
│   │__ flyway.conf     <- configuration file
│___ drivers            <- JDBC drivers
│___ jars               <- Java-based migrations (as jars)   
│___ jre                
│___ lib          
│___ licenses       
│___ sql               <- SQL migrations
│___flyway.cmd         <- Windows executable   

    

JDBC drivers

In order to connect with the database, Flyway needs the appropriate JDBC driver to be available in its drivers directory.

Flyway command line tool already ships with all major database drivers.If Flyway does not ship with the JDBC driver, you will need to download the driver and place it in the drivers directory yourself.

Configuration

The Flyway Command-line tool can be configured in a wide variety of ways. You can use config files, environment variables and command-line parameters. These many setup methods can be mixed at will.

Config files

Config files are supported by the Flyway command-line tool.Flyway will search for and automatically load the following config files if present.

  • <install-dir>/conf/flyway.conf
  • <user-home>/flyway.conf
  • <current-dir>/flyway.conf

It is also possible to point Flyway at one or more additional config files. This is achieved by supplying the command line parameter -configFiles= as follows:

flyway -configFiles=path/to/myConfig.conf migrate
Code language: Shell Session (shell)

Alternatively you can also use the FLYWAY_CONFIG_FILES environment variable for this. When set it will take preference over the command-line parameter.

export FLYWAY_CONFIG_FILES=path/to/myConfig.conf,myConfig2.conf flyway migrate
Code language: Shell Session (shell)

Environment Variables

To make it easier to work with cloud and containerized environments, Flyway also supports configuration via environment variables.

Command-line Arguments

Finally, Flyway can also be configured by passing arguments directly from the command-line:

flyway -user=postgress -url="jdbc:postgresql://localhost:5432/eis" migrate
Code language: Java (java)

Configuration from standard input

You can provide configuration options to the standard input of the Flyway command line, using the ` -configFiles=-` option. Flyway will expect such configuration to be in the same format as a configuration file.

flyway migrate -configFiles=-
Code language: Shell Session (shell)

Overriding order

If multiple configurations are available configuration settings are loaded in the following order

  1. Command-line arguments
  2. Environment variables
  3. Standard input
  4. Custom config files
  5. <current-dir>/flyway.conf
  6. <user-home>/flyway.conf
  7. <install-dir>/conf/flyway.conf
  8. Flyway command-line defaults

Flyway command usage.

Now let’s see how to use the flyway command-line tool.

First create a directory and navigate to that directory.

md databasemigration cd databasemigration
Code language: Shell Session (shell)

Next Let’s create a conf file flyway.conf which contains Jdbc url, user name, password ,location of database migrations.

Marinating conf files along with database migrations 2 advantages.

  • You can maintain the conf file in version control system
  • when dealing with multiple databases, you do need to switch between conf files
flyway.url= jdbc:postgresql://localhost:5432/eis flyway.driver=org.postgresql.Driver flyway.user=postgres flyway.password=admin flyway.locations=filesystem:./
Code language: plaintext (plaintext)

Note

As explained in above sections , you can also provide config properties from command line. If you do not want to maintain sensitive properties like password, it is better to provide from the command line.

Migration file

Let’s create SQL based migration file and place them in databasemigration folder.

create sequence hibernate_sequence start with 1 increment by 1; create table department (id integer not null, name varchar(255), primary key (id)); create table employee (id integer not null, birth_date date, email varchar(255), first_name varchar(255), gender varchar(255), hire_date date, last_name varchar(255), salary numeric(19,2), department_id integer, primary key (id));
Code language: SQL (Structured Query Language) (sql)

V1__Initail_Schema.sql

ALTER TABLE EMPLOYEE ADD ADDRESS VARCHAR(255) ; ALTER TABLE EMPLOYEE ADD CONSTRAINT FK_EMPLOYEE_DEPARTMENT FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENT (ID);
Code language: SQL (Structured Query Language) (sql)

V2__Add_Address_Emp_Table.sql

command-line tool supports following commands

NameDescription
migrateMigrates the database
cleanDrops all objects in the configured schemas
infoPrints the details and status information about all the migrations
validateValidates the applied migrations against the ones available on the classpath
baselineBaselines an existing database, excluding all migrations up to and including baselineVersion
repairRepairs the schema history table

migrate

Migrates the schema to the latest version. Flyway will create the schema history table automatically if it doesn’t exist.

usage

flyway [options] migrate
Code language: Shell Session (shell)

Info

Prints the details and status information about all the migrations.

flyway [options] info
Code language: Java (java)

> flyway info Database: jdbc:postgresql://localhost:5432/eis (PostgreSQL 13.4) Schema version: 2 +-----------+---------+-----------------------+------+---------------------+---------+ | Category | Version | Description | Type | Installed On | State | +-----------+---------+-----------------------+------+---------------------+---------+ | Versioned | 1 | Initail Schema | SQL | 2022-07-30 21:38:56 | Success | | Versioned | 2 | Add Address Emp Table | SQL | 2022-07-30 21:38:56 | Success | +-----------+---------+-----------------------+------+---------------------+---------+
Code language: Java (java)

Let’s add new migration file and run info command again.

create table address (id integer not null, addressline1 varchar(255), addressline2 varchar(255),city varchar(50), primary key (id));
Code language: SQL (Structured Query Language) (sql)

V3__Address_Table.sql

If you run the info command now, it will show new migration as pending as it is not yet applied on database.

> flyway info Database: jdbc:postgresql://localhost:5432/eis (PostgreSQL 13.4) Schema version: 2 +-----------+---------+-----------------------+------+---------------------+---------+ | Category | Version | Description | Type | Installed On | State | +-----------+---------+-----------------------+------+---------------------+---------+ | Versioned | 1 | Initail Schema | SQL | 2022-07-30 21:38:56 | Success | | Versioned | 2 | Add Address Emp Table | SQL | 2022-07-30 21:38:56 | Success | | Versioned | 3 | Address Table | SQL | | Pending | +-----------+---------+-----------------------+------+---------------------+---------+
Code language: plaintext (plaintext)

validate

Validate applied migrations against resolved ones (on the filesystem or classpath) to detect accidental changes that may prevent the schema(s) from being recreated exactly.

Validation fails if

  • differences in migration names, types or checksums are found
  • versions have been applied that aren’t resolved locally anymore
  • versions have been resolved that haven’t been applied yet
flyway [options] validate
Code language: Java (java)
> flyway validate Database: jdbc:postgresql://localhost:5432/eis (PostgreSQL 13.4) Successfully validated 2 migrations (execution time 00:00.028s)
Code language: Java (java)

If there are some accidental changes to already applied migrations

You will see error message like below

> flyway validate Database: jdbc:postgresql://localhost:5432/eis (PostgreSQL 13.4) Automate migration testing for Database CI with Flyway Hub. Visit https://flywaydb.org/get-started-with-hub ERROR: Validate failed: Migrations have failed validation Migration checksum mismatch for migration version 2 -> Applied to database : 387136746 -> Resolved locally : 1691182695 Either revert the changes to the migration, or run repair to update the schema history.
Code language: Java (java)

repair

Repairs the Flyway schema history table. This will perform the following actions:

  • Remove any failed migrations on databases without DDL transactions
    (User objects left behind must still be cleaned up manually)
  • Realign the checksums, descriptions and types of the applied migrations with the ones of the available migrations
  • Mark all missing migrations as deleted
    • As a result, repair must be given the same locations as migrate
flyway [options] repair
Code language: Java (java)

In below output you can see that repair command fixed checksum issue.

Database: jdbc:postgresql://localhost:5432/eis (PostgreSQL 13.4) Repair of failed migration in Schema History table "public"."flyway_schema_history" not necessary. No failed migration detected. Repairing Schema History table for version 2 (Description: Add Address Emp Table, Type: SQL, Checksum: -756122151) ... Successfully repaired schema history table "public"."flyway_schema_history" (execution time 00:00.044s).
Code language: plaintext (plaintext)

If there are no issues output will look like below.

baseline

Baselines an existing database, excluding all migrations up to and including baselineVersion

This concept is used introduce the flyway migrations to existing database.

First create the migration file which include all the current database changes and run the

To use baseline concept, you need to use 2 properties

flyway.baselineVersion=1 <- set the version based on your file version. flyway.baselineOnMigrate=true
Code language: Java (java)
> flyway [options] baseline

once your run the baseline command, it will create flyway_schema_history will be created and you will see the baseline version set.

> flyway baseline Database: jdbc:postgresql://localhost:5432/eis (PostgreSQL 13.4) Creating Schema History table "public"."flyway_schema_history" with baseline ... Successfully baselined schema with version: 1
Code language: Java (java)

clean

Drops all objects (tables, views, procedures, triggers, …) in the configured schemas.

By default clean command will be disbaled.

If you want to enable, you need to add following property to conf file.

flyway.cleanDisabled=false
Code language: Java (java)