Right tool for tracking DB structure changes

Question

Right now i have a PHP project and i track all changes in code by SVN. I would also like to track changes made in database structure.

Which is the right tool to use ?

Answer 1

Usually I've a php script I use to create the database structure during the installation process and test phase.

I find very useful to have it right into the svn repository that track the entire project (you have the changes linked to the codebase changes automatically).

Answer 2

Hi,

There are different sort of tools to track changes of database structure.

a very good one is mysql Workbench : http://www.mysql.com/products/workbench/

you can modelize your db with this tool to be able to update it with the sychronisation.

An other type of tool that my be perfect for svn : PHP Migrations

http://code.google.com/p/mysql-php-migrations/

With this tool you create different scripts : 001_initial.php 002_changes.php for example.

and when you execute the tool, it will apply the files that were not applied to make your schema up to date.

I Hope this helps you.

Answer 3

I usually solve this with scripted database dumps, containing the table definitions and all other things you might be interested in. This will even handle your data itself if your DB is not too big.

For a more systematic approach, you could have a look at Liquibase. I haven't tried it myself so can't comment on the practical usability, but it seems like it could be a very valuable tool for DB versioning.

Answer 4

For database changes, we have (had, no longer working there) a directory in the VCS:

+ dbchanges
|_ 01_database
|_ 02_table
|_ 03_data
|_ 04_constraints
|_ 05_functions
|_ 06_triggers
|_ 07_indexes

When you make a change to the database you put the change in a .sql file, into the correct directory with the current date and time as the filename. When you want to get your DB updated, you run the integration script, which goes through these directories in order, and import every change into the db.

The directories are named this way, to make sure that the order in which the scripts are run are correct. (So the script that tries to insert data into a table, won't run before the script that creates said table)

The sql files have to have to start with a comment, which is displayed to the user when the integration script imports the change, describing what it does. It logs every imported sql file's name to a file, so when you run the script next time, it won't apply the same change again.

Drawbacks are, that this doesn't support rollbacks, because you would have to store the inverses of the operations, and for some operations this is not possible, like DELETEs. The other drawback is, if you somehow lose the file that tracks the already applied changes, you have to recreate your DB from scratch.