The maze book for programmers!

Algorithms, circle mazes, hex grids, masking, weaving, braiding, 3D and 4D grids, spheres, and more!

DRM-Free Ebook

The Buckblog

assorted ramblings by Jamis Buck

Getting Started With ActiveRecord Migrations

26 September 2005 — 3-minute read

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!

Reader Comments

You stole my idea! :-) See 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.
Eeek, pasting the script file didn't work out all that well.
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
Alan, Have a look at 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
Thanks, Chad :-)
Quick question, can foreign key constraints be specified ? I looked, but couldn't see it.
I guess I should have publicized a bit better :-)
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?
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 :-)
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?
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": It works really, really well.
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.
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.
Any updates on this? Is there a better way to jump into using migrations now since v1 is coming out?
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.
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...