Seeking Advice: Managing Branches and Migration Scripts Across Multiple Environments

• 
  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?

  David Atkinson March 08, 2024 10:29

  - 0 +
• 
  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.
  
  

  Fredrik_S March 08, 2024 10:44

  - 0 +
• 
  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.
  

  David Atkinson March 08, 2024 12:29

  - 0 +
• 

  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)

  Fredrik_S March 08, 2024 13:11

  - 0 +
• 
  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.

  David Atkinson March 08, 2024 15:47

  - 0 +
• 
  • 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)

  Fredrik_S March 08, 2024 15:53

  - 0 +
• 
  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.

  David Atkinson March 08, 2024 16:48

  - 0 +
• 
  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,

  Fredrik_S March 08, 2024 16:55

  - 0 +
• 
  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.
  
  

  David Atkinson March 08, 2024 21:19

  - 0 +

Add comment

Please sign in to leave a comment.