[Question] Migrating to arbitrary version

[kshpytsya]

In our project, the "D" in "CI/CD" stands for "delivery". The build pipeline produces Docker images which, per this reasonable suggestion also include Alembic migrations. Deployment is managed in GitOps-y way with a dedicated repository describing which image versions are deployed to which environments. It is my intent to perform DB migrations as one of the steps in deployment pipeline. While migrations in production environment are expected to be linear upgrades, for our dev/sandbox environment I expect situations with having to first deploy image version containing "A->B" (let's call it image v1.feat1) and then that containing "A->C" (lets call it image v1.feat2) migrations. The problem here is that v1.feat2 knows nothing of migration B and v1.feat1 knows nothing of migration C, making it impossible to use either of those two images to perform migrations necessary to switch between these two images. Therefore one possible solution I see is the following three-step procedure:

• Alembic in the image containing the version to be deployed ("new") is queried to report entire migration version graph known to it and the head migration version.
• Alembic in the image containing already deployed version ("old") is supplied with information from the first step and is requested to perform all the migrations it needs to reach some version that is known to the "new" image.
• If the "old" image doesn't know of the head migration for the "new" image, alembic in the "new" image is requested to perform remaining migrations.

The drawback of this solution is that migrations are not going to be performed in a single DB transaction. Other possible solutions are:

• Two steps:
  • The "old" image is queried to export all the migrations in some Alembic-specific format
  • The "new" image is supplied the file produced by the previous step thus making it capable of dealing with current DB versions it wouldn't normally know of.
• alembic/versions/*.py files are copied into the same directory of the new image.

I am not sure whether these alternative solutions would not have any issues with different versions of Alembic (or some other library, or even version of Python) in the "old" and "new" images. These solutions have the benefit of performing all migration steps in a single DB transaction.

Of course, regardless of the approach taken, Alembic would still need a "deploy" (tentative name) command instead of or in addition to "upgrade"/"downgrade".

While targeted at dev environment, the same mechanism would also work for downgrades in production (which are hoped to be exceptional situations).

This may be already a solved problem, but questions like this make me doubt it, and I would also expect to find something in the cookbook.

[zzzeek]

the stack overflow question refers to someone who doesn't know if their current migration target is an upgrade or a downgrade, relative to the current state of the database. they of course should look at "alembic history" to see where the database's current version is relative to history, but downgrades are not really a normal part of automated deployments and they are very much separate from "upgrades". your issue seems to be related to deployment, so I dont see how that question applies here.

if you have an A->B A->C kind of problem, those sound like branches. if two branches of code have diverging alembic trees, you first merge them into a common merge point and alembic can move to that point. I would check out https://alembic.sqlalchemy.org/en/latest/branches.html

[kshpytsya]

I hoped it would be obvious from my description, that in my situation, deployment pipeline, which I intend to be the place where alembic migrations are executed, doesn't know whether it is "upgrade" or "downgrade". It just knows the version of our application it needs to deploy and also the version that is currently deployed. So I am looking for a way to be able to apply a necessary sequence of migrations that would bring the database from "old" to "new" version. One of the situations is the need to switch from "A->B" to "A->C", which would involve "downgrade B->A" and then "upgrade A->C". As an extra complication, one image, containing alembic knows only of ["A", "B"] and another one knows only of ["A", "C"].

For prod I mostly expect regular upgrades, however, there is a possibility that we would need to do a roll-back to previous version which would imply doing downgrade. But for dev we need to be able to conveniently switch our single dev cluster between different branches of our app, which would also possibly involve abovementioned db migrations.

This seems to be remotely related.

[zzzeek]

OK so...you need to run one operation and have it do either upgrade or downgrade? as mentioned, use the output of alembic history, which you can also link into programmatically. I would look at the source code to the command and see how to programmatically walk the whole tree and compare it to whatever version the database is at:

alembic/alembic/command.py

Lines 452 to 519 in 3d9b112

	def history(
	config: Config,
	rev_range: Optional[str] = None,
	verbose: bool = False,
	indicate_current: bool = False,
	) -> None:
	"""List changeset scripts in chronological order.
	:param config: a :class:`.Config` instance.
	:param rev_range: string revision range
	:param verbose: output in verbose mode.
	:param indicate_current: indicate current revision.
	"""
	base: Optional[str]
	head: Optional[str]
	script = ScriptDirectory.from_config(config)
	if rev_range is not None:
	if ":" not in rev_range:
	raise util.CommandError(
	"History range requires [start]:[end], " "[start]:, or :[end]"
	)
	base, head = rev_range.strip().split(":")
	else:
	base = head = None
	environment = (
	util.asbool(config.get_main_option("revision_environment"))
	or indicate_current
	)
	def _display_history(config, script, base, head, currents=()):
	for sc in script.walk_revisions(
	base=base or "base", head=head or "heads"
	):
	if indicate_current:
	sc._db_current_indicator = sc.revision in currents
	config.print_stdout(
	sc.cmd_format(
	verbose=verbose,
	include_branches=True,
	include_doc=True,
	include_parents=True,
	)
	)
	def _display_history_w_current(config, script, base, head):
	def _display_current_history(rev, context):
	if head == "current":
	_display_history(config, script, base, rev, rev)
	elif base == "current":
	_display_history(config, script, rev, head, rev)
	else:
	_display_history(config, script, base, head, rev)
	return []
	with EnvironmentContext(config, script, fn=_display_current_history):
	script.run_env()
	if base == "current" or head == "current" or environment:
	_display_history_w_current(config, script, base, head)
	else:
	_display_history(config, script, base, head)

alembic's API within there that's not underscored is public so I would look through to get the bits that you need.

[kshpytsya]

Do I understand it correctly that you consider this to be outside the scope of Alembic itself, despite me being not the only one with such a request? I just would like to know official position before starting to implement my own bicycle.

[zzzeek]

guessing for a certain ID if we should use upgrade or downgrade automatically, I think that's out of scope yes.

[CaselIT]

Do I understand it correctly that you consider this to be outside the scope of Alembic itself, despite me being not the only one with such a request? I just would like to know official position before starting to implement my own bicycle.

you mean something like alembic move_to arbitraty_id? where arbitrary it may be in the after the current revision (so upgrade) or before it (so downgrade)?

[kshpytsya]

Digging through Alembic issues found this one:

#464 (comment)

so it seems like this is a recurring idea. It's a shame that it doesn't get traction with Alembic's authors.

P.S. Looking at this PR probably gives an idea of complexity of proper implementation of this feature.

[zzzeek]

re: issue #464, that was a very complicated add, and that submitter actually contributed the code and spent many weeks collaborating on its implementation.

As I mentioned previously, given a single identifier and determining if it's "up" or "down" based on the current version in the alembic_version table is relatively trivial.

If, OTOH, the feature request, which I had a lot of trouble understanding from the description, is that some database is at some version that is both "up" and "down" at the same time relative to some other alembic version tree that represents a later version, that feature is not possible; the migration files themselves would not be compatible with such a database and that idea is a non-starter. Since you stated you think this problem is of similar complexity to #464 makes me think that might be what you're thinking of.

[kshpytsya]

I am totally confused as to the both "up" and "down" at the same time part, which I attribute to my limited understanding of vast possibilities of Alembic (ab)use.

[kshpytsya]

Just to give a broad picture: I want to have a system that "Just works"™, meaning that developers that do not change any db schemas in the code should not know anything about migrations at all, those devs that change db schemas should know how to create proper alembic migrations (probably with restriction on having a single head) locally (there will be a local instance of the same db engine running on dev's machine), and only if something goes wrong, a specially trained person should do the investigation as to why and how. Deploying arbitrary versions of the application should not become more complicated due to performing db migrations being one of the steps done by deployment pipeline. Of course, there is a possibility of data loss but that's what continuous DB backups are for.

[zzzeek]

alembic already does all those things. I dont understand the part where someone has version 1.1 of the app, then downgrades to an older version of it, and somehow does not know if they need to type "upgrade" or "downgrade" . if developers are working on branches that have divergent db migration files, then by definition they are devs that "change db schemas" and they have to create merge points.

[zzzeek]

Alembic's branching model is described at https://alembic.sqlalchemy.org/en/latest/branches.html . As you noted, in #464 a contributor came in to improve some aspects of this, however that developer was able to provide us with a very specific scenario, an exact branch structure and the exact operation they wanted. After that, I still did not have the resources to provide this feature but I worked with them on getting it completed.

So far the descriptions of what is desired here have been quite vague. I would suggest you dig into specific scenarios that are not working and determine what exactly needs to happen first in order to satisfy those scenarios.

I would also note that alembic is not the only migration tool out there. Django's tool is much more automated and generally is perceived as very easy to use. Alembic is not. I am actually fine with that, so you might want to look into what they do as it is pretty much the "push button" experience you seem to be looking for.

[kshpytsya]

. I dont understand the part where someone has version 1.1 of the app, then downgrades to an older version of it, and somehow does not know if they need to type "upgrade" or "downgrade" . if developers are working on branches that have divergent db migration files, then by definition they are devs that "change db schemas" and they have to create merge points.

The thing is that deployments of versions that introduce schema changes may not always be done by people developing those feature branches.

Let me remind that we have a single shared dev environment where all the devs can deploy. They do not step on each others toes by communicating via messenger. Consider the following scenario:

• Alice develops a feature branch that includes changes to DB. Eventually she deploys it to check it on real system or to let other developer Charlie that work on other apps to try out new features. Alice is done for the day, keeping her feature branch deployed.
• Bob is working on some other feature (that doesn't change anything in DB) and wants to deploy. He asks whether anyone is using the app on dev, and deploys his version. This causes an alembic downgrade of which Bob knows nothing about.
• Charlie needs to interact with Alice's branch again, and, after making sure it is ok to deploy, deploys Alice's version (or asks Bob, as Charlie may even not know how to deploy the app). This causes alembic upgrade of which neither Charlie nor Bob know about.

Similar scenario can be with A, B and C db migrations and a need to switch between feature branches containing B and C (and possibly back). And that switch can be done by Daniel, who just knows of feature branches and knows nothing of migrations. He just knows how to activate the deployment pipeline specifying desired app version.

[zzzeek]

then you are building something very custom so again I suggest using Alembic's API to pull in the current version number and make these special decisions programmatically.

[zzzeek]

also noting, if there were a feature to add to alembic here, I dont have the resources to implement myself, so if you work with the API directly here, that's where the beginnings of a "feature" would start in any case. I suggest getting the thing you want working first. Getting it to be "officially part of alembic" is secondary. Getting someone else to actually do the dev work here, that's not happening. I dont have development resources for unusual new use cases.

[kshpytsya]

Ok, agree to disagree, let it be a custom solution.
Now, a practical question. What would be a proper way of migrating from version "A->B A->C B->D C->D" to "A->B"? I believe it should be just "undo the changes introduced by A->C". How would I compute that set of operations and how do those operations map into Alembic's API?

[zzzeek]

if you have a version of the app that has generated this migration structure:

A->B         -> D
 ->C@branch1 -> D

D by definition is a mergepoint.

either way, that app has a "downgrade" where you can downgrade back to:

   A->B

that's where the code to do this is. the feature we did in #464 should allow this operation exactly, by doing "alembic downgrade branch1@A". Now if the mergepoint gets in the way for that specific case, I'm not sure. let me know if it does. not sure what the solution would be as I deal with this branching stuff basically never.

[kshpytsya]

Kind thanks for explanations and for Alembic. So, my plan is to implement the desired logic myself. I will chime in here if I hit some edge cases that will cause me doubts.