I have a task to add some new migration scripts to the existing application using flyway, roughly as following:
There are 2 alternatives:
I like to keep these changes in 3 separate migration scrips for the sake of logical delineation and in case something fails in any of these scripts execution I don't have to create another large correction script to fix the failure(s) as it might get quite messy.
On the other hand, these 3 tasks all belong to the same piece of work (bigger parent JIRA task) and go to production together.
Could someone share their experience and opinion as to what is the best practice - keep 3 migration scripts or put it all together, with pros and cons of each approach if possible please?
I found this article, but it sheds no light to my specific query: https://dbabulletin.com/index.php/2018/03/29/best-practices-using-flyway-for-database-migrations/
This is going to partly opinion, so we'll just have to deal with that within SO and the rules.
I would absolutely break it up into smaller scripts. There's two reasons for this. First, when one of those sets of changes goes south (yeah, probably not, but what if), you can divorce that change from all the others much easier if it's in its own script. Of course you'd still group the changes so they're all 7.1, 7.2, 7.3 (or whatever), but being able to pull stuff apart is easier, if it's already pulled apart.
Second, if you get into a situation where you're doing piecemeal deployments through cherry picking, you're also going to want to break down the scripts into individual sets of changes. Same reason as above.
That's not to say I'd always, ever, only do individual changes. But the kind of disparate changes you're listing, ones that aren't related to one another directly and could, in fact, be pulled apart, those, I'd keep apart.
Partly opinion, partly an understanding of how things fall apart or need to be changed. I hope it's an answer within the SO rules.