Flyway Migration Issue: Handling Baseline Script Validation Post Schema Alteration

Fredrik_S

I have a baseline script (B001_baseline.sql) which was the first in a series of migration scripts. After this baseline script was generated and applied, a column (OldColumn) in an external database (not part of the project directly) was removed. I updated a view in my project that referenced this now-nonexistent column.

Now, when trying to generate a new migration script (to reflect the updated view), Flyway throws an error during validation, seemingly attempting to re-validate the baseline script against the current database state.

For context i use Flyway Desktop and the error occurs when i try to generate new migration scripts

Migration B001_baseline.sql failed

SQL State : S0001

Error Code : 207

Message : Invalid column name (OldColumn)

Attempts to Resolve:

• I have considered disabling schema validation for the baseline (validateOnMigrate = false), but I'm concerned about the implications for other scripts.
• I am hesitant to modify the baseline script as it has been executed in other environments.
• Creating a new baseline at this stage seems tricky due to existing deployment in multiple environments.

Questions:

1. Is there a recommended approach in Flyway to handle such a scenario where the baseline script validation fails due to schema changes made afterward?
2. Are there any Redgate Flyway Desktop-specific features or settings that can help bypass this validation error or handle the schema changes more effectively?
3. What are the best practices for managing and updating baseline scripts in Flyway, especially when dealing with external database dependencies?

Any insights or suggestions would be greatly appreciated. Thank you in advance!

Peter_Laws

Hello Fredrik_S,

Thank you for the excellent question!

Firstly, an implicit behavioural element that may not be immediately obvious, if you have multiple baselines, flyway will only use the newest, this is the preferred way to handle changing requirements.
By extension, but arguably optionally, is the idea of 'rebaselining' which is to consolidate all your existing changes into a new baseline.

This can be useful when as time progresses, the sum of all your migrations accumulates technical debt over time and due to ever-changing business requirements have become bloated with changes back and forth. As a result, drawing a proverbial line in the sand and reconstituting all those changes into a single baseline which becomes your new source of truth that you operate from can be very useful.

In practical terms that means you either package up your existing migrations and remove them from Flyway Desktop, your you start a new project for the purpose of generating this baseline. The exact choice of adding this to the existing scripts, or replacing them wholesale is best done on a case by case basis, how many environments it will affect is commonly the most impactful consideration.

Lastly, a couple of clarifications on some of your points.

• validateOnMigrate doesn't validate your sql code. What it does is prepend your Migrate verb with a validate verb, the purposes of which is to verify that the scripts available match the scripts that are known.
  I.E A safety net to highlight if anyone snuck an alteration into a script between when it was last run and now.

• The exception you're seeing is direct from the db, not flyway and as such can't really be ignored. You could potentially suppress the error if you intend to deal with it through some other means. But I would use that approach sparingly as you could easily make a rod for your own back by continuing instead of addressing the issues.
  
  See flyway error overrides