Re: Idempotent DDL Updates

On 8/30/21 8:56 AM, Miles Elam wrote:
>
>
> On Fri, Aug 27, 2021 at 7:14 PM Julien Rouhaud <rjuju123(at)gmail(dot)com
> <mailto: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.

I think you need to investigate Sqitch:

https://sqitch.org/

When working on dev database I run the deploy(up) script and then the
revert(down) every time I do a change to make sure it does work. Not
only that I routinely revert back to some previous state. Helped by
Sqitch tags that allow you set a marker in your change history. I'm
going to say that if you spend some time with the documentation you will
find that Sqitch is the scratch that eliminates your itch:)

>
> - Miles

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com