Is there a version control system for database structure changes?

In Ruby on Rails, there's a concept of a migration -- a quick script to change the database.

You generate a migration file, which has rules to increase the db version (such as adding a column) and rules to downgrade the version (such as removing a column). Each migration is numbered, and a table keeps track of your current db version.

To migrate up, you run a command called "db:migrate" which looks at your version and applies the needed scripts. You can migrate down in a similar way.

The migration scripts themselves are kept in a version control system -- whenever you change the database you check in a new script, and any developer can apply it to bring their local db to the latest version.

MyBatis (formerly iBatis) has a schema migration, tool for use on the command line. It is written in java though can be used with any project.

To achieve a good database change management practice, we need to identify a few key goals. Thus, the MyBatis Schema Migration System (or MyBatis Migrations for short) seeks to:

• Work with any database, new or existing
• Leverage the source control system (e.g. Subversion)
• Enable concurrent developers or teams to work independently
• Allow conflicts very visible and easily manageable
• Allow for forward and backward migration (evolve, devolve respectively)
• Make the current status of the database easily accessible and comprehensible
• Enable migrations despite access privileges or bureaucracy
• Work with any methodology
• Encourages good, consistent practices

I'm a bit old-school, in that I use source files for creating the database. There are actually 2 files - project-database.sql and project-updates.sql - the first for the schema and persistant data, and the second for modifications. Of course, both are under source control.

When the database changes, I first update the main schema in project-database.sql, then copy the relevant info to the project-updates.sql, for instance ALTER TABLE statements. I can then apply the updates to the development database, test, iterate until done well. Then, check in files, test again, and apply to production.

Also, I usually have a table in the db - Config - such as:

SQL

CREATE TABLE Config
(
    cfg_tag VARCHAR(50),
    cfg_value VARCHAR(100)
);

INSERT INTO Config(cfg_tag, cfg_value) VALUES
( 'db_version', '$Revision: $'),
( 'db_revision', '$Revision: $');

Then, I add the following to the update section:

UPDATE Config SET cfg_value='$Revision: $' WHERE cfg_tag='db_revision';

The db_version only gets changed when the database is recreated, and the db_revision gives me an indication how far the db is off the baseline.

I could keep the updates in their own separate files, but I chose to mash them all together and use cut&paste to extract relevant sections. A bit more housekeeping is in order, i.e., remove ':' from $Revision 1.1 $ to freeze them.

Redgate has a product called SQL Source Control. It integrates with TFS, SVN, SourceGear Vault, Vault Pro, Mercurial, Perforce, and Git.