Enhanced Migrations v1.2.0

The original release of this highly useful plugin marked a turning point in collaborative Rails development by freeing the developer to commit their database migration without fear of having it ignored because of a higher placed migration number. This latest release includes some minor bug fixes plus a useful method for stepping through migrations one at a time without the need for copying and pasting long version numbers.

1. Fixed bug where an empty migrations_info table would create a non-parseable schema.rb.
2. Made plugin database independent.
3. Added capability to step through migrations using VERSION=[previous, next, first and last].
4. dump_schema_information now returns all migrations, not just latest (credit to François Beausolei).
5. Added tests which use SQLite and a task (enhanced_migrations:clean_sqlite_db) to help with testing.

As an example of item number three, consider the following situation. Using the enhanced migrations plugin, you've just created a migration that adds a new table to your database. Upon running it, you discover that you used the wrong data type for a column. Rather than having to copy and paste the previous migration's version number, simply using 'previous' in the VERSION number will now suffice.

rake db:migrate VERSION=previous

This also works using prev along with next, first and last for their respective operations. Keep in mind that first will go to the first migration and is not the same as VERSION=0.

Migrate as usual

shell> rake db:migrate
== CreateRecipesTable: migrating ==============================================
-- create_table(:recipes)
-> 0.0902s
== CreateRecipesTable: migrated (0.0904s) =====================================

== AddRecipesForUser1: migrating ==============================================
-- execute("INSERT INTO recipes (name, owner) VALUES ('Lemon Meringue Pie', 'user1')")
-> 0.3684s
== AddRecipesForUser1: migrated (0.5302s) =====================================

== AddRecipesForUser2: migrating ==============================================
-- execute("INSERT INTO recipes (name, owner) VALUES ('Steak and Kidney Pie', 'user2')")
-> 0.2574s
== AddRecipesForUser2: migrated (0.3962s) =====================================

Migrate to the previous version

shell> rake db:migrate VERSION=previous
== AddRecipesForUser2: reverting ==============================================
-- execute("DELETE FROM recipes WHERE owner = 'user2'")
-> 0.4512s
== AddRecipesForUser2: reverted (0.4516s) =====================================

Migrate to the first version

shell> rake db:migrate VERSION=first
== AddRecipesForUser1: reverting ==============================================
-- execute("DELETE FROM recipes WHERE owner = 'user1'")
-> 0.1676s
== AddRecipesForUser1: reverted (0.1678s) =====================================

Migrate another previous version, essentially the same as VERSION=0 since we are already at the first migration.

shell> rake db:migrate VERSION=previous
== CreateRecipesTable: reverting ==============================================
-- drop_table(:recipes)
-> 0.1680s
== CreateRecipesTable: reverted (0.1683s) =====================================

How to get
Download the gem from Rubyforge and install it like so:

sudo gem install enhanced_migrations-1.2.1.gem

Update - 11/15/2007
The previous version had a small bug that was discovered during use here that sometimes caused an app's database yaml file to be overwritten by the gem's database config which is used only for testing. The chances of this happening outside of our particular setup are small but we felt it warranted an immediate fix. We've also added a gzip'd source file for the plugin fans out there.

Links in the post have been updated to reflect the location of this new release.

Plugin I Cannot Live Without

The Enhanced Rails Migrations plugin was written to end the constant battle we had with clashing names in db migrations within our large development team. We tried everything: special commit policies, rake tasks, even claiming the next migration number in subversion. Nothing worked and CI server was sending 'broken build due to conflicting migration number' messages almost daily. Since the plugin was introduced to all our rails applications around six months ago, I have not heard of a single case of conflicting migrations. Seemingly, the goal was well accomplished.

What I found over time, is that the plugin is not only useful for large projects. Any rails development effort with more than one programmer involved benefits from using it. If you ever had to renumber your new migration after doing svn up you know what I am talking about. It makes sense to install this plugin as the very first one in your project since an amount of migrations at the beginning tends to grow much faster then later in the game.

