Seeking Advice: Managing Branches and Migration Scripts Across Multiple Environments

Fredrik_S

Subject: Seeking Advice: Managing Branches and Migration Scripts Across Multiple Environments

Introduction:

Our team follows a branch-per-environment strategy for development, including main (prod), dev, and qa (quality assurance) branches. We utilize Redgate Clone containers for development within feature branches and Flyway Desktop for schema migrations. We're encountering challenges with migration script management across environments. We're seeking suggestions to streamline our process.

Development Process Overview:

• Initial Development: Takes place in Redgate Clone containers on feature branches derived from the main branch
• Schema Updates: Utilize Flyway Desktop to update the schema model within the feature branch.
• Integration to Dev Environment: Merge feature branch into dev branch. Generate dev-specific migration scripts using Flyway Desktop. Deploy scripts to the dev environment and commit them to the dev branch.
• QA Testing: Merge the feature branch into the qa branch for QA-specific migration scripts and deployment.
• Production Deployment: Post QA approval, merge the feature branch into the main branch. Generate production migration scripts and deploy to production. 

Objective: Our strategy aims to allow environmental divergence by generating specific migration scripts per branch/environment, and ensuring feature branches remain clean for conflict-free merges into the main branch (no merging of dev or qa into feature before merging back).

Challenge:

Migration script management is problematic. Creating a feature branch from main inadvertently includes production migration scripts, complicating merges into dev and qa with misplaced scripts. Attempts to isolate prod scripts in a dedicated branch failed, as Git then tries to delete migration scripts in dev, qa, and prod branches during merges without migration scripts.

Seeking Suggestions:

• Process Adjustments: Are there modifications to our workflow that could resolve these issues?
• Tool or Strategy Recommendations: Is there a different tool or strategy better suited for managing migration scripts across multiple environments?

Thank you in advance

David Atkinson

Hi,
What do you mean by "production migration scripts"? Are ther scripts that should run on all environments, and some scripts that should only run on specific environments? If so, can you give an example of what might be applied to production but shouldn't be applied to Dev or QA?

Fredrik_S

we generate migration scripts for each of our environments and save them in there respective branch (dev, qa and main (for prod)). When we create a new feature branch from main it contains the migration scripts generated for our production environment, when we den merge our feature branch into our common dev branch the migration scripts generated for prod are merged into our migration folder for the dev environment. 

So each environment should have its own set of migrations scripts and the problem is that when we merge in our feature branch int contaminates the environments migration scripts with migration scripts generated for production.

David Atkinson

I'm not sure I understand why each environment needs to have a different set of migration scripts. Ideally you want to aim for the same migration scripts being used to update all environments as knowing the same migration scripts have been exercised in lower environments will provide you with maximum confidence that the eventual deployment to production will run as expected.

Fredrik_S

Our problem is that we often get development requests that for some reason or other gets stuck in development and therefore that environment might differ from our production environment. So, if I generate migration scripts to work for our dev environment, they may not be suited for our production environment. To minimize the risk for problems during deployment to production we felt it was easier to generate migration scripts separately for each environment.

There is also the point that if you have to go back and forth a lot in an feature you are developing (it did not pass integration test for example) you end up with multiple migration scripts for the same feature, but when you come to production all your back and forth changes gets rolled into one migration script (because what we save an merge is the schema model)

David Atkinson

Which edition of Flyway do you use (Community, Teams or Enterprise), and what database are you using it with? This will help me suggest options.

Fredrik_S

• We user Flyway Desktop Enterprise edition.
• Our different environments PROD, QA, and DEV is in SQL Server 2019.
• For our individual development databases we use Redgate Clone (data image based on PROD)

David Atkinson

I would strongly recommend using the same scripts in all environments. If you have migration scripts in dev that need to be rolled up, best to delete them, recreate your development database and generate a new script. 

An approach is to avoid creating migration scripts in development, as these are likely to need to be deleted and recreated after the "back and forth". Just develop using the schema model, and only when you're confident that the changes need to be promoted to higher environments do you generate the migrations. 

Fredrik_S

Big thank you for your suggestions. 

Just to make sure I understand, when you say “develop using schema model” do you mean in point my feature branch to the dev environment and use apply to database? 

Secondly do you recommend generating the migration scripts in the feature branch?

Best regards,

David Atkinson

By "develop using the schema model" I mean that developers only save changes to the schema model, commit/push these to the branch, and other developers use apply to database to sync their database with other's changes. Avoid using migration scripts until there's a level of confidence that they're ready. 

Migration scripts can be created in the feature branch. That's entirely optional. If the developer who has made the changes is confident to generate the migration scripts, I'd suggest the right time would be in the feature branch, before merging. If they're not created at this stage, then the migration scripts can be created after the (schema model) changes are merged to the dev branch. This would be appropriate if a different developer is charged with creating the migration scripts.