Overview

A typical Kill Bill deployment is comprised of several components: The core Kill Bill webapp and some plugins. Migrating the software to a higher version may require to migrate one, a few or all the different components and so potentially upgrading the database schema associated with one, a few or all of those components. The compatibility version between core Kill Bill and its plugins depends on the version of the plugin api exported by a given Kill Bill version. The KPM tool allows to list compatible plugins associated with a given Kill Bill version:

kpm info --version <killbill_version>

Kill Bill and individual plugins assume specific tables, functions, etc. are installed in your database. The source of truth for the schema (DDL) files are:

These files always contain the latest version, matching the latest code in that specific branch/tag.

To access a specific version:

Migrations with Docker images

The Migrations tooling section below goes into details on how migrations are handled for individual components, and the tooling available to you to update your database schema when upgrading Kill Bill.

If you are using our standard Docker images, the procedure is simplified and as follows:

  1. Update the /var/lib/killbill/kpm.yml file in one of your containers to reflect the new version of Kill Bill you want to upgrade to.

  2. Run $MIGRATIONS_CMD. This will:

    • Configure the database for migrations if needed (i.e. create the schema_version table(s), see below)

    • Fetch all migrations needed to upgrade to the Kill Bill version specified in the kpm.yml

    • Display the SQL that would be run (for Kill Bill and each plugin) and prompt the user whether it should be applied

Depending on your dataset size, you may or may not want the script to apply these directly (e.g. you may prefer to use an online-schema-change tool for production), in which case you can simply grab the SQL output instead.

Caveats:

  • This is only supported since killbill/killbill:0.20.10

  • Ruby migrations aren’t supported (if you have any, see the manual steps below)

Migrations tooling

In order to ease the database migrations, we decided to rely on standard tools for migrating from one given version to a higher version:

In addition to these standard tools, we also enhanced our existing tooling to support the following use cases:

  • Ability to download all the migration files associated with a migration from version N to M (see section KPM below)

  • Ability to output SQL (migration files) for production systems (see section SQL output below)

KPM

We extended (KPM with a new migrations verb to be able to download migration files that should be applied when migrating from one version to the next.

Usage:

kpm migrations github_repository from_tag to_tag

For example, to download all migration files for Kill Bill between 0.16.12 and 0.18.0:

kpm migrations killbill killbill-0.16.12 killbill-0.18.0

Similar examples for Java plugins:

kpm migrations killbill-analytics-plugin analytics-plugin-3.0.0 analytics-plugin-3.0.1
kpm migrations killbill-cybersource-plugin v4.0.1 v4.0.2

Because the implementation relies on the GitHub API, unauthenticated requests are subject to rate limiting. To work around it, go to https://github.com/settings/tokens to generate a token (default public, read-only, permissions will work) and specify it to KPM:

kpm migrations killbill killbill-0.16.3 killbill-0.16.4 --token=TOKEN

SQL output

In production environments, database access is often restricted and developers don’t necessarily have rights to execute DDL commands (i.e. CREATE, ALTER, DROP, etc. statements). To get around this, we provide scripts to get access to the SQL that needs to be run for the migrations, including the INSERT statements to update the metadata table(s).

Java

For Java code (Kill Bill and Java plugins), we publish a killbill-flyway.jar binary which is a wrapper around the flyway utility and exposes a dryRunMigrate command. Simply run it against your production database (read-only credentials are sufficient) to get the raw SQL.

Usage example:

# /var/folders/tmp/migrations is the directory where the migration files were downoaded (using kpm migrate)
java -jar killbill-flyway.jar -locations=filesystem:/var/folders/tmp/migrations -url='jdbc:mysql://DATABASE_IP:DATABASE_PORT/DATABASE_NAME' -user=<USERNAME> -password=<PASSWORD> dryRunMigrate

Ruby

For Ruby code, we provide a killbill-migration script with the killbill gem (JRuby is recommended, see our installation instructions).

Besides outputting the SQL, it can also run the migrations.

Usage examples:

killbill-migration current_version cybersource
killbill-migration sql_for_migration cybersource --path=XXX
killbill-migration migrate cybersource --path=XXX
killbill-migration ruby_dump cybersource
killbill-migration sql_dump cybersource

Options --username, --password, --database and --host are supported.

If you are a plugin developer, these commands are also available as part of the default Rake tasks of the plugin itself, for example:

PLUGIN_NAME=paypal_express USERNAME=root PASSWORD=root DB=killbill rake killbill:db:sql_for_migration
PLUGIN_NAME=paypal_express USERNAME=root PASSWORD=root DB=killbill rake killbill:db:migrate

Note that the PLUGIN_NAME variable needs to match the prefix of the schema_migrations table.

Kill Bill

Starting with Kill Bill 0.16.4, SQL migrations files for Flyway are provided. They can be found on a per-module basis under */src/main/resources/org/killbill/billing/*/migration/. The versioning is based on the creation timestamp (i.e. date +'%Y%m%d%H%M%S') and must be unique for each file so as to indicate the ordering (what Flyway calls the version_rank).

Baseline

For Flyway migrations to work correctly, there is an initial baseline operation that needs to happens so as to create the metadata table, called schema_version. In the scenario where a developer can run its own migrations, the following command would be run:

flyway -url='jdbc:mysql://DATABASE_IP:DATABASE_PORT/DATABASE_NAME' -user=<USERNAME> -password=<PASSWORD> baseline

In the production scenario, the schema_version along with the initial version would have to be created manually:

CREATE TABLE `schema_version` (
  `installed_rank` int(11) NOT NULL,
  `version` varchar(50) DEFAULT NULL,
  `description` varchar(200) NOT NULL,
  `type` varchar(20) NOT NULL,
  `script` varchar(1000) NOT NULL,
  `checksum` int(11) DEFAULT NULL,
  `installed_by` varchar(100) NOT NULL,
  `installed_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `execution_time` int(11) NOT NULL,
  `success` tinyint(1) NOT NULL,
  PRIMARY KEY (`installed_rank`),
  KEY `schema_version_s_idx` (`success`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into schema_version (installed_rank, version, description, type, script, installed_by, installed_on, execution_time, success) VALUES (1, 1, '<< Flyway Baseline >>', 'BASELINE', '<< Flyway Baseline >>', 'admin', NOW(), 0, 1);

Migration from version N to M

A typical migration from version N to M will require to first identify the set of migrations files that should be applied and then either apply them using Flyway commands or manually (production use case).

# Will download migration files in a temporary folder (e.g /var/folders/XXX)
kpm migrations killbill killbill-N killbill-M --token='GITHUB_TOKEN'

# If using flyway is an option
flyway -url='jdbc:mysql://DATABASE_IP:DATABASE_PORT/DATABASE_NAME' -user=<USERNAME> -password=<PASSWORD>  -locations=filesystem:/var/folders/XXX migrate

Java plugins

Java plugins migrations also rely on Flyway and follow a similar workflow than Kill Bill itself. They can be found in each plugin under src/main/resources/migration/.

The metadata table should be called <plugin_name>_schema_version. Make sure to specify -table=<plugin_name>_schema_version when invoking Flyway.