How do you maintain revision control of your database structure?

Question

What is the simplest way of keeping track of changes to a projects database structure?

When I change something about the database (eg, add a new table, add a new field to an existing table, add an index etc), I want that to be propagated to the rest of the team, and ultimately the production server, with the minimal fuss and effort.

At the moment, the solution is pretty weak and relies of people remembering to do things, which is an accident waiting to happen.

Everything else is managed with standard revision control software (Perforce in our case).

We use MySQL, so tools that understand that would be helpful, though I would also be interested to learn how other places are handling this anyway, regardless of database engine.

Answer 1

You can dump the schema and commit it -- and the RCS will take care of the changes between versions.

Answer 2

You can get a tool like Sql Compare from Red-Gate which allows you to point to two databases and it will let you know what is different, and will build alter scripts for you.

If you're using .NET(Visual Studio), you can create a Database project and check that into source control.

Answer 3

This has alrady been discussed a lot I think. Anyhow I really like Rails approach to the issue. It's code that has three things:

1. The version number
2. The way of applying the changes (updates a version table)
3. The way of rolling the changes back (sets the version on the version table to the previous)

So, each time you make a changeset you create this code file that can rollback or upgrade the database schema when you execute it.

This, being code, you can commit in any revision control system. You commit the first dump and then the scripts only.

The great thing about this approach is that you can easily distribute the database changes to customers, whereas with a standard just dump the schema and update it approach generating an upgrade/rollback script is a nuisance

Answer 4

In my company each developer is encouraged to save all db sctructure changes to a script files in the folder containing module's revision number. These scripts are kept in svn repository.

When application starts, the db upgrade code compares current db version and code version and if the code is newer - looks into scripts folder and applies all db changes automatically.

This way every instance of application (on production or developers machines) always upgrades db to their code version and it works great.

Of course, some automation could be added - if we find a suitable tool.

Answer 5

Poor mans version control:

Separate file for each object (table, view, etc)

When altering tables, you want to diff CREATE TABLE to CREATE TABLE. Source code history is for communicating a story. You can't do a meaningful diff of CREATE TABLE and ALTER TABLE

Try to make changes to the files, then commit them to source control, then commit them to the SQL database. Most tools poorly support this because you shouldn't commit to source control until you test and you can't test without putting the code into SQL. So in practice, you try to use SQL Redgate to compare your files to the SQL database. Failing that, you adopt a harsh policy of dropping everything in the database and replacing it with what made it into source control.

Change scripts usually are single use, but applications exist, like wordpress, where you need to move the schema from 1.0 to 1.1, 1.1 to 1.5, etc. Each of those should be under source control and modified as such (i.e. as you find bugs in the script that moves you from 1.0 to 1.1, you create a new version of that script, not yet-another script)