It seems that version control for database schema changes has traditionally received little developer attention. Almost everyone just goes ahead and changes database schema, trying to keep it backwards-compatible.
That’s possible if the change is small or non-intrusive (as it is in most cases). For more complex changes you could create ad-hoc script which makes necessary alterations and data conversion. In the most serious cases you stop the service, backup the database (this step is usually skipped by everyone once in lifetime, and only once), upgrade the code, run the conversion procedure, start the service back and hope that it all went ok.
This path of less resistance works reasonably well, and rarely leads to disasters. However, it is still error-prone, and the trend is to make the schema upgrades more relaxed, both error developers and production admins.
More integrated development environments seem to get built-in database schema versioning naturally. Less integrated ones get consistent migration strategy only at individual product level, and the tools are usually ad-hoc.
For example, Ruby on Rails has a very refreshing idea of Active Migrations. They work seamlessly, because:
- Rails is tightly integrated with underlying database;
- migrations are not tied to using SQL: to create table or add new field to it, you use Ruby itself; this helps with thinking in right way about data changes;
- keeping Rails application under Subversion is the encouraged practice;
- rake utility knows about migrations, and helps in creating and deploying them;
- capistrano utility also knows about migrations, and helps in pushing them to remote production/testing servers;
Perl is, of course, less integrated, and its primary offer, DBIx::Migration could use more attention. Being tied to using SQL is its main drawback.
I believe that mimicking Active Migrations functionality should be a new standard for every major open-source language, such as Perl, Python, or PHP. That’s just like everyone currently expects something similar to CPAN for every new language which tries to attract serious attention.
Meanwhile, Microsoft is taking advantage of its integrated development environment, and makes it easier to keep database schema under common version control.
Jeff Lynch writes in Microsoft Visual Studio Team Edition for Database Professionals:
…with Visual Studio Team Edition for Database Professionals, database development now becomes a fully supported part of your application’s development lifecycle. Now all database development is done “off line” and in a “sandbox” environment (this should make your dba stand up and sing!). All user defined functions and stored procedures can be fully unit tested using representative test data automatically generated by the toolset. And best of all, this new Visual Studio sku fully integrates into Team Foundation Server so your database schema (SQL scripts) can be put under source control just like any other C#, BizTalk or Web Application!
Sachin Rekhi writes in Extensibility in Team Edition for Database Professionals:
Test Conditions. The database unit testing feature allows you verify tests using either SQL assertions or easily configurable UI client-side test conditions. We ship a set of test conditions in the box, including row count, scalar value, empty resultset, etc. But these test conditions are completely extensible so you can imagine creating your own to do more powerful test verification.
Check-in Policies. Team System also allows you to create custom check-in policies that require certain actions to be performed prior to check-in. For example, a testing policy that ships with TFS enforces that a specific set of tests is run prior to checking in your code. You can implement other such db specific policies if you desired.
Several third-party tools are available to take control of your database schema for MS SQL Server, e.g.:
What’s your experience with such tools? What do you use? How did it affect your development habits? What are the drawbacks? How should we design database migration tools for open-source languages?
