On 8/30/21 8:56 AM, Miles Elam wrote:
On Fri, Aug 27, 2021 at 7:14 PM Julien Rouhaud <rjuju123@xxxxxxxxx
<mailto:rjuju123@xxxxxxxxx>> 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@xxxxxxxxxxx
