Using Flyway

What is Flyway?

Flyway is a database migration tool. As of Chado 1.4, the schema is distributed and updated via Flyway.

Flyway creates a flyway_schema_history table in your Chado database. Once Flyway is configured, it will look for migrations (versioned SQL scripts) in your specified migration folder(s). These migrations are tracked in the flyway_schema_history table, and will be run in order when you run the migrate command. As the Chado schema is updated with new migrations, they can be tracked and updated with Flyway.

Note

Please read the Flyway getting started guide for more details before continuing.

Adding Flyway integration to an existing site

First, follow the instructions for installing Flyway using your distribution method of choice. You’ll need to provide a flyway.conf file which describes the connection details to your Chado database. For quick testing, place one in your home directory

Note

Flyway will search for and automatically load config files from the following paths if present:

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

For managing multiple development Chado databases on a single machine, we recommend placing your application-specific flyway.conf in the application root directory and then running Flyway commands from there.

Here is an example configuration file where the PostgreSQL server is on the same machine as Flyway (i.e. localhost) and Chado is installed inside a database named drupal and a schema named chado (a typical arrangement for a Tripal-installed instance of Chado):

flyway.url=jdbc:postgresql://localhost:5432/drupal
flyway.user=user
flyway.password=secret`
flyway.schemas=chado
flyway.locations=filesystem:/path/to/Chado/chado/migrations
flyway.validateOnMigrate=false

For your setup, replace drupal in the flyway.url setting with the name of the database where Chado is installed, and change the flyway.schemas setting to the schema where Chado is installed. If Chado was installed using the Perl-based installer this will be public.

Once Flyway is configured properly, running flyway info should report a connected database. The first step is to run flyway baseline. This tells Flyway what version of Chado to start at. Chado switched to Flyway prior to 1.4, so specifying flyway baselineVersion=1.3 baseline, or just flyway baseline (defaults to 1.0) should be appropriate for your pre-existing database. Now, running flyway info should list all of the migrations available to your database with their state as Pending.

flyway info
Flyway Community Edition 5.2.4 by Boxfuse
Database: jdbc:postgresql://localhost:5432/drupal (PostgreSQL 10.5)
Schema version: 1

+-----------+---------+-----------------------+----------+---------------------+----------+
| Category  | Version | Description           | Type     | Installed On        | State    |
+-----------+---------+-----------------------+----------+---------------------+----------+
|           | 1       |  Flyway Baseline      | BASELINE | 2018-12-20 14:31:32 | Baseline |
| Versioned | 1.3.3.001 | add stock biomat table | SQL      |                     | Pending  |
+-----------+---------+-----------------------+----------+---------------------+----------+

Updating a Chado database with Flyway

Once your migrations appear in Flyway, you can run flyway migrate to execute all new migrations. By default, Flyway will run all pending Migrations greater than your current version. Afterwards, when new updates are added to Chado, you can update the Chado codebase on your server to an official tagged release or to the development branch and run flyway migrate. Once a migration is completed, flyway info will update the State of the migration from Pending to Success.

Writing new migrations

To create a new migration, create a new .sql file in the migration folder. The file should contain plain SQL that performs the proposed changes. However, the file name is important: the first migration, for example, is V1.3.3.001__add_stock_biomat_table.sql. Subsequent migrations should follow the schema: V{version}{increment}__{description}` where: - {version}: is the current version of chado - {increment}: is a number that should be incremented for each new migration - {desrciption}: a very brief description of what the migration changes.

Please note that proposed changes to the Chado schema should follow the project contribution guidelines. You can have multiple migration folders to allow for site-specific migrations.