Recommended practices
Published 18 March 2022
- After auto-generating a migration script, never edit it to include new schema changes. Always make the changes to your development database first, then save to schema model, and then generate the migration script.
- Be careful with branching. Best to start simple and avoid branching unless you need to/really understand what you're doing. See Switching Branching for more information.
- Generate migrations from the same branch to avoid conflicts. The schema model could catch conflicts, but not necessarily and you may need to edit version numbers, which could create additional work to straighten out.
- The filename description defaults to your username. It's nice to give this a clearer description to more easily understand the contents of your repo.
- Always protect your credentials. You can leverage the CI/CD system to do this using variables.
- Rolling forward: In the event there was a bad change in a migration script that has been applied to some of the downstream environments (e.g, Pre-Prod) and you don't want it to be deployed any further, the recommended workflow is to:
- Apply the undo script to all the environments the script was applied. For example, if your pipeline is Test > QA > Pre-Prod > Prod, then you would have to undo Test, QA, and Pre-Prod. Ideally this will catch everything in the migration. You can also use our comparison tools to check how the environments differ from Prod or an environment without this change to make sure everything is undone.)
- In local filesystem, delete the bad migration script. This will cause the Shadow DB to be cleaned and rebuilt the next time you refresh the Generate Migrations tab.
- Commit and push this change (or use a Pull Request) to the remote repo so this bad migration script is no longer in the repository.
- Correct the Development DB to the desired state in the IDE of your choice (Visual Studio Code, SSMS, SQL Developer, etc.).
- In Flyway Desktop
- Save the changes made in the Development DB to the schema model.
- Generate a new migration script.
- Commit and then push this change (or use a Pull Request) to your remote repo.
- This new script will now flow through the pipeline as normal.
- It's best to explicitly name your constraints. You can read more about this in the Bad UNDOs section in this article.
- If you're using Oracle, or a database that auto-commits DDL, then it might be best to have one change per versioned migration script so that if the script fails, then you're not left in a state where some of the migration script is executed and the rest is not and you have to fix up the release.
