So I finally put in a few minutes to get my latest project into source control, and it occurred to me that keeping up with differences in database schema (to say nothing of content) isn’t handled with source control at all. I’d like to at least keep up with database structure in source control, though I suppose it isn’t really ‘source’ at all.

I was thinking of just doing an export of my database structure, maybe one with just structure and one with data, and then keeping track of these files with SVN (picked SVN purely for career reasons, liked git better). Is that sensible?

How do you guys handle keeping up with your database structure and data, and ensuring it is backed up and revertable if something should go wrong? Seems silly to use such complex systems to manage source code yet fall back on tarballing for database backups.

I do a db dump of the schema and commit it.

I’ve written a set of command-line scripts that handle database migrations for me, then I control the database schema through the migration system. Migration files consist of two functions, and ‘up’ and a ‘down’ function, which consist of their own PHP code that builds up the schema using CodeIgniter’s DBForge library. That way I’m using the language and framework that I’ll use to write my application to write my migrations.

It’s all relatively simple; I’ll write up a blog post about it and open-source the code at some point soon.

Did you have a look at DBVersion? http://codeigniter.com/wiki/DBVersion/

I’ve been building a distributed system on CI now for over a year and I’m just using Database Forge to create the tables and insert the data. I have a file called fields.php that has my schema then a data.php file with all the stuff it inserts. It’s worked pretty good so far.

We don’t do dumps of our database in version control because it adds unnecessary bloat to the repository. We have nightly backups and grab them from FTP when needed

Colin Williams - 29 October 2009 04:34 PM

We don’t do dumps of our database in version control because it adds unnecessary bloat to the repository. We have nightly backups and grab them from FTP when needed

Yes we do the same thing, but committing the schema isn’t that large and its nice to be able to track it along with code changes via git/whatever.

MySQL diff is pretty handy for this.

I just copy each of my changes into an UPGRADE text file and maintain 1-tables.sql, 2-default_data.sql, 3-dummy_data.sql. This is a horrible method that I need to improve.

Jamie’s got a good method with mentioning DBForge. It is essentially the same as my UPGRADE plan but means you can just run an upgrade controller to do it all for the user. BambooInvoice has been doing this brilliantly for years.

There should be a migrations tool for CodeIgniter but untill then dbdeploy works.

Someone feel like converting the Cake migrations implementation to CodeIgniter?

I’ll stick with tracking my SQL changes manually and logging them in a .sql file. Those solutions seem overkill (for my needs at least) and not very n00b friendly.

BrianDHall - 29 October 2009 11:03 AM

So I finally put in a few minutes to get my latest project into source control, and it occurred to me that keeping up with differences in database schema (to say nothing of content) isn’t handled with source control at all.

My database schemas are handled by source control.

All my table creates and inserts are stored in a bunch of files, located (for want of a better place) in assets/schema/

Files are called things like CREATE_site.mysql, INSERT_site.mysql - and there’s a CREATE.mysql that sources all the CREATE_* files (and an equivalent one for INSERTs) - such that I can recreate my database with one command (source CREATE.mysql) and re-populate it with my test data with another.

This works really well for me because I’m not afraid of using a text editor to design my schema, or to prepare my test data.  It might be also because I don’t have hugely complex test data - but I can’t see this approach becoming unwieldy even on reasonably large sets of data.

The files are all pulled into git - so as long as my commit annotations make sense, it’s easy enough to track changes.  Using something ilke bisect is trickier, of course, but no trickier than any other method would make tracking down a particular database change.