Friday, February 11, 2011

ActiveRecord migrations without Rails

I spend a lot of time working with data from partners that comes in various forms from Excel spreadsheets to archive files with field descriptions.  It is always a pain to work with this data but one thing I have been doing lately is creating databases for the data that live outside of our application.  This way I can manage frequent updates and apply transformations to the data before it is loaded into our production database.

I used to do this with sql files and had a numbering scheme but what I really wanted were ActiveRecord migrations without a full blown Rails application.  Since it is just a single database I didn't need all of the typical rake tasks but I did create the following Rakefile:

The following tasks are available:
wesbailey@feynman:~/autism_data> rake -T
(in /Users/wesbailey/autism_data)
rake db:create    # Create the database from config/database.yml for the current DATABASE_ENV
rake db:drop      # Drops the database for the current DATABASE_ENV
rake db:migrate   # Migrate the database (options: VERSION=x, VERBOSE=false).
rake db:rollback  # Rolls the schema back to the previous version (specify steps w/ STEP=n).
rake db:version   # Retrieves the current schema version number

Instead of defining RAILS_ENV you instead define DATABASE_ENV.  To specify where the migrations live you can override the default of db/migrate with MIGRATIONS_DIR.  Just like with rails you define config/database.yml for database connection information.
wesbailey@feynman:~/autism_data> cat config/databases.yml 
  adapter: mysql
  user: root
  database: autism_speaks_dev
  host: localhost
  logger: false

An example run for this database shows usage and results are just like one would expect in a full blown rails environment:
wesbailey@feynman:~/autism_data> rake db:create
(in /Users/wesbailey/autism_data)
wesbailey@feynman:~/autism_data> rake db:version
(in /Users/wesbailey/autism_data)
Current version: 0
wesbailey@feynman:~/autism_data> rake db:migrate
(in /Users/wesbailey/autism_data)
==  CreateLoadedProviders: migrating ==========================================
-- create_table(:loaded_providers)
   -> 0.1285s
==  CreateLoadedProviders: migrated (0.1288s) =================================

wesbailey@feynman:~/autism_data> rake db:version
(in /Users/wesbailey/autism_data)
Current version: 1
wesbailey@feynman:~/autism_data> rake db:rollback
(in /Users/wesbailey/autism_data)
==  CreateLoadedProviders: reverting ==========================================
-- drop_table(:loaded_providers)
   -> 0.0023s
==  CreateLoadedProviders: reverted (0.0024s) =================================

wesbailey@feynman:~/autism_data> rake db:drop
(in /Users/wesbailey/autism_data)