Mark migrations as idempotent

[fdw]

Hi, we are using Evolve for our product, so first some thanks is in order!

Unfortunately, we are in a situation where we have to support multiple versions of our product. Thus, we might be expected to hotfix an older version (f.e. v1.7), and of course we want to include the same hotfix also for the next release (f.e. v2.2). Just having the script in there is not enough, as it will not be executed for anyone that is already on v2.0.
However, duplicating it as v2.3 will lead to conflicts when the hotfix has been applied beforehand if the scripts haven't been written in way to make them idempotent.

We did think about using OutOfOrder, but as we already don't really know the version of the database, this will introduce many more possible migration paths and increase the overall complexity there. So we'd rather not use it.

Instead, as one possible solution, we could imagine to somehow mark "identical" scripts as such, even if they have different versions, so that Evolve will only execute them if the appropriate sibling script hasn't been executed before.

I know this is a very specific use-case, but I still wanted to ask if you have any ideas for our problem or if such an explicit marking would be helpful for others, too.

Thank you!

[lecaillon]

Hi @fdw
Indeed this is a very specific issue. I don't think a new special instruction can help anyone but you :)
Do you use Evolve as a NuGet package ? (or the Tool or the CLI) ?

[fdw]

Thanks for answering, @lecaillon!

We're using Evolve as a NuGet package in a slightly larger application.

[lecaillon]

I was thinking about the new feature i'm adding : use its own custom IMigrationLoader. It could do the trick in your case maybe.

But in fact, you could just add a V2_3 migration script that checks if the V1_7 hotfix has already been applied. Query the Evolve metadatatable in order to do that. Something like:

IF NOT EXISTS (SELECT * FROM changelog WHERE version = '1.7') THEN
    -- apply the hotfix
END IF;

Something to test I guess

[fdw]

That would be quite a nice solution, thank you very much :)

[lecaillon]

Keep me in touch