Note that the IF EXISTS / IF NOT EXISTS are *not* idempotent. If you
need to write idempotent schema update scripts, you need to query the
catalogs to check if the specific change you want to apply has already
been applied or not.
Poor choice of words. You're absolutely right. The goal is for the script to be idempotent, not that individual statements like that are idempotent.
For example, adding ADD COLUMN foo IF NOT EXISTS and DROP COLUMN… to the script in addition to CREATE TABLE IF NOT EXISTS statements so that the end result is always the same column definitions no matter how often the script is run.
Eventually the individual ADD and DROP COLUMN statements can be removed once all databases are up to date.
Not sure that querying the catalogs is strictly necessary though… Could you say more?
This removes the ability to have "down" migration scripts, but I'll be honest, I've never actually used a "down" script in production. If the "up" script failed for some reason, the validity of the logic in the "down" script is immediately suspect. It's always a new "up" script to fix the problem. That's leaving aside the issue of "down" scripts not getting anywhere near the same level of scrutiny and testing as "up" migration scripts get.
- Miles