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 
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)

11 comments:

  1. Amazing post Wes! I´m starting with Rails and didn´t know Rake was so cool. To understand your code i had to research a little though. :)
    Thanks and keep the good work!

    ReplyDelete
  2. Thanks Bernardo! Glad you found this useful.

    ReplyDelete
  3. Nice tip here. The API is easy to use, and Rake should be familiar to even a novice Rubyist, but it's nice to have quick references like this lying around. Thanks!

    ReplyDelete
  4. You are welcome Seth! I was frustrated with the lack of info out there on how to do this. Now it is there for everyone.

    ReplyDelete
  5. I'm doing the same sort of thing with scripts and running them at the command line. Using Rake seems smart, I'm going to try that next.

    ReplyDelete
  6. Have a look at standalone migrations on github which does just that:

    https://github.com/thuss/standalone-migrations

    we can always use more contributors!

    ReplyDelete
  7. @Todd I forked your project and added to it and issued a pull request. Review it if you are interested.

    ReplyDelete
  8. A post on stack overflow here:

    http://stackoverflow.com/a/9450343/365798

    specifically for the use of activerecord + sinatra + JRuby + postgresql

    with JRuby you need to make sure you've got the right activerecord gem adapter "activerecord-jdbcpostgresql-adapter"

    ReplyDelete
  9. Thanks. Using this right now!

    ReplyDelete
  10. Do you know why i keep getting the error:
    private method `create_database' called for #?

    ReplyDelete