sql 2021-08-30 | Slack Archive

isak17:08:58

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.

isak17:08:55

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.

isak17:08:45

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.

isak17:08:01

Right, makes sense

isak17:08:53

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

hiredman18:08:42

ideally that is all caught by running tests

seancorfield18:08:00

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.

hiredman18:08:07

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

seancorfield18:08:47

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

hiredman18:08:55

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

hiredman18:08:09

instead of merging master into your branch and running the tests

hiredman18:08:34

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

seancorfield18:08:30

@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...

hiredman18:08:07

I don't recall either

seancorfield18:08:42

(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)

hiredman18:08:49

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)

2021-08-30

Channels