Automatically Creating, Loading, and Migrating your Database (with magic!)

Posted by Trevor in Ruby/Rails on April 10, 2008
12 Comments

So, let's say you're working on this open source Rails app, and you're doing your best to keep the requirements light and the installation as easy as possible. You'd love to have that Famous 5-Minute Install that WordPress has going for it, and you start to wonder...

Why should I even have to worry about the database?

Why can't my app take care of itself? Why can't it automatically create and maintain the database for me?

Well friend, I just wrapped up a little initializer that will automatically create the db, load the schema, and/or migrate as necessary. It all seems fine so far, but I still have this funny feeling that I'm doing something wrong...

 
/config/initializers/db_create_load_or_migrate.rb
 

I dunno... Have I gone too far this time?

 
unless defined?(Rake)
  load "#{RAILS_ROOT}/Rakefile"
  begin
    current_version = ActiveRecord::Migrator.current_version
    highest_version = Dir.glob("#{RAILS_ROOT}/db/migrate/*.rb").map { |f| f.match(/(\d+)_\w*\.rb$/) ? $1.to_i : 0 }.max
    Rake::Task["db:migrate"].invoke if current_version != highest_version
  rescue
    Rake::Task["db:create"].invoke
    abort 'ERROR: Database has no schema version and is not empty' unless ActiveRecord::Base.connection.tables.blank?
    Rake::Task["db:schema:load"].invoke
    retry
  end
end
 

Addendum

Thanks to the Fail Early chapter of the Advanced Rails Recipes book for turning me onto the idea of (at least) checking for an up-to-date schema with an initializer.

Also, make sure to check out this nifty trick that lets you run Rake tasks from within a Rails app. It's pretty easy - all you need to do is load the Rails Rakefile and use .invoke:

 
load "#{RAILS_ROOT}/Rakefile"
Rake::Task["db:create"].invoke
 

P.S. I spent the last hour re-enabling comments on this site, just so somebody like you can tell me that I'm not allowed to do this and why. So, please, have at it!

Update: I updated the regex thanks to a patch that Ben sent to deal with more kinds of directory names.

Update 2: After living with this for a while, I decided to take it out of El Dorado for the time being. I still think it's an interesting idea, but it's too risky to play with production data like this, and I think it's better to be on the safe side. Plus, the Rails developers I've spoken to about it seem to think that it's introducing unexpected behavior into the app (e.g. auto-migrations are not the norm).

12 Comments

 Adam

Love the idea but potentially couldn’t you get a race condition with multiple mongrels all trying to migrate the app at the same time?

Don’t know the internals of migrate tasks well enough to say for sure.

April 11, 2008
 Trevor

I don’t know, actually – that’s an interesting question. Maybe there’s a way to lock the entire database or app so that no other requests would be processed at that time?

April 11, 2008
 Eric

Might have to change the regex if you’re using UTC migrations? Maybe go with a time parse or something…

http://ryandaigle.com/articles/2008/4/2/what-s-new-in-edge-rails-utc-based-migration-versioning

Thanks for the Rake trick, btw!

April 11, 2008
 Rodrigo Urubata

Great idea :D
I liked this initializer :D

April 11, 2008
 Rodrigo Urubata

there is no need to change it for timed migrations because the format used grants a higer number every time :D

April 11, 2008
 Michael Buckbee

You could wrap the SQL in a transaction so that only the first mongrel (or whatever you happen to be using) got to it that it would already be taken care of.

Even without that, you’d still _probably_ be ok as the subsequent migrations would fail as soon as they hit something that already existed (like trying to add a column that was already there).

April 11, 2008
 Trevor

Michael, that’s interesting. Maybe wrapping the entire thing in a transaction wouldn’t be a bad idea. I don’t see how it could hurt…

April 11, 2008
 Malcontent

Rake keeps the latest version in the database, why not do this…

check if the database exists, if not create it and migreate.

If the database exists see if the migration tables are there. If not migrate.

Check latest version from the database and migrate if needed.

Come to think of it why not migrate if the database exists? What’s the harm in running the migration?

April 11, 2008
 Al Brown

this seems dangerous

Wouldn’t it be better if the code notified you (by emailing you a report or a URL for it) as to what changes it needs to make and give you the option of approving?

In the mean time, your app can make people wait until the db version is correct.

Starting your app could also display a message, but with everything automated, you won’t see it.

I think its best that you avoid depending on this mechanism though. Your best beat is to establish robust processes that check the db version and do the appropriate thing before restarting your app. Yes, the above would be cooler of course, but looking goofy to your users just once because of it will wipe out any coolness upgrade it buys you.

April 11, 2008
 links for 2008-04-24 | Libin Pan

[...] almost effortless » Automatically Creating, Loading, and Migrating your Database (with magic!) Automatically Creating, Loading, and Migrating your Database (with magic!) (tags: activerecord database rails rubyonrails migrations rake ruby) [...]

April 23, 2008
 Josh Nichols

This seems like it’d be extremely useful… in development.

For production, I think capistrano can nicely handle creating/migrating the database.

May 2, 2008

Leave a comment

WP_Big_City