Fork me on GitHub

How do people normally handle database migrations where developers are writing migrations at the same time, and then need to migrate their databases even though the database version they wrote the migration for might not be the same? E.g.,: 1. Developer A writes a 'drop table Foo' migration 2. Shortly after, without knowing about 1, developer B writes a migration that drops Foo, but also has some other changes It seems like for this reason you'd want to have a db version counter (so not a timestamp) that all developers need to sync up on to write migrations, so that 2 would result in a merge conflict. But also then I guess they would need to manually update their database? (For example, developer B would have already ran his migrations before pulling the latest code, so how does he get synced again?)

Russell Mull17:08:27

In my mind, there are two separate issues here: 1. The migrations must have a total order. Timestamps are a reasonable way to accomplish this. 2. You need a way to author your migrations so that conflicts don't happen. Tbh, I think this is a social problem, and trying to apply a technical solution to it will do more harm than good in the vast majority of cases.

Russell Mull17:08:06

If you really need a technical solution, then your 'db version counter' is reasonable. I'd probably put it in a dedicated file in source control. You're right that people will need to roll back their migrations, and deal with conflicts. I've experienced this myself, and it can be pretty painful at times.


Doing it by timestamps would require a table to record the migration timestamps you have executed, which wouldn't be needed with the counter / merge conflict approach. Though I guess a benefit is your tool could let you decide whether the new migrations are OK to execute in the 'wrong' order (without hassling you with a merge conflict)

Russell Mull17:08:46

Yep. This is handled by cleanly by migratus.


Oh really? Nice. It was helpful to get your thoughts on this, thanks @U7ZL911B3

👍 1
Russell Mull17:08:33

Fwiw, even with a 'db version number' approach, it's good practice to have a table of 'verison number, timestamp' at least, so you have some kind of audit trail.


Right, makes sense


I can see merge-conflicts plus database resets on desync working here, just wondering if anyone has a different solution that works


ideally that is all caught by running tests


As Russell says in that thread, it's really a social problem and at work we always announce in our Slack dev channel when we are planning to add a new DB migration. Ours are just sequentially numbered. We have a task in our build.clj file to create the next new migration filename automatically.


so you won't merge if something is broken, and you'll fix what is broken


(and we should catch conflicting migration numbers in PR reviews if it ever happened that we did add two with the same number)


but I dunno, so many ci setups run the tests just on your branch, and then run on the tests on master after merging


instead of merging master into your branch and running the tests


which is what you need to do to catch conflicts like that


@hiredman Did we establish whether BitBucket Pipelines does the master merge when testing a branch? I seem to recall we discussed what it was doing but I don't recall the outcome...


I don't recall either


(although even that won't necessarily catch two active branches that both add the same numbered migration -- until after the merge and the second one will fail CI because its migration will not be applied)


it does look like we merge master before running the tests (looking at the pipeline output there is a git merge of some random sha, which I assume was master at the time)