A failed schema upgrade can ruin your day, week, or career
After developing an application upgrade with a partner site several states away, we were finally deploying. And it wasn't going well.
The upgrade included several schema upgrades. Those upgrades and the required data migration were scripted. Good. The development database schema didn't match the production schema. Bad. Their answer? "Run the script again and see if it works." Bad, bad. Two hours later, we had to roll back to the previous application. They had backups. Good. They had no idea how to restore from their backups. Oh... oh no, bad, bad, bad!
At that point I had been using ActiveRecord::Migrations for painless database upgrades for months, so i knew there was a better way, but a Ruby tool was a tough sell for this client. Developers in all languages deserve an easy and repeatable way to upgrade their databases on deploy day, and this lens should put someone on the right track.
Schema Refactoring Around the Web
Track and record database schema versions
My first search for information regarding schema upgrades
turned up this article. It still holds true, and it provides
the foundational knowledge required for implementing schema
migrations from a technical and administrative point of view.
Evolutionary Database Design
Matt Stevens interviews Scott Ambler concerning his work in the field of
database refactoring. Scott is a heavyweight in the field. Want proof?
Keep reading, he's the author two books listed below.
Books About Database Refactoring and Schema Migrations
Agile Database Techniques: Effective Strategies for the Agile Software Developer (Wiley Application Development)
Amazon Price: $26.40 (as of 07/13/2009) 
Refactoring Databases: Evolutionary Database Design (Addison-Wesley Signature Series)
Amazon Price: $47.99 (as of 07/13/2009)
Language Specific Libraries
Many popular application languages now have support for database schema refactoring and migrations in the form of libraries.
Java: LiquiBase
A database refactoring tool for Java. Of course, the actual migrations are written in XML. How could they resist?1 point
Ruby: ActiveRecord::Migration
The first introduction to a standard, cross platform database schema
migration framework for many developers. Some developers are working
on non-linea...0 points
Lisp: cl-migrations
"Anything you can do, I can do better" seems to be the motto of Lispers
everywhere. In this case, the ActiveRecord::Migrations code was
ported to lisp...0 points
Perl: DBIx::Migration
Migrations written in SQL, with common code shared and vendor specific SQL allowed.0 points
PHP: CakePHP Migrations
Migrations are written in YAML. A great screencast was recently published to walk developers through the process of using Migrations with CakePHP as w...0 points
Python: migrate
Migrate provides linear database schema management for Python
applications that use the SqlAlchemy library for database access. And who doesn't use th...0 points
Python Package Index : tgmigrate 0.2
TurboGears meets Migrate.0 points
Full Convert: Advanced database conversion/migration
Full Convert is a sophisticated database converter - supporting 16 database engines (Microsoft Access, dBase, FoxPro, Microsoft Excel documents, Inter...0 points
Your Thoughts
How do you handle database schema refactoring and migration? Any horror stories to scare folks straight? Please, share them here!
rose08 wrote...
Yes, data migration is sometimes a headache...check out my summer beach lens for relax...
Posted September 15, 2008
semanticdb wrote...
Hi,
Database schema refactoring was definitely a nightmare for me. But for the past 1 year I am using Poseidon Database its a Schemaless database which eliminates any kind of data modeling providing the fastest time to start, and needed peace of mind. It comes packed along with Brainwave Platform a complete development and deployment suite. http://www.brainwavelive.com/
-SW
Posted September 12, 2008
semanticdb wrote...
Hi,
Database schema refactoring was definitely a nightmare for me. But for the past 1 year I am using Poseidon Database its a Schemaless database which eliminates any kind of data modeling providing the fastest time to start, and needed peace of mind. It comes packed along with Brainwave Platform a complete development and deployment suite. http://www.brainwavelive.com/
-SW
Posted September 12, 2008
semanticdb wrote...
Hi,
Database schema refactoring was definitely a nightmare for me. But for the past 1 year I am using Poseidon Database its a Schemaless database which eliminates any kind of data modeling providing the fastest time to start, and needed peace of mind. It comes packed along with Brainwave Platform a complete development and deployment suite. http://www.brainwavelive.com/
-SW
Posted September 12, 2008
semanticdb wrote...
Hi,
Database schema refactoring was definitely a nightmare for me. But for the past 1 year I am using Poseidon Database its a Schemaless database which eliminates any kind of data modeling providing the fastest time to start, and needed peace of mind. It comes packed along with Brainwave Platform a complete development and deployment suite. http://www.brainwavelive.com/
-SW
Posted September 12, 2008
by timfreund
I'm a web application developer who works with Java, Python, and Ruby.
I have personally used database migration tools with TurboGears and Ruby on Rail...
(more)
