Automatic migration ordering (Alan: Making Alembic migrations more team-friendly)

[awoimbee]

In this article: https://medium.com/alan/making-alembic-migrations-more-team-friendly-e92997f60eb2
Engineers from Alan explain how they customized Alembic to be more "team friendly", meaning: no more branches needing resolution.

In the end they have a customized Alembic setup that allows for automatic migration ordering (along with storing metrics about migrations).

What does the community think about this ?
What does the Alembic team think about this ?
I know I would love something like that !

[CaselIT]

hi,

I have skimmed the article, great that it works for them.

There are some things mentioned there that are already supported by alembic, like 1 is the file template in the ini file https://alembic.sqlalchemy.org/en/latest/tutorial.html#editing-the-ini-file
The point 3 seems to be mostly #309 and #1075 that were never implemented

For other parts is seems that the main idea assumes that a migration can run whenever, and do not depend on other ones. If that's the case then the migration ordering is a lot simpler.

Overall the maintainer of alembic do not have the resources to implement and/or support another revision model.
For point 3 I think there would be appetite to have it directly in alembic if it were contributed, but for the rest of the change an extension or a fork is likely the best avenue.

So I guess from your point of view, you could ask if they are willing to open source the changes they have made

[CaselIT]

edit: regarding 1, it seems that they wanted to change the actual revision number, not the file name, so nevermind

[zzzeek]

well, comments:

At this point, someone would say on Slack “hey, build is broken on the main branch, hold on everyone I’m fixing”.

I think this is the key issue and I can think of ways that this can be more simply flagged in CI by introducing a new file that will create a git merge conflict, so that the need to run alembic merge would be highlighted before the conflicting branch can be actually merged to main. I can think of simple cookbook recipes or perhaps a built in feature that could generate such a file.

migrations are numbered with something that makes them roughly sorted naturally

Alembic was explicitly designed to replace sqlalchemy-migrate which used numbered migrations and was immensely difficult to deal with, replacing numbering with a linked list instead. Datestamps are of course not the same as "numbered".

but we don’t care much about the order of migrations introduced the same day/week

That seems like a huge problem? Migrations absolutely can have dependencies on each other and this is not based on whether or not the migrations in question were introduced years or hours apart

we get rid of the strict dependency between each migration; we store explicitly all the migrations that have been run;

This sounds like what you get when you run multiple roots in alembic. i think we might have had people talking about storing all migrations as their own root. the flow / UX is not optimized for this however

  revision = "20211110161024"  
  down_revision = find_previous_revision(revision)

well that's interesting

But we actually did all this work so we could insert migrations in the middle of the chain without worrying about it, and with no annoying synchronization needed.

I dont totally get this. They are essentially changing the numbering scheme so that it naturally merges by exact datestamp of a migration. I can see ways to just have the usual branch / merge flow which uses a simple utility to determine this for you, without using a weird implicit date scheme that will hide problems, and as mentioned above, the issue that it gets merged into main without alerts can be resolved without rewriting Alembic's whole versioning model. I definitely dont think it's safe to just say "we dont have branches and every migration just flows implicitly, with no operator alerts in a shuffled form", the operator definitely has to know that someone else also added a migration on the same root as yours. Maybe these folks just havent worked on the kinds of database systems that I have.

The full implementation is not super interesting as it relies a lot on Alembic complex internals, but we did basically 3 things:

good for them! they should release on pypi.

My overall impression is:

1. they should release their tool on pypi and support it
2. for the actual problem they wanted to solve, which is, "branching and merging is slightly inconvenient", I think they went way overboard and were clearly very colored by their preference for Ruby on Rails so this falls into a very familiar NIH-ish bucket for me
3. if the issue is that the code merges to main and the conflict isn't seen before then, adding a simple file with a list of version numbers or some kind of hash of the whole version directory will generate simple git conflicts and alert the need to run alembic merge.
4. I think it's a bad idea for the tool to have no alerting system at all that two competing versions are both trying to build off the most recent version without them being checked by an operator.

[zzzeek]

a migration not working means the software ran on a database and if you dont have transactional DDL it put it into a corrupted state. even if this is a staging database that is a much bigger headache than the software just didnt run in the first place and did not corrupt the database.

[zzzeek]

also wondering if you have a comment on my proposed (much much simpler) solution to the issue

[CaselIT]

1. Any big project will have staging environment(s) where migration issues will become apparent.

unless they don't.

overall it seems that the premise of their change is this

but we don’t care much about the order of migrations introduced the same day/week

as I wrote before, it you can make this assumption things are much simpler. Alembic doesn't make that assumption. You could argue that it should be an option, but as I mentioned at the moment the alembic team doesn't have the resources to make such a change

[zzzeek]

I mean yeah they are using just one migration root too. A system that maintains different sub-schemas might like to have multiple roots, this was one of the use cases I think we were asked to support and this flow has been used in openstack

[awoimbee]

also wondering if you have a comment on my proposed (much much simpler) solution to the issue

About creating git conflicts ? I'm not expert, but I'm all for it, it would prevent us having to check the revisions and write "don't forget to update the down revision" at review time !