Preserving your data when changing your schema

I've been working with rails in an agile model ... build something, get it working & tested, move on to the next thing. As a result, my underlying schema is continually evolving. Mostly that's in the form of additional tables being added, but sometimes it's changes to existing tables to add new fields, etc.

When I make these changes, I don't want to lose the data I've already got loaded into the old tables. I also wanted to have stand alone files/scripts that could build and populate my database from scratch so I could do clean builds. As a result, I wasn't comfortable with just going into the database's interactive console and doing

CREATE TABLE

and

ALTER TABLE

My understanding is that Rail's migrations are a great way to do this, but with so many things to learn, I haven't gotten to them yet.

On my development machine (Mac G5 with Rails 1.0 and MySQL 4.x), I've created the following short aliases to dump the current database contents, build a clean db from my db/create.sql script, and then reload my data:

imac20:~/Technical/ruby/rails/myapp steven$ db_dump
imac20:~/Technical/ruby/rails/myapp steven$ db_build
imac20:~/Technical/ruby/rails/myapp steven$ db_reload

These are based on the aliases being defined in my .bashrc file:

alias db_dump='mysqldump -u app1 -ppw1 -t -c myapp_development > db/obelisk.dump'
alias db_build='mysql -u app1 -ppw1 myapp_development < db_reload="'mysql">

if you try to use these, you'll need to replace "app1" with your mysql account name, "pw1" with your password, and "myapp_development" with the name of the database you are using for development.

the -t and -c flags on mysqldump do the following:
-t --> stops the dump from including table creation information. This is important because all the tables are created by db_build in step 2. If you don't have the -t option when you do the dump, you'll run into problems in step 3 (db_reload) if you're trying to import data back into a table where a column has been added.

-c --> forces the dump to create complete insert statements (with column names). Again this is important if the change you've made to your create.sql includes changes to the columns of an existing table.

One more thing... in order to get OSX to load the aliases in your ~/.bashrc file, you need to have the following in your ~/.bash_profile

if [ -f ~/.bashrc ]; then source ~/.bashrc; fi

The format of create.sql follows the advice from Agile Web Development with Rails.

Drop Table if exists my_table;
Create Table my_table(
...columns go here...
primary key(id)
);

Why another rails blog?

For about the last month, I've been building business applications with Ruby on Rails and loving it. In the course of that time, I've hit a number of road blocks and had to figure out how to get around them. I thought it might be save other new rails developers to see these problems/solutions, so I started this blog.