The plugin works for rails versions 1.1.6 up to the latest edge. When you start your next project with multiple developers, use it and you should be able to forget that you ever had problems with clashing migrations.

Moving models to a different database

There many reasons to use multiple databases (DBs) and when this is done, there is often a case when a model needs to be moved from one DB to another. The impetus could be that part of the data is referential and this is being reflected by moving it to a read-only DB. Another possibility is that we want to protect some data with an additional layer of security, so we extract it to a secure DB. In all cases, the challenge is to migrate the existing data. When the amount of data is considerably large, there is no choice but to do it via SQL data loaders or similar techniques. On the other hand, if it is acceptable to leverage rails db migrations and you prefer to do any data manipulation through them, there are some challenges to face.

Often, you need to have an access to old and new models during data migration. One solution is to move or copy an existing model to a separate namespace and put the new model instead at the old namespace. Let's look at a couple of examples:


Extracting referential data

I have a model Fruit in our main DB which gets its data from an external source, so we only access it read-only. We want to enforce it by moving the data to a DB which we access with a read-only account. First, I create a referential_db entry in database.yml:

dbs:
  database: main

  referential_db:
    database: referential

Then, I copy the original model, Fruit, to a dedicated namespace, so the model becomes RetiredModels::Fruit. I add establish_connection to the original namespace model:

# create_table :fruits do |t|
#   t.column :name, :string
# end
class Fruit < ActiveRecord::Base
  establish_connection configurations[RAILS_ENV]['referential_db']  
end

Everything is set for migration. Since it is a referential data, the migration needs to preserve data integrity so the models belonging to Fruit can still reference it by an old id:

def self.up
  RetiredModels::Fruit.find(:all).each do |old_record|
    Fruit.new(old_record.attributes) { |new_record| new_record.id = old_record.id }.save!
  end
end

After the successful migration run, all data is replicated to a new DB. The retired model can be removed during next deployment, and the original table dropped.

There is one caveat for development and test modes. If you don't want to bother with multiple databases in those modes, you need to take care of having no table name clashing. So, the new model would have to use different table names via set_table_name.


Securing sensitive data

One of the models belonging to Fruit is SecretFruit. It contains a secret name for every fruit out there. Our legal department asked the development team to protect that data in case our DB is stolen. We decided to migrate the existing SecretFruit data to a protected DB and keep sensitive data encrypted with help from Acts As Secure. First, I create a secure_db entry in database.yml:

dbs:
  database: main

  secure_db:
    database: secure
    host: protected_host

Then, I copy the original model, SecretFruit, to a dedicated namespace, so the model becomes RetiredModels::SecretFruit. I modify the model in the original namespace to reflect the new requirements:

# create_table :secret_fruits do |t|
#   t.column :name, :binary
#   t.column :fruit_id, :integer
# end
class SecretFruit < ActiveRecord::Base
  establish_connection configurations[RAILS_ENV]['secure_db']
  acts_as_secure :crypto_provider => MasterKeyProvider
  belongs_to :fruit
end

Since data encryption is done on-the-fly and there are no data integrity requirements, the migration is straightforward:

def self.up
  RetiredModels::SecretFruit.find(:all).each { |old| SecretFruit.create!(old.attributes) }
end

I can now safely delete RetiredModels::SecretFruit and associated data.

Data Loaders in Migrations

Two cases of data usage at RHG (as outlined in the DB Migrations With a Twist post) - migration consolidations and test data creation via migrations, led us to having migrations that just load and execute SQL statements from a file. Another use case is mass-loading of referential data. To help with those tasks we use a simple module that provides a method for data loading:

module DataLoader

  def load_data(name = 'data', ext = 'sql')
    file_name = data_file(name, ext) || fail("Cannot find the data file for #{ name }")
    execute_sql_from_file(file_name)
  end

  def execute_sql_from_file(file_name)
    say_with_time("Executing SQL from #{ file_name }") do
      IO.readlines(file_name).join.gsub("\r\n", "\n").split(";\n").each do |s|
        execute(s) unless s == "\n"
      end
    end
  end

