apgdiff

I encountered a defect yesterday that was caused by our production database schema being out-of-sync with the domain model. Our schema has undergone numerous migrations over the course of a few years. Each migration involves applying manually-written SQL scripts that update the schema while migrating the extant data to the new schema. It was inevitable that some schema change was going to be omitted from a migration script, and that's in fact what happened. So I decided it was time to perform a full schema diff (actual versus expected) to see if we had any other hidden time bombs. I exported the schema from both a newly created database (schema only) and the production database. But one cannot perform a straightforward diff on these schema SQL representations, since ordering of objects (tables, triggers, etc.) and nested items (table fields) may be different. So I found apgdiff, which performs the necessary "intelligent" diff. Instead of standard diff output, it outputs the SQL needed to bring the old schema up-to-date with the newer one. After tracking a NPE in its source code (caused by a missing create table statement and an associated alter table statement), it worked. It's a nice, straightforward tool that I will integrate into my development process. In particular it will save me time when the next schema migration script needs to be written.