Migrations that change the schema

Up until today I did everything with a lot of planning and I let my OCD use migrations in a way they were not intended: I would go back and fix old migrations and destroy the database and re-run them.

With Sano, as I went through as fast as I could, two things happened: I made mistakes in the schema and those mistakes are now deployed. Time to make migrations to fix them.

The original migration for the weight model was like this:

create_table :weights do |t|
  t.integer :user_id
  t.float :weight
  t.datetime :measured_at
  t.timestamps
  t.foreign_key :users
end

and then I created two destructive migrations:

change_column :weights, :measured_at, :date
rename_column :weights, :measured_at, :measured_on

and

add_index :weights, [:user_id, :measured_on], :unique => true

The first one converted the measured_at datetime column in a measured_on date column. It destroys data, but I believe there’s no way that one could fail.

The second one adds an index for uniqueness between measured_on and user_id. That means that users can have only one weight per day. That one doesn’t destruct any data but it has the potential to fail when run on the production server.

I was about to just give it a try and pray. It’s not like thousands of people are using Sano anyway. Well, I’ve just realized I didn’t have to pray. I could test the migration first. It was trivial:

  1. Open local phpMyAdmin and go to sano_devel
  2. Delete all tables in local sano_devel
  3. Open remote phpMyAdmin and go to sano (the production database)
  4. Export everything
  5. Run SQL in sano_devel with the exported text
  6. Try migrations

Well, they worked:

$ rake db:migrate
(in /Users/pupeno/Projects/sano)
==  ChangeMeasuredAtTypeAndName: migrating ====================================
-- change_column(:weights, :measured_at, :date)
   -> 0.3609s
-- rename_column(:weights, :measured_at, :measured_on)
   -> 0.1440s
==  ChangeMeasuredAtTypeAndName: migrated (0.5056s) ===========================

==  AddUniquennessIndexToWeightUserIdMeasuredOn: migrating ====================
-- add_index(:weights, [:user_id, :measured_on], {:unique=>true})
   -> 0.1171s
==  AddUniquennessIndexToWeightUserIdMeasuredOn: migrated (0.1173s) ===========

and while I’m at it, let’s test the down-migrations, so I can be sure that if something goes wrong on production, I can rollback:

$ rake db:migrate VERSION=20091121135320
(in /Users/pupeno/Projects/sano)
==  AddUniquennessIndexToWeightUserIdMeasuredOn: reverting ====================
-- remove_index(:weights, {:column=>[:user_id, :measured_on]})
   -> 0.2745s
==  AddUniquennessIndexToWeightUserIdMeasuredOn: reverted (0.2748s) ===========

==  ChangeMeasuredAtTypeAndName: reverting ====================================
-- rename_column(:weights, :measured_on, :measured_at)
   -> 0.1381s
-- change_column(:weights, :measured_at, :datetime)
   -> 0.1335s
==  ChangeMeasuredAtTypeAndName: reverted (0.2719s) ===========================

Note: actually, there was a typo in the down-migrations; I’ve fixed it and everything was all right.

The new version with the improved forms is now deployed (the one I showed in the previous post), you can now play with it: sano.pupeno.com.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s