Is there a version control system for database structure changes?

Question

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.

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.

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

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

I highly recommend SQL delta. I just use it to generate the diff scripts when i'm done coding my feature and check those scripts into my source control tool (Mercurial :))

They have both an SQL server & Oracle version.

I wonder that no one mentioned the open source tool liquibase which is Java based and should work for nearly every database which supports jdbc. Compared to rails it uses xml instead ruby to perform the schema changes. Although I dislike xml for domain specific languages the very cool advantage of xml is that liquibase knows how to roll back certain operations like

<createTable tableName="USER"> 
   <column name="firstname" type="varchar(255)"/>
</createTable>

So you don't need to handle this of your own

Pure sql statements or data imports are also supported.