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:

	require 'yaml'
	require 'logger'
	require 'active_record'
	namespace :db do
	def create_database config
	options = {:charset => 'utf8', :collation => 'utf8_unicode_ci'}
	create_db = lambda do |config|
	ActiveRecord::Base.establish_connection config.merge('database' => nil)
	ActiveRecord::Base.connection.create_database config['database'], options
	ActiveRecord::Base.establish_connection config
	end
	begin
	create_db.call config
	rescue Mysql::Error => sqlerr
	if sqlerr.errno == 1405
	print "#{sqlerr.error}. \nPlease provide the root password for your mysql installation\n>"
	root_password = $stdin.gets.strip
	grant_statement = <<-SQL
	GRANT ALL PRIVILEGES ON #{config['database']}.*
	TO '#{config['username']}'@'localhost'
	IDENTIFIED BY '#{config['password']}' WITH GRANT OPTION;
	SQL
	create_db.call config.merge('database' => nil, 'username' => 'root', 'password' => root_password)
	else
	$stderr.puts sqlerr.error
	$stderr.puts "Couldn't create database for #{config.inspect}, charset: utf8, collation: utf8_unicode_ci"
	$stderr.puts "(if you set the charset manually, make sure you have a matching collation)" if config['charset']
	end
	end
	end
	task :environment do
	DATABASE_ENV = ENV['DATABASE_ENV'] || 'development'
	MIGRATIONS_DIR = ENV['MIGRATIONS_DIR'] || 'db/migrate'
	end
	task :configuration => :environment do
	@config = YAML.load_file('config/databases.yml')[DATABASE_ENV]
	end
	task :configure_connection => :configuration do
	ActiveRecord::Base.establish_connection @config
	ActiveRecord::Base.logger = Logger.new STDOUT if @config['logger']
	end
	desc 'Create the database from config/database.yml for the current DATABASE_ENV'
	task :create => :configure_connection do
	create_database @config
	end
	desc 'Drops the database for the current DATABASE_ENV'
	task :drop => :configure_connection do
	ActiveRecord::Base.connection.drop_database @config['database']
	end
	desc 'Migrate the database (options: VERSION=x, VERBOSE=false).'
	task :migrate => :configure_connection do
	ActiveRecord::Migration.verbose = true
	ActiveRecord::Migrator.migrate MIGRATIONS_DIR, ENV['VERSION'] ? ENV['VERSION'].to_i : nil
	end
	desc 'Rolls the schema back to the previous version (specify steps w/ STEP=n).'
	task :rollback => :configure_connection do
	step = ENV['STEP'] ? ENV['STEP'].to_i : 1
	ActiveRecord::Migrator.rollback MIGRATIONS_DIR, step
	end
	desc "Retrieves the current schema version number"
	task :version => :configure_connection do
	puts "Current version: #{ActiveRecord::Migrator.current_version}"
	end
	end

view raw databases.rake hosted with ❤ by GitHub

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 
development:
  adapter: mysql
  user: root
  password:
  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)