private
    
  def data_file(name, ext)
    mode = ENV['RAILS_ENV'] || 'development'
    [ 
      data_file_name(mode + '_', name, ext),
      data_file_name('', name, ext)
    ].detect { |file_name| File.exists?(file_name) }
  end 

  def data_file_name(prefix, name, ext)
    "#{ RAILS_ROOT }/db/migrate/data/#{ prefix }#{ name }.#{ ext }"
  end
  
end

A sample migration using the method for loading the consolidated data to all DBs looks like this:

class ConsolidatedMigrations < ActiveRecord::Migration

  extend DataLoader

  def self.up
    all_dbs.each_key do |name|
      run_on_db(name) { load_data "consolidated_for_#{ name }" }
    end
  end

end

It loads Rail-environment specific files from the db/migrate/data/ directory:

development_consolidated_for_main.sql
development_consolidated_for_portal_referential.sql
...
production_consolidated_for_main.sql
production_consolidated_for_portal_referential.sql
...
test_consolidated_for_main.sql
test_consolidated_for_portal_referential.sql

[PLUGIN RELEASE] Enhanced Rails Migrations v1.1.0

Enhanced Rails Migrations is a plugin that makes easier rails development in a large team with multiple code branches.

Native rails db migrations are great for most of the teams. Troubles begin when many developers try to add a migration or when code is merged between branches. Since native migrations are based on incremental numbers, the name clashing is frequent. Same happens with a merged code on even larger scale. This plugin was born at the Revolution Health Group team to address the problems with large multi-branched development (see our blog entry - revolutiononrails.blogspot.com/2007/01/db-migrations-usage-at-rhg.html) where it has been successfully used for a few last months.

The Solution

There is a couple of patches against the Edge rails approaching the problem from different angles. You might want to try them unless you work on 1.1.6 or prefer plugins to handle such things.

The plugin monkey-patches ActiveRecord classes to replace the sequential number based rails migration mechanism with the timestamp based one. That allows to avoid name collisions. In addition, it maintains a tracking table of already run migrations instead of the standard single number schema_info. The table is being used for decision whether to run a migration. The biggest difference from the standard way is that migrations below the current schema version are still applied if they have not been applied yet (not in the tracking table).

Important: See the First Run section for details how to use it in existing projects.

Compatibility

The code has been used mostly on 1.1.6 and it was tested against the edge rails.

Setup

Installation

Install as any other Rails plugin:
ruby script/plugin install svn://rubyforge.org/var/svn/enhanced-mgrtns/enhanced_migrations

Usage

There is no difference in migration usage - use script/generate migration to generate a new migration and rake db:migrate for migration.

schema_version is depreciated. If you need to find the last run migration, use: SELECT MAX(id) FROM migrations_info

First Run

If you are adding the plugin in a middle of the development process, when some migrations have been created and applied, you need to mark those as already run or rake db:migrate would try to apply them again. The plugin contains a sample zero-number migration which queries the DB for the last run migration number and marks all migrations prior to the number as already run in the new tracking table. It also deletes the schema_version table. To use it, copy migrations/000_run_migrations_marker.rb from the project to your rails db/migrate directory.

License

Enhanced Rails Migrations is released under the MIT license.

Support

The plugin RubyForge page is rubyforge.org/projects/enhanced-mgrtns

TOD

The rails development mail list at RHG is busy at any day, holidays included: trunk is broken, again ... Please stop bouncing Dev ... new build delivered to QA ... [SVN COMMIT] rhg r40941 ... IE+HTTPS+XML+NoCache=Bad ... Another topic often seen there is TOD for Tip Of [the] Day. Here is an example of such tip:

Prefer atomic migrations

There is a potential issue with any db migration that tries to do more then one thing. For example, if a migration adds three indexes and the second one fails, because a key is misspelled, the migration is stuck. When the key name is corrected, the migration still won’t pass on a DB where it was originally run (read on forty workstations of your fellow coworkers who just updated the code and run migrations ). This time it would fail on the first index creation, because it was created by the failing migration and was not rolled back due to the nature of rails migrations.

