Flyway command-line easy setup

One of the reasons for adopting Flyway command-line was the easy setup process (no need to install). First, I will show how to setup Flyway for a single database, i.e., as if have only one database in your server, and than the setup for multiple databases in a server.

Single database

You can download Flyway command-line here.

After download and extract you have the following folders/files structure:

flyway_sctructure

In this case it’s only necessary to work the “flyway.conf” file (you can find it in conf folder):

  1. Set url to target server/database
  2. Set user and password to target server/database

Alternatively you don´t need to set user and password on the configuration file, it can be provided as argument.

Multiple databases

Because numbers matter! If you have two or more databases in your server, a option is to apply the single database setup for every database. This approach will lead to an unnecessary files multiplication. As alternative we can create a centralized folder dedicated to the Flyway application.

  1. Extract Flyway for a folder and change the name of the folder for flyway;
  2. Create a folder each one of your databases (MyDatabase1, MyDatabase2, …);
  3. Create the folder flyway_conf and copy the file flyway.conf inside;
    1. For flyway.conf file set url, user and password for the target database;
    2. Uncomment the locations configuration and set his value with “filesystem:.” (flyway.locations=filesystem:.). This means that Flyway will scan recursively for migrations the folder that contains the flyway.cmd (MyDatabase1 for example);

  1. Create the file flyway.cmd with the following code inside:

  1. After this you can execute the flyway command from each database.

And that’s it! You are ready for manage the changes of multiple databases.

Multiple servers

If you have multiple servers like: dev, test, staging, … the solution is to create a flyway.cmd and flyway.conf for each server/environment.

  1. Inside flyway/flyway_conf folder create a flyway_servername.conf file for each server (flyway_dev.conf, flyway_test.conf) and set the url, user and password for the target server and database;
  2. Create a flyway_servername.cmd file for each server (flyway_dev.cmd, flyway_test.cmd). Muke sure that you are using the correct configuration file;
    1. Inside of each cmd file should be the following code:

You just have to execute the flyway command for the intended server/environment.

User and Password

Because we want to source control all migrations and Flyway related files, saving the user name and the password in plain text in the configuration file it’s not such a good idea. So, where’s a solution:

  1. Remove your user name and password from your configuration file;
  2. Create the folder “C:\Program Files (x86)\flyway” and inside this folder create the file flyway_ep_dev.cmd
  3. The file flyway_ep_dev.cmd should contain the following code:

  1. Add the folder “C:\Program Files (x86)\flyway” to you PATH.

This way you just have removed your credentials from you source control. You should run the new cmd file from your target database.

Flyway: “Hello database migrations”

flyway-logo

Flyway is a open source database migration tool that allows you to manage database changes using migrations. Last week the version 4.0.1 has been released and I decided to write my first post about Flyway.

I started to use Flyway command-line almost 3 years ago (version 2.2.1) and the main reason that took me to use and keep using nowadays is: it’s simplicity, “database migrations made easy”. This key factor is translated into the following:

  • Zero dependencies (you need java and your jdbc driver)
    • You can download the version that already includes both: java and the driver;
    • This is a key factor for the easy setup process;
  • Easy to setup, no need to install
    • You just have to configure Flyway: target server, migrations location, etc;
    • This makes the deploy process extremely easy;
  • The scripts are written in SQL
    • You, or your team, do not have to learn or use a different language to create migrations.

Flyway commands

Flyway provides 6 basic commands:

  • migrate
    • Apply all migrations until the latest version, or until a specific target version;
    • If the metadata table doesn’t exist this command will create it automatically;
    • This is they key command of the Flyway workflow;
  • clean
    • Drops all database objects in the configured schemas;
    • This command should be used with caution, especially in system databases or in production environment;
    • Its useful in development and test environments enabling a fresh start  cleaning completely your database;
  • info
    • Gives the current status information about all the migrations;
    • This is done by checking the migrations scripts against the metadata table;
    •  Allows you to know if a migration was applied with success, or still pending or was ignored;
  • validate
    • Validates applied migrations against the available migration scripts on your folder;
    • Allows to validate if all migrations were applied, i.e. do not exist pending migrations;
    • Allows to validate if a migration script was change after being successfully applied;
      • This validation is made trough checksum validation;
      • Allows to reliably recreate the database schema;
  • baseline
    • Allows to set the baseline of an existing database;
    • All migrations upto and including the baseline version will me ignored;
    • If the metadata table doesn’t exist this command will create it automatically;
  • repair
    • This command repairs the metadata table;
    • Remove migrations from the table marked as failed;
    • Realign the checksums of the applied migrations to the ones of the available migrations

flyway_commands

Flyway configuration

The Flyway configuration can be specified in two ways:

  1. In the configuration file: flyway.conf
  2. In the command-line using the format “-key=value” (this way overrides the previous one)

The following picture shows all available configuration options for Flyway version 3.2.1:

flyway_options

Flyway metadata table

The metadata table is used to track the state of the database. Allows to know which migrations have already been applied, when were applied and by whom. Additionally also tracks migration checksums.

flyway_metadata_table

The default name of the metadata table is “schema_version”. If the database is empty and the metadata table do not exist, Flyway will create it automatically.

Flyway scans the migrations directory and check migrations against the metadata table. Migrations are sorted based on their version number and applied in order.

Flyway command-line structure

When you download and extract Flyway command-line you will find the following structure:

flyway_strucuture

  • conf
    • In the folder you will find the configuration file “flyway.conf”
  • drivers
    • This folder contains the jdbc drivers
  • jars
    • In this folder you can add java migrations
  • lib
    • This folder contains Flyway jar files
  • sql
    • In this folder you can add SQL migrations
  • flyway.cmd
    • File responsible for executing Flyway (Windows command script)