CAPI Migration Style Guide

Why?

Everything that is written for Cloud Controller needs to work for postgres and mysql.

Migration Style Guide

mysql migrations are not wrapped in transactions

If anything goes wrong during a migration in mysql the db will not rollback. Here is an example of a dangerous migration. If this migration blew up in the middle of running on mysql the db would have to be rolled back by hand. This is because mysql does not support DDL transactions.

Examples of why transactions in mysql are terrible

module VCAP::CloudController
  class Dog < Sequel::Model
  end
end

Sequel.migration do
  up do

    ## NOT in transaction
    alter_table :dogs do
     add_column :testing, String
    end


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

    ## 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 below example, NOTHING will rollback if the database is mysql. The DDL action will cancel out any effects of a transaction.

module VCAP::CloudController
  class Dog < Sequel::Model
  end
end


Sequel.migration do
  up do

    transaction do

      # This looks like it is in a transaction, IT IS NOT
      # When this migration errors nothing will be rolled back
      # The guids will remain updated and the column `testing` will still exist
      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 "wut"

    end
  end
end

How to deal with transactions in mysql

• Separate data changing migrations from schema changing migrations (DDL statements).
• Explicitly wrap data changes in a transaction
• Avoiding big schema changes in a single migration since if one fails, the whole migration needs to be rolled back manually. Minute migration files are easier to roll back.

Name all constraints

If you don't, mysql and postgres 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.

Don't use postgres only features

Sorry.

Different code for different databases

If you must do different things for mysql and postgres you can check the type with the following code.

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

Migrations must work with rolling deploys

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