CAPI Migration Style Guide

Overview

Cloud Controller supports both MySQL and PostgreSQL databases. We need to make sure our migrations are compatible with each and are using common features in these database distributions.

Data Definition Language (DDL) statements in MySQL

If a structure altering migration fails during a migration in MySQL, the migrator cannot rollback the change. The biggest implication of this behavior is that the Cloud Foundry operator has to manually revert the changes. Here is an example of a migration that fails to rollback. This is MySQL's behavior for all DDL commands, including variants such as MariaDB.

An exception in the example migration below will result in no rollbacks. This is especially concerning for migrations that touch multiple tables and change data.

# This migration will not run in a transaction

Sequel.migration do
  up do
    alter_table :dogs do
     add_column :testing, String
    end

    # Each update will be in its own transaction
    VCAP::CloudController::Dog.each_with_index do |dog, i|
      dog.update(guid: "c"*(i+1))
    end

    # Forces one transaction for all updates
    transaction do
      VCAP::CloudController::Dog.each_with_index do |dog, i|
        dog.update(guid: "d"*(i+1))
      end
    end
  end
end

In the example below, MySQL will not rollback any change before raise, including the data changes. The DDL action will cancel out any effects of a transaction.

In the example below, an explicit transaction is forced with a DDL statement within. While MySQL logs as though the migration is running in the transaction, it does not rollback any changes including the updates to the dogs table. The database column continues to exist too.

Sequel.migration do
  up do
    transaction do
      VCAP::CloudController::Dog.each_with_index do |dog, i|
        dog.update(guid: "a"*(i+1))
      end

      alter_table :dogs do
       add_column :testing, String
      end

      raise "Forced error"
    end
  end
end

Strategies to deal with transactions in MySQL

• Separate data changing migrations from schema changing DDL statements.
• Explicitly wrap data changes in a transaction that does not include DDL statements. Avoid inclusion of DDL in that transaction.
• Avoid extensive schema changes in a single migration. If any single change fails, the whole migration needs to be rolled back manually. Atomic migration files are easier to roll back.

Let’s rewrite the examples above keeping the listed strategies in mind:

Sequel.migration do
  up do
    alter_table :dogs do
     add_column :testing, String
    end
  end
end

Sequel.migration do
	up do
    # Forces one transaction for all updates
    transaction do
      VCAP::CloudController::Dog.each_with_index do |dog, i|
        dog.update(guid: "d"*(i+1))
      end
    end
  end
end

Name all constraints

If you don't, MySQL and PostgreSQL will default to different constraint names, this makes it more difficult to edit the constraints in the future.

• Example
• Docs for uniqueness validations
• Docs for foreign keys

Uniqueness constraints with null

If you write a uniqueness constraint where any of the values can be null, remember that null != null. For example the values [1, 1, null] and [1, 1, null] are unique.

Different code for different databases

If you must do different things for mysql and postgres you can check the type like this:

Sequel::Model.db.adapter_scheme == :postgres

Migrations must work with rolling deploys

This means that all migrations must be backwards compatible (with some exceptions).