Summary
While object-relational mapping tools greatly ease development with relational databases, few tools assist in migrating database schema as well as data to match the evolution of an object model. In an IBM DeveloperWorks article, Bruce Tate describes one such tool, Rails' Migrations feature.
Advertisement
When evolving changes to a database schema, how do you handle the changes occurring to the data at the time of migration, such as assigning default values to a newly created database column? And how do you remove database artifacts you no longer use, such as stale columns?
These are some of the questions Bruce Tate tackles in his recent IBM DeveloperWorks article, Rails Migrations.
To motivate the discussion, he points out that most schema migration tools deal with the schema, not with data: For instance, few tools allow you to insert some seed data into the database at the same time you create new tables or table columns. And few tools let you gracefully remove unused database objects:
The tools from a typical Java persistence framework won't help the database administrator because those tools deal with only part of the problem: the changes in the schema. When you make [a] schema change, you also need to be able to deal with the existing data... The database administrator usually must manually create SQL scripts to do the following:
Create a new column called name.
Capture the data from first_name and last_name into the new column.
Drop the first_name and last_name columns.
And if the code version causing the schema change is in any way deficient, the change must often be rolled back by hand. Only rare teams have the discipline to integrate and automate changes across the model, schema, and data.
Ruby on Rails' approach to data migration is a bit different. Rails provides a set of files, called migrations, each encapsulating not only changes to a database schema, but also changes to database data that result from that schema change, as well as the steps to back out from a migration:
In Rails, all schema changes—including the schema's initial creation—occur in a migration. Each change in the database schema has its own migration object, encapsulating a move up and a move down...
By allowing changes to data, migrations make it much easier to synchronize changes in data and schema that often come together. For example, you might add a new lookup table associating each state and its two-digit ZIP code. Within the migration, you can populate the database table, perhaps by invoking a SQL script or loading a fixture. If your migrations are correct, each migration leaves your database in a consistent state, with no manual intervention.
What do you think of Rails' Migrations approach? And how do you handle forward and backward database migrations?
Migrations are one of the cool features of Rails, and I'm not a proponent for the framework ;).
For us obsolete Java devs, Hibernate has the ability to do schema management, but I doubt the average J2EE dev even has production access to their data! And even if they did no doubt the Oracle DBAs usually on task would balk at the idea.
In terms my test data "migrations" I usually do the following or something similar.
create table contact_info as ( select fname, lname, phone from users ); alter table users drop column phone;
What was that about "few tools" being available? SQL does the job just fine, and these are performant operations. Save it? Sure, open up "executed-commands.sql" and paste.
Was data migration even a problem to begin with? I'm going to go with a firm no. Is it a nice to have? definitely, but when it comes time to make a blog in 15 minutes (who writes their own blogging software again?), I still wont go with Ruby on Rails.
This is important for live data. My current job is the first to have DBAs. Ironically some of the smaller places I've worked seem to have better migration systems, via ad hoc JDBC (i.e., SQL) frameworks. It's easier to coordinate the changes when they're not being made by more than one person in more than one place.
Still, our migrations never included any steps to back themselves out. When necessary, we did this as another migration to the next higher version number. If the reason is to undo a bug, then how can the necessary steps be known in advance?
Previous Topic
