Re: Idempotent DDL Updates

On Fri, Aug 27, 2021 at 7:14 PM Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:

>
> 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