Tags

, ,

Dump an Existing Database Schema Into a Ruby On Rails Migration Ready Format

by Justin Ball on May 9th, 2008

I have been looking around for a plugin or bit of code that can dump the schema from an existing database into the Ruby on Rails database migration format. The solution is a 'duh' moment, and probably obvious to most Rails programmers. All you have to do is change your database.yml file so that it points to the existing database you want to dump. Next run rake db:schema:dump. The schema.rb file in your db directory will now contain a dump of all the tables and indexes in your old database. Rename the schema.rb file to something else so that it doesn't get over written. Don't forget to change your database.yml file to back and now you only need to copy and paste what you need from the schema file into your migrations. This one should have been obvious, and it can save you an insane amount of time.

From Ruby On Rails

  • Thank you !

    This is typically the obvious part of the Ruby On Rails framework that is not formally documented, but rather given drop by drop by cool people like you.

    Why isn't there a wiki where we would all write a more comprehensive documentation for Rails in a single place ?
  • jbasdf
    I don't know that I'm so cool, but there are some cool guys around the net. Rails Guides is a great place for the big stuff - http://guides.rubyonrails.org/ and Rails inside highlights some cool stuff http://www.railsinside.com/
  • Bob Campbell
    Nope! Which of course I discovered after posting. :)

    I found the yaml_db which is what I want.

    So maybe what's needed is a mix of the two? Like, 'rake db:backup' and 'rake db:restore'? Hmm... :)
  • Bob Campbell
    The default schema.rb file has this comment:

    # Note that this schema.rb definition is the authoritative source for your database schema. If you need
    # to create the application database on another system, you should be using db:schema:load, not running
    # all the migrations from scratch.


    Would this imply that you could rake db:schema:dump your development database, copy the file to your production database and rake db:schema:load (with RAILS_ENV=production) and it would populate the database with everything?

    Seems like this might be a decent way to create a backup of the database, too, that would be faster to regenerate, ala' cronjob:
    rake db:schema:dump
    cp /site/db/schema.db /backup/site/db/schema.rb.$datestamp
    In case of disaster, copy back and
    rake db:schema:load

    Thoughts?
  • I'm not sure if rake db:schema:dump will dump the data. I think it just dumps the db structure in which case it wouldn't make a very good backup. You probably could take the rake task and modify it to dump the data in which case you would have an easy backup.
  • Dietrich Speer
    D-A-N-G-E-R-O-U-S
    I ran into some weird problems on a new site under development and decided to restart by creating a new database using db:schema:dump. So I create the new site using rake, change the db config to the old database and run it. Next is the new migration. Then I should have changed the db config to the new database, which I forgot. The result after db: migrate is a nice clean database schema with all the data gone.
    D-A-N-G-E-R-O-U-S
  • No doubt the technique is a bit tricky, but you should only be doing this on a dev machine with a backup copy of a database so that if something goes bad you can just start over.
  • This is great idea. Been doing migrations and database conversions for a while, and never thought to "hack" it at this level.
    Simple "DRY" and nice.
  • Gerald Byrket
    Thank you for this information. You just saved me TONS of work... :)
  • Glad I could help. Hopefully it all goes smoothly.
  • ariel
    Cool tip. Kind of reverse engineering. I'm gonna test this.
  • Thom Parkin
    Rails Rocks! It is so elegant and 'natural'. This is yet another example.
  • Steve K
    Thanks! helpful tip.
  • Marc Mengel
    Ahh... Well for those of us just reading up on Rails, this is an excellent tidbit to have pop up on Google :-) So thanks.
blog comments powered by Disqus