How Do You Sync Database Schemas?

Rob Sweet asks: "I recently got started coding for a PHP front end to RRDTool. Right now, there are only two developers but we get the impression that once a protocol is in place, we'll have several more. The question has been posed: We can use CVS to keep our code synchronized but how do we go about keeping our database schemas synchronized? The obvious answers involve using mysqldump to keep updated table creation scripts in CVS but I'm wondering if there isn't a better way..." At the very least, a file containing a list of schema changes would be necessary, but what about programs that can take two schemas, look at the differences, and return the commands necessary to make the one mirror the other?

Comments

I've hit this problem before

[photon317]

I've been working in private for a while on my own ERD-like software (similar in flavor to Erwin and the likes), and I've dealt with this problem to some degree. It's much easier to have a higher-level tool deal with the issue. In my case, abstract schemas are stored in XML, and I have a tool that parses the XML and generates all the sql for "create table blah blah blah" for whatever db vendor you pick. Then there's another tool that can diff between two revisions of the XML schema definition and issue "alter table blah blah" statements to update a database's table layout.

Mine won't be ready for public consumption for some time yet, since I only work on it now again in spare time, and it has big huge unrealistic design goals - but it's not a hard job to build a simple version of the above on your own that's tailored to just your needs.

Alzabo

[m_ilya]

If you use MySQL or PostgreSQL you can use Alzabo to synchronize database schemas.