The Buckblog

Migrations have got to be one of the coolest aspects of ActiveRecord. They allow you to incrementally evolve your database schema, as you build your application. Bit by bit, you define the tables as you need them, add or remove columns, insert data, and so forth, preserving and migrating your data as you go. This has the added benefit of making it possible to keep the databases for multiple developers in various locations in sync—all each developer has to do is run rake migrate each time they update their code from the repository.

However, if you have an existing application, you may be wondering how to start, given that you already have a potentially mature schema. True, you can just start building onto your existing schema incrementally, but where’s the starting point? How do you give your application to another developer and have them get their database to a point where the migrations are useful to them?

Enter the SchemaDumper. This is a new addition to Rails, not yet released (except maybe in the very latest beta gems). It allows you to easily dump your existing schema to a file using the migration syntax. From there, you can either distribute the file and use it to recreate a database, or you can do a bit of copy-and-paste and use the file as the basis of your application’s first migration.

So, assuming your application has no existing migrations, and you would like to start using them, the process might look something like this:

1. rake db_schema_dump. This will create a file called db/schema.rb that contains the definition for your current schema.
2. Open db/schema.rb and copy everything inside of the block attached to ActiveRecord::Schema.define.
3. Run script/generate migration InitialSchema. This should create a file called db/migrate/1_initial_schema.rb.
4. Edit db/migrate/1_initial_schema.rb and paste what you copied from schema.rb into the self.up method.
5. Save the file.

And that’s it. You’re set with an initial migration that will allow another developer to recreate your database schema in their own database. From this point on, when you need to modify the database, just use the migration generator to add a new migration file, putting the DDL statements in the self.up method. The generator makes sure new migrations are added in the right sequence, so all that is needed is a simple rake migrate to keep your schema in sync with other developers.

It’s pretty slick!

(Update: the above won’t work quite as advertised, unfortunately, because the first time you try to run rake migrate, the migration will die telling you that the tables already exist. There are various ways to work around this, but none of them very elegant. I’ll see if I can find a better way to manage this “first run” situation.)

Alternately, if you’ve already been using migrations but want to take a snapshot of the current schema and use it when distributing your application, you can easily import that snapshot into another database. Just do rake db_schema_import, which will read the db/schema.rb file and suck it into the database defined in config/database.yml.

Note that schema.rb is portable between databases. You can dump a mysql database schema and import it into postgres, for instance.

No more maintaining multiple SQL schema definitions, one for each database you want to support. Just use the one schema.rb file and you’re good to go!