I have a task to add some new migration scripts to the existing application using flyway, roughly as following:

1. Update 4 records in one table for one logical task (JIRA ticket #1)
2. Update another record in the same table for another logical task (JIRA ticket #2)
3. Update record(s) in another table for (JIRA ticket #2)

There are 2 alternatives:

1. Keep the changes 3 in separate migration scripts.
2. Lump all the changes together in one large all-encompassing script.

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/

CodePudding user response：

This is more opinion that hard fact, but what I've usually come across as "best practice" is to keep scripts to either a feature or dependent commands. This way if a command breaks the migration, you don't loss too much progress and can help pinpoint where your issue is.

CodePudding user response：

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.