Database changes and VCS branching

Question

Suppose I am developing a web application that uses a database, and I create a branch in my version control system which requires significant database changes.

Let's suppose I can't just use the normal trunk database as I often do for other branches, and in this case I actually need a new copy of the database for my branch.

Now, suppose I have another branch which requires yet another copy of the database. Should I just create two additional copies of the database? Is there a way I can branch my database? What do you all do?

I am using MySQL. I'd like to just use one instance of MySQL. Maybe there is a version of MySQL where I can do something like this with my DSN? Server=myServerAddress;Database=myDataBase?version=123;Uid=myUsername;Pwd=myPassword;

I know I can make forward-only changes, but I'd rather not leave old tables and fields lying around unnecessarily.

Answer 1

There's no clean solution that I can think of for doing this. I'd just go down the route of mysqldump the "trunk" database then load it into a "branchA" database.

In terms of being able to keep the data fresh if you keep the set of CREATE, ALTER, etc commands required to get you from the trunk to the branch version then you can run this against a copy of the trunk or live data every time you want a fresh set of data. Doing it this way and having this script under version control means you've got a really easy was of doing this on production when you're ready merge your branch back into the trunk and deploy it.

You could go the route of LVM snapshots, virtual machine snapshots, etc to make a full copy of your database data or machine but I think that would probably be overly complicated.