Flyway - schema/data migration

Owned by Karel Kovářík
Last updated: Aug 21, 2017 by Petr Jůza

OpenHub database schema is managed by Flyway.

It was selected because it is a simple tool that quite successfully addresses the issue of evolving DB schema. Typical problem of evolving applications with usage of relational databases. Flyway works on granularity of SQL files, that are automatically applied based on current schema on running database. Scripts are numbered by convention.

In OpenHub, these scripts are added for both currently supported DB engines - PostgreSQL & H2. By default, DB migration using flyway is enabled. If developer checks out the project, schema is created during application startup by Flyway. If any changes are introduced in form of new SQL script, flyway will apply them - scripts can be DDL or data migrations (if they make sense).

Prerequisite for successful flyway run with PostgreSQL:

  • there is user/role (e.g. openhubusr)
  • there is database (e.g. openhubdb)
  • there is schema openhub

See db/migration/postgresql/V1_0_0__schema_init.sql init script for more details.

If you need to use not concurrent scheduled jobs with Quartz then you must create database structure manually from GitHub scripts.


Enabling/disabling flyway in application.properties

flyway.enabled = true/false

For more configuration properties, see application.properties in OpenHub source.

Manual db schema creation/update

Flyway does mainly simplify development. On production deployment you may want to manually create or update schema - for example with more priviliged user than is used running the application. Some options:

Migrate tool from flyway

Flyway provides command-line tool that can be used to perform migration. It does setup metadata table as well, so after migration using this tool, even with enabled flyway it should be perfectly valid like done automatically. See https://flywaydb.org/documentation/commandline/ for more info.

Manual SQL scripts

You can sure just disable flyway and just run OpenHub SQL scripts. Downside would be missing metadata table, so you would have to keep track of applied/not applied scripts by your self. Combined approach is possible: apply all scripts, then setup flyway with baseline (flyway.baseline-on-migrate = true & flyway.baseline-version = XXX) to create metadata table with given version. If any new scripts are added, flyway will migrate only on top of baseline version, or fail trying.

Backups

There are also tables and functions for archivation of "production" tables for PostgreSQL. Archivation tables have prefix "archive_", e.g. archive_external_call

Function for archivation is called archive_records(integer) where input is number of months after that the message will be archived.