What is the general consensus within the community on idempotent DDL scripts, ie. consistent usage of IF EXISTS/IF NOT EXISTS/OR REPLACE for SQL init files that get checked into source control?
My experience has been that it's much easier to manage than an ever-growing set of migration files, but I'm just a data point of one. Obviously for other engines that don't support transactional DDL, it's a non-starter, which leads me toward the notion that its lack of obvious popularity is due to its limited cross-engine viability. But PG does have transaction DDL, preventing incomplete DDL updates. However this may just be my personal bias talking. Yet I cannot seem to discount the advantages over ever-increasing volumes of Flyway-style migration files & directories:
* being able to do diffs of changes in source control
* limiting the size of the total SQL over time relative to what's actually in the DB structure
* much more easily determining the most current version of a function/procedure (this bit me hard in the past with dozens of migration files)
* the ability to prune old changes that no longer apply to any deployed version of the database
* treating database structure as code
The drawbacks I've run across are those areas where the EXISTS/REPLACE constructs aren't implemented like roles management, domains, constraints, etc. However those cases seem to be handled with only minor increases in complexity with judicious use of inline plpgsql.
In others' opinions, has DDL idempotency been viable for maintenance of PG databases fo you in production?
- Miles Elam
