Getting Started With ActiveRecord Migrations

Posted by Jamis on September 26, 2005 @ 08:13 PM

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!

Comments

Have something to add? Click here to leave a comment.

26 Sep 2005

1. Joe said...

You stole my idea! :-) See http://joevandyk.com:2529/repository/file/trunk/load_database.sh for what I load often during development. It's posted below: #!/bin/sh echo 'drop database gamefest_development; | mysql -h localhost -u root echo 'create database gamefest_development;' | mysql -h localhost -u root rake migrate && rake reload_db I drop the development database, then create the development database. Then rake migrate is run which creates all the tables. I have a task (reload_db) in my rake file that will load all my test fixtures into the development database. So, after making changes to the database schema, I can do ./load_database, and everything's loaded from scratch. It's been pretty helpful.

2. Joe said...

Eeek, pasting the script file didn't work out all that well.
27 Sep 2005

3. Alan Francis said...

Alan Francis said 2 days later: Hi Jamis, Could you give us a 5 minute tutorial on using the migration from the get-go ? I've just started a project and have my schema in a .sql file which I load with my own little populate_database script. Before I go any further, I'd rather switch and use the ActiveRecord stuff. So assuming I'm starting a brand new project, I do 'rails myproject'. At the moment the next thing I do is start editing my .sql file. What should I do instead ? Alan

4. Chad Fowler said...

Alan, Have a look at http://wiki.rubyonrails.org/rails/pages/UnderstandingMigrations You use a generator to create the initial migration files, and then you fill in their "self.up" and "self.down" methods with the simple language of ActiveRecord::Schema (e.g. "add_table do |t|; t.column :name, :string; end) Chad

5. Alan Francis said...

Thanks, Chad :-)

6. Alan Francis said...

Quick question, can foreign key constraints be specified ? I looked, but couldn't see it.

7. Scott Laird said...

I guess I should have publicized http://rubyforge.org/projects/schemagenerator/ a bit better :-)

8. Ryan Platte said...

I've been kinda squatting on the RubyForge name "ActiveSchema" for some months now -- I had an idea that I never got around to implementing, and then RoR gained migration support. I'll give it to someone who presents a RoR schema idea I like, or to Rails if there's something that they'd like to be named "activeschema". Is Rails the first framework to offer built-in database refactoring support?

9. Alan Francis said...

Found it, indirectly. Doing it in raw SQL is a little clunky, but I guess that's exactly what I'm doing at the moment :-)

10. Jay Levitt said...

Not for nothing, but it sure seems like SchemaDumper could be a first step on the road to specifying the initial schema right in Rails, solving the DRY problem... ya?

11. Jamis said...

Jay, rather than specify the initial schema using the SchemaDumper, I'd just use migrations. Use the migration generator to create an initial migration and then define your first few tables and indexes right there, using the migration DSL(Domain Specific Language). In fact, that's what I've been doing with "BudgetWise":http://bw.urug.org. It works really, really well.
28 Sep 2005

12. Scott Laird said...

Yeah, we're generating the schemas directly from migrations in Typo now too, and it works really well. Before we started doing this, any DB changes had to be specified in a migration file and then in 3 schema files (mysql, pgsql, sqlite). It was a big DRY problem. That's fixed now, using my schema generator on RubyForge. It does nasty things to the internals of Rails (gotta love open classes), but it works with the three dbs that we care about right now.

13. Jamis said...

Scott, thanks for pointing out your schema generator stuff. If you're after raw SQL definitions of your schemas, that is definitely the way to go. The SchemaDumper tackles it from the other direction, and will never give you SQL without requiring you to import into a DB and then dump the schema. It's nice to have both tools available.
14 Nov 2005

14. Subimage said...

Any updates on this? Is there a better way to jump into using migrations now since v1 is coming out?
14 Dec 2005

15. Jon said...

I just upgraded one of my apps to 0.14.4 to try out migrations, and it works like a charm. I already had a database full of data and didn't want to start from scratch, but wanted to use migrations for future updates. In response to the question above about 'first run' situations, here's what I did: Follow the instructions above (rake db_schema_dump, script/generate migration Initial, copy to 001_initial.rb) Add any foreign key constraints to the migration file. Create a table named schema_info with a single integer column named version (this is what rake migrate does for you the first time you run it). Put a single entry in it with a value of 1 and when you run "rake migrate", rake will skip your first migration. Presto! No table_already_exists errors, the data doesn't get touched (I'm using MySQL 4.1 and 5.0, btw), and migrations after that (up and down) work like a charm. Also, if you are adding foreign key constraints in the migration file, make sure you either create dependent tables after independent tables, or add your foreign keys after you add your tables.
06 Jul 2006

16. John Sietsma said...

A bit late, but I'll add a comment underneath all your spam. To get around the table already exists problem I first added an empty migration and then the real one with matching table creates and drops. For some reason if I enter: rake migration VERSION=0 it doesn't execute the down function in the first migration file. So, if I have an emtpy migration number 1 and do the real work in migration 2, then I can do: rake migration VERSION=1 and have all my tables dropped for me. Next time I run migrate the tables are created without error. I'm a rails newbie, so hopefully this is the problem you were talking about! If not, feedback welcome...