Keeping a database structure up to date in a project where code is on subversion?

Question

I have been working with Subversion for a while now, and it's been incredible for the management of my projects, and even to help managing the deployment to several different servers, but there is just the one thing that still annoys me. Whenever I make any changes to the database structure, I need to update every server manually, I have to keep track of any changes I made, and because some of my servers run branches of the project (modifications that are still being worked on, or were made for different purposes), it's a bit awkward.

Until now, I've been using a "database.sql" file, which is a dump of the database structure for a specific revision. But it just seems like such a bad way to manage this.

And I was wondering, how does everyone else manage their MySQL databases when they're working on a project and using Subversion?

Answer 1

In my team here is what we currently do: we only have one branch: the trunk, which is where every developer checked in his changes.
When we want to release a new version of our solution, we create a new branch from the trunk. (after stabilizing it a bit).
For each release, we also have a file to migrate the schema of our databases from version n-1 to version n. We also have a script to rollback from n to n-1. So when we start a new release, we create new migration & rollback files which are comitted in the trunk.
Thus we are able to rebuild the database corresponding to any version of our solution starting from any "version" of a given schema.
Actually, we also had a lot of debates on this question and this is finally what we chose to do. But if you guys have some ideas to help us to improve, let us know :)

Answer 2

Liquibase might be something useful for you.

I've played around with this quite a bit, although not to the point of using it in anger. Basically you define your database and scripts in their syntax, and they generate upgrade and from-scratch scripts for various databases for you.

Takes a bit of getting used to, but works quite well.