Really resetting the database

When I start coding a Ruby on Rails project, I find myself modifying the migration files over and over. I know this is not the way they were intended to use, but to avoid upfront design, I only ad fields when I need them. If I respected the way migrations were intended I would end up with hundred of migrations the first day and I would waste half my day just creating migrations.

After a project is deployed or a second developer is working on it, I revert to the way migrations are intended and I create a new one every time there’s a change I need in the database.

As migrations are intended to run only once, if you modify them, they won’t get run; and if you force them to run them, they’ll fail, because the database already contains such a table. So I’ve found myself doing this quite often:

rake db:drop && rake db:create && rake db:migrate && rake db:seed && rake db:data

db:data is a task I created to generate some sample data. Good known data that I can use to test the site locally. I’m using Factory Girl to create it, which I also use for the tests so I can re-use as much data creating logic as possible. It’s very good to get to a known state of the project you are developing and to get other developers started right away. I really recommend everyone doing it.

The problem is that I also need to reset my test data, so I end up having this other command and it gets horrible:

RAILS_ENV=test rake db:drop && RAILS_ENV=test rake db:create && RAILS_ENV=test rake db:migrate && RAILS_ENV=test rake db:seed

Note: no db:data this time.

I’ve got tired of re-writing these commands or trying to find them in my bash history, so I decided to write a Ruby task that will do it for me and here it is in case you want to use it too:

namespace :db do
  desc "Crush and burn the database"
  task :hard_reset => :environment do
    if !Rails.env.test?

  desc "Generate sample data for developing"
  task :data => :environment do
    # Create the sample data in here


Update: To delete all records without resetting the whole database, check my post Deleting all records in a Rails project.

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.foreign_key :users

and then I created two destructive migrations:

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


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: