DB Migration - Flyway

Owned by Daniel McMillen (Unlicensed)
Last updated: Sept 20, 2016
2 min read

Overview

Flyway is a database migration tool. It provides a system for recreating/migrating a database consistently and keeping track of the past and present state of the database. It stores this meta data in a table called `schema_version`.

We will be using the command line execution mode.

You can find more about it at https://flywaydb.org/ .

Commands

  • `migrate` - Migrates the schema to the latest version. Flyway will create the metadata table automatically if it doesn't exist.
  • `clean` - Drops all objects (tables, views, procedures, triggers, ...) in the configured schemas. The schemas are cleaned in the order specified by the schemas property.
  • `info` - Prints the details and status information about all the migrations.
  • `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. 
  • `baseline` - Baselines an existing database, excluding all migrations up to and including baselineVersion.
  • `repair` - Repairs the Flyway metadata table. 

These are all of the commands that are available.

Most common commands that people run

  • `./flyway clean migrate info` to drop all, recreate, and then display schema version info
  • `./flyway migrate info` to recreate and then display schema version info.

Configurations

Flyway will automatically look for a file called flyway.conf. This file is usually in `<install-dir>/conf/flyway.conf`

Command-line parameters override Configuration files.

Most notable ones are:

  • `flyway.url` - Jdbc url to use to connect to the database.
  • `flyway.user` - User to use to connect to the database. Flyway will prompt you to enter it if not specified.
  • `flyway.password` -  Password to use to connect to the database. Flyway will prompt you to enter it if not specified.
  • `flyway.schemas` - Comma-separated list of schemas managed by Flyway. These schema names are case-sensitive.

Conventions

Flyway has a strict naming convention when it comes to deciding when/how to run SQL migrations. The name of the SQL file matters!

Here is a sample file name `V1_0001__create_db.psql` the structure being prefixVERSIONseparatorDESCRIPTIONsuffix.

  • Prefix is `V`
  • Version number `1.0001`
  • Separator is `__`
  • Suffix is `psql`

If the SQL file in question is not formatted as such, Flyway will not pick up the file to run.

Contributing

DMC has two distinct Flyway migrations

  • Core - Any core functionality or data needed needed for the application to run.
  • Data - Any test data that would be loaded.

In our SQL directory we have a core and data sub directory; please put your migrations in the appropriate place.

In order to run core or data, you will need to point to the appropriate flyway.conf file.

For example standing up a brand new DB with test data you would run the following:


  1. ./flyway clean migrate info -configFile=conf/core/flyway.conf

  2. ./flyway migrate info -configFile=conf/data/flyway.conf