Mechanisms for Tracking Db Schema Changes

Mechanisms for tracking DB schema changes

In the Rails world, there's the concept of migrations, scripts in which changes to the database are made in Ruby rather than a database-specific flavour of SQL. Your Ruby migration code ends up being converted into the DDL specific to your current database; this makes switching database platforms very easy.

For every change you make to the database, you write a new migration. Migrations typically have two methods: an "up" method in which the changes are applied and a "down" method in which the changes are undone. A single command brings the database up to date, and can also be used to bring the database to a specific version of the schema. In Rails, migrations are kept in their own directory in the project directory and get checked into version control just like any other project code.

This Oracle guide to Rails migrations covers migrations quite well.

Developers using other languages have looked at migrations and have implemented their own language-specific versions. I know of Ruckusing, a PHP migrations system that is modelled after Rails' migrations; it might be what you're looking for.

How do you track database changes?

I ended up using Navicat for MySQL.

Version track, automate DB schema changes with django

There are at least two third party utilities to handle DB schema migrations, South and Django Evolution. I haven't tried either one, but I have heard some good things about South, though Evolution has been around a little longer.

Also, look at SchemaEvolution on the Django wiki. It is just a wiki page about migrating the db.

What are some good methods to push schema updates to end user databases?

Can you distribute the DB changes as part of the code changes? Then, when the app restarts, it checks if it needs to run any updates on the DB.

Obviously, you'll need to version the DB schema to avoid applying the same update more than once.

I know some applications that do this (mostly in Ruby, but also in Java).

How do you version your database schema?

See

Is there a version control system for database structure changes?

How do I version my MS SQL database in SVN?

and Jeff's article

Get Your Database Under Version Control

I feel your pain, and I wish there were a better answer. This might be closer to what you were looking for.

Mechanisms for tracking DB schema changes

Generally, I feel there is no adequate, accepted solution to this, and I roll my own in this area.

How to Ignore change tracking in Sql Schema Compare?

Change tracking is a lightweight solution that provides an efficient change tracking mechanism for applications. Typically, to enable applications to query for changes to data in a database and access information that is related to the changes, application developers had to implement custom change tracking mechanisms.

According my experience, we can not ignore the "ENABLE CHANGE_TRACKING" in SQL Schema Compare.

But when the the schema/database duplicated, we can disable Change Tracking manually:

ALTER DATABASE AdventureWorks2012  
SET CHANGE_TRACKING = OFF

For more details, please reference: Enable and Disable Change Tracking (SQL Server).

Hope this helps.

Dynamic patching of databases

I have a similar situation here, though I use MySQL. Every database has a versions table that contains the version (simply an integer) and a short comment of what has changed in this version. I use a script to update the databases. Every database change can be in one function or sometimes one change is made by multiple functions. Functions contain the version number in the function name. The script looks up the highest version number in a database and applies only the functions that have a higher version number in order.

This makes it easy to update databases (just add new change functions) and allows me to quickly upgrade a recovered database if necessary (just run the script again).

Even when testing the changes before this allows for defensive changes. If you make some heavy changes on a table and you want to play it safe:

def change103(...):
    "Create new table."
def change104(...):
    """Transfer data from old table to new table and make
       complicated changes in the process.
    """
def change105(...):
    "Drop old table"
def change106(...):
    "Rename new table to old table"

if in change104() is something going wrong (and throws an exception) you can simply delete the already converted data from the new table, fix your change function and run the script again.

But I don't think that changing a database dynamically when a client connects is a good idea. Sometimes changes can take some time. And the software that accesses a database should match the schema of the database. You have somehow to keep them in sync. Maybe you could distribute a new software version and then you want to upgrade the database when a client is actually starting to use this new software. But I haven't tried that.

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.

---

Related Topics