Fail Upon Skipped Versioned Migration

[zanebclark]

The Problem

I use the epoch time as the version number when creating versioned scripts. This almost removes the version-number guesswork from the creation of a new script. Here's the exception:

Two developers are creating scripts around the same time. Or, one dev starts work on a script and has to come back to it after making some other small change:

• V1702512738__some_change_requiring_a_lengthy_review.sql
• V1702512750__a_small_change.sql

Because it is smaller or easier to review, the V1702512750 script is applied by Schemachange first. When the V1702512738 script is merged into the branch in question, Schemachange silently ignores it. I mean, it isn't silent. Somewhere in the thousands of lines of logging, there's a line about it. The dev could check the logs or review Snowflake to ensure that the changes "took", but I think we can do better than that.

The Ask

I'd like to support a flag that would cause schemachange to fail if both of the following are true:

• A versioned script isn't already applied and
• The script won't be applied because the max version number is already greater than the script version

In fetch_change_history, we're already fetching information from the change_history_table about versioned scripts. Instead of get_alphanum_key(script['script_version']) <= get_alphanum_key(max_published_version), we could retrieve the change_history_table record when iterating over the repository scripts. If there's no change history table record to speak of and the "fail on missing" flag is true, throw an exception. While we're at it, we could compare the has of the versioned script to what was recorded in the change history table. If that value differs, a warning would suffice.

If you're assigning arbitrary version numbers (e.g. 12345), this functionality is baked in. The process of provisioning a version number would likely involve reviewing existing script version numbers and selecting the next available number. If two developers use this process and arrive at the same number, Schemachange will complain about the duplicated version number and fail the second migration.

I can implement this in a fork, but I'd like to do so with feedback. Useful? Monstrous? Thanks for reading!

[goranzdraveski]

@zanebclark wouldn't Out of order parameter with same functionality as in Flyway resolve this issue?

[zanebclark]

Hey, @goranzdraveski . I wasn't aware of Flyway's Out of Order parameter. It wouldn't be hard to add a similar flag to schemachange. This feature is a bit different. Migrations are still applied in order of the version, but out of order scripts aren't silently ignored. In an environment with multiple developers or a fast release cadence, it's possible for a developer's work to be delayed by a merge request and never applied because other work was applied during the delay. Unless the developer checks that their work is applied after deployment, there's currently no indicator for this.

I've implemented this feature in this pull request: zanebclark#1. If you think the Out Of Order parameter would be useful in schemachange, let me know.

[goranzdraveski]

Hey, @zanebclark, yes Out of Order parameter would be very useful.
From my experience nobody can guarantee the sequence of changes going into any particular environment and there can always be holdouts from various reasons(QA testing dint pass, it was postponed for what ever reason etc...). Out of Order concept deals with this kind of issues.