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.
Tags: database · migrations · Ruby On Rails12 Comments
12 responses so far ↓
Ahh… Well for those of us just reading up on Rails, this is an excellent tidbit to have pop up on Google
So thanks.
Thanks! helpful tip.
Rails Rocks! It is so elegant and ‘natural’. This is yet another example.
Cool tip. Kind of reverse engineering. I’m gonna test this.
Thank you for this information. You just saved me TONS of work…
Glad I could help. Hopefully it all goes smoothly.
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.
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.
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.
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…