The obvious solution for such problems is to make all migrations atomic. If there is a need to add three indexes, there should be three migrations. Instead of adding a table, populating it with data, and adding an index, all within a single migration, there should be three of them, with data population wrapped in a transaction.

DB migrations with a twist

As any project using Rails, we try to stay within the existing framework boundaries as much as we can but we are not afraid to push them if we see that there is a better way for us to adopt. The way we handle db migrations in our project have been driven by these forces:

• Dozens of developers working on the same rails source tree
• Multiple branches with frequent merges
• Multiple DBs defined in database.yml
• Migrations are used to populate DBs for tests
• No-outage policy for the site

Test DB recreation via migrations

We have accumulated hundreds of migrations over the life of the project. If we were running all of them to recreate the test DB before running tests, it would take more then an hour to finish the loading task. To bring that time down to just a couple of minutes, a process called db migrations consolidation is being used. Every time a build is deployed in production, all migrations up to the latest one in production are executed and the DB is dumped to a SQL file. A single migration, loading the generated SQL, then replaces all those migrations. The number used for the migration is the one that was already applied in production, so the new migration is never executed in production but on empty DBs like the one for tests.


Multiple DBs

We use DB partitioning for various reasons (from secure DBs with all data encrypted to read-only referential DBs). There is not much needed, from the migration point, to support multiple DBs. We have a rake task to read database.yml and to purge all DBs defined there before populating them via migrations. Another code included by migrations helps to run migration actions on a specific DB. Example:

run_on_db('secondary_db') { drop_index :x, :y }

We have multiple environments. In some we use a full set of DBs while in others it is a single DB with all entries in the config file pointing to it.

For population of some referential DBs with large quantities of data we have chosen to not use rails migrations at all but to store, deliver and deploy it via a separate process.


Many developers working on different branches

It was the biggest issue for us. At early stages name clashing of migrations was a norm. We tried to mitigate it by adding a special file (next_migration) where people could reserve the migration number they would use next. It did not work well. Some developers were forgetting to claim the number, other would do so but, due to the nature of rails migrations their migrations, would not run on other developers' machines because some new migrations were added after that, etc. Finally we gave up the standard way and created a gem which contained these migration enhancements:

• Using timestamps for migration numbers. It reduced name clashing to none.
• Using a migration tracking table to support migrations with numbers below the last migration run. There is no need to renumber migrations after merges anymore.

There is nothing new in this approach and some people have been using similar implementations (see François 's blog for example). It seems to work pretty good for us and the problems we had with the standard way went away since we started using the migrations gem.


Minimal or no site outages

Keeping db migrations backwards compatible comes with a cost in terms of development time and until recently we cheated on that since the site was not in the full production mode. But it all changed a couple of weeks ago when we were told that our goal should be to not bringing down the whole site for applying a new release.

From the practical point of view, it means making sure these two conditions are met:

• The old code can work with the new data during migrations and, potentially, long after in case the new code is rolled back (we don’t roll back data if a release fails because of the code).
• The new code code works with both new data structures and old data structures because there is no guarantee there will be no changes to the old data between the time when a db migration is applied and the new code is rolled to all nodes. For example, if a field is moved from one model to another (= a column from one table to another) the approach to just copy all data between tables and then ignore the old field is not acceptable. The reason is that at the time a new model comes live, there might be some updates to the old field. So, the right solution is to make the model intelligent enough to lookup in both places.

Our data structure retention period is one production release. I.e. an unused table column cannot be dropped in the same release when it made obsolete, but only in a release after that.

There might be rear exceptions when there would be no way to make changes backward compatible, but those are treated as special cases and go through peer->DBAs->management->QA reviews before being approved for production.

So far, we have not had a really challenged migration to meet the no-outage requirement. It would be a good team exercise to brain-storm the best solution when the time comes.