How should I avoid partial migrations in Oracle

[git-youzer]

I've recently started using FluentMigrator with Oracle.

I've discovered that some migrations will fail when using the donet fm tool yet still commit a portion of the updates.

I'm attempting to identify some general guidelines that will help me and others avoid partially failed migrations.

It seems that using scripts can introduce a significant amount of risk if too many changes are contained in that script. Also, multiple changes in a single Up method are risky.

Some of the guidelines that I'm discovering are the following:

1. Each migration should represent a single DDL statement.
2. Scripts should only contain one DDL statement.

Does this align with anyone else's experience?

[jzabroski]

@git-youzer
The problem is not FluentMigrator, but Oracle. Oracle does not have a transactional DDL. Postgres and SQL Server do. I think what you're experiencing is everyone's reaction when they use Oracle :)

In terms of how to avoid partial migrations, one answer you already guessed at is to cut up a single Up migration into smaller migrations.

However, even SQL Server can have partial upgrades of a database, since most of us run in transaction-per-migration mode. I have done 112 releases with about 500 hotfixes with FluentMigrator, and our secret sauce is that we test our migrations daily using shadow copies of the production database. I am not sure if there is a database cloning feature in Oracle or through some third party vendor, but you might want to check that out. It really cuts down on partial migrations even being a thing. I realize that in some industries access to the production data or a copy of it is prohibited or impossible. But it is the best option process-wise.

Other ways to mitigate migration failures is to have a Gitflow model, where only one branch gets pushed to production and there is only one "client" (such as a cloud service database). This again has nothing to do with FluentMigrator or Oracle, and is really about adopting processes that reduce the mean time to recover from failure by reducing special cases.

The only Oracle workaround I can think of that might solve your problem in a creative way is to always use Execute.WithConnection and inside the connection callback code, if an error is detected via the DbConnection, call System.Environment.Exit(1); Pretty dirty trick, but if you only need it ever so often, it might be useful. I personally wouldn't do it, but I am also not in your shoes maintaining Oracle databases.

[jzabroski]

@PhenX Any other ideas in addition to what I said above?

[git-youzer]

Thank you for the suggestion. I've considered wrapping the DDL changes in a try/catch, where the catch will call the Down method. It seems a bit simplistic, and I haven't given it enough thought to see if this would be feasible.

[jzabroski]

@git-youzer If I understand your try/catch wrapping idea, You can't do that, unfortunately. There is no monadic interface for Up/Down methods to capture the side effects of "collecting the execution specification" from "handling side effects from executing the specification".

When you call Up, most of the FluentMigrator API is merely building a specification for what to run, and promising it will run it until it encounters an error. Similar to creating a lambda, and then calling it. Except rather than use lambdas, we use classes that implement the IExpression interfaces to create and extend the "closure". It's actually similar to how some JavaScript libraries work in that regard.

The reason the Execute.WithConnection workaround works is its an escape hatch to execute arbitrary C# code, so you can execute a try { /* do connection */ } catch { Environment.Exit(1); } - a bit twisted, but true nonetheless that this should work.

I had ideas on refactoring fluent migrator to enable capturing side effects in the Up migration so it could execute as a script where you could transform the "side effects" of each individual call, but it is a lot complicated

[git-youzer]

Thank you for helping me understand the nuances of how the API merely creates the specification. I get it now. I believe I am overcomplicating the obvious issue of not having a clean copy of the target database each time I run a migration. I will work on putting together a copy of the target database prior to running migrations. It's the most logical approach. Thanks for the additional insight. Great library BTW!