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@gmail.com 
> <mailto:rjuju123@gmail.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.klaver@aklaver.com