Feature: deployment rollback - saving the previously migration hash on DB for downgrade

[staticdev]

Describe the use case I have a Python application using SQLAlchemy and Alembic for DB migrations, it runs on Kubernetes and gets deployed by ArgoCD. Before the application is deployed there is a pre-sync hook to run migrations from last merged changes on main branch. So it will go from on migration hash X to Y (if there was migrations to apply). I want it to execute a down migration to the initial hash X if something fails on deploy. In order to do that, I need to create an extra table to remember the initial state (migration hash) and use two custom scripts to get and update after migration is successful.

It would be excellent if alembic had a built-in option to add this extra column/table on the DB and could be able to go back to previous hash.

Databases / Backends / Drivers targeted

It should depend on a specific database/backend or driver.

Example Use Already in the description.

Additional context I am available to further discuss this.

Have a nice day!

[CaselIT]

Hi,

I think this more or less a duplicate of https://github.com/sqlalchemy/alembic/issues/309

(please confirm if so)

[staticdev]

@CaselIT for me it is not at all a duplicate. What I am asking is much more simple. I don't need a history of timestamps, but just the previous hash before alembic upgrade head. Also it could even be the same hash as currently applied migration, this will happen when you do alembic upgrade head and there is no new migration. Then if I deploy something to prod and deployment failed, I will do alembic downgrade last to the same current hash, and alembic will know there is no down migration to apply.

[CaselIT]

I'm not sure if something integrated to alembic is the best option here.

You can already do what you want using alembic current before running the upgrade, saving the output to a file or similar location. If the upgrade fails you can then use the saved revision to run a downgrade.

@zzzeek do you have suggestions on this?

[zzzeek]

as far as making it possible to have a record of what the previous hash was, we absolutely would do #309 here, since adding a new table to alembic would be a very big deal and we'd only want to do that once, and have this use case be a part of that, so in that regard this is a dupe. there is not "a previous hash" in alembic's model, it can be any number of hashes as we support a branching model with multiple bases in addition to independent movement along those branches, so the problem is not anything like "store the previous hash" in the general sense (not to mention a single alembic upgrade command can move over many revisions, and the revision at the start of the command as well as each revision in the series are both significant in rollback scenarios).

I agree re: @CaselIT running alembic current, and even simpler the output from the alembic upgrade command prints out the version identifiers as it runs in any case, you could just capture that from the command output.

we don't appear to have a cookbook recipe for this however at some point we added a hook for the explicit purpose of logging individual revision changes at https://alembic.sqlalchemy.org/en/latest/api/runtime.html#alembic.runtime.environment.EnvironmentContext.configure.params.on_version_apply . So for now you would create such a callable that writes to the database the additional information that you want, which could be as simple as version identifier + timestamp. We added this hook to support the Audit-Alembic project which does a history audit right now, you can look at its use of the hook to write something that suits what you need right now.

[staticdev]

@zzzeek @CaselIT I am still not convinced that implementing #309 would solve the mentioned use case. Since ArgoCd is stateless with regards of what happened before the deploy so a db migration would neither store alembic current hash not the timestamp to search on audit logs of migration history would be remembered. So really having the last hash, for me, would be the solution.

I would also have to double check if Audit-Alembic saves to history an alembic upgrade head even if no migration were applied since I would say this is the edge-case and also the most common scenario of a more stable application. Regarding that, I can post the results here later.

[CaselIT]

I am still not convinced that implementing https://github.com/sqlalchemy/alembic/issues/309 would solve the mentioned use case.

That feature would be used as the base to implement anything like this built directly in alembic

Since ArgoCd is stateless with regards of what happened before the deploy

I'm sure that it supports artifacts that could be used to perissist the output the commands

[staticdev]

I'm sure that it supports artifacts that could be used to perissist the output the commands

Based on what? Do you mind sharing some documentation or some evdence?

[CaselIT]

I've never heard of it before this issue, I've just assumed it. It would be very strange and overly limited a ci thwt does not support artifacts of some kind.

[CaselIT]

Also literally the first result for a search for "ArgoCd artifacts" is https://argoproj.github.io/argo-workflows/configure-artifact-repository/

[zzzeek]

re: audit-alembic, I dont know what it actually stores. I referenced this only as an example so you can use the on_version_apply hook to roll what you need.

[staticdev]

@zzzeek I had a deeper look in Audit-Alembic and it is just a broken code/prototype that uses some functions deprecated on Python 3.6 (https://github.com/jpassaro/Audit-Alembic/blob/master/src/audit_alembic/base.py#L25 / https://docs.python.org/3/library/inspect.html). It was last updated 6 years ago, so it makes sense.

@CaselIT now I will try a script using alembic current to create an artifact actually with Artifactory and see how it goes. But to me this solution still looks pretty much a complicated workaround to a simple extra column last_upgrade_state that could be built-in and enabled by a flag.

[CaselIT]

But to me this solution still looks pretty much a complicated workaround to a simple extra column last_upgrade_state

How would that column help?

Alembic is pretty extensible, so feel free to use another way. My suggestion was just a suggestion, but I make no claim that it's the only solution or even the best one

[zzzeek]

But to me this solution still looks pretty much a complicated workaround to a simple extra column last_upgrade_state that could be built-in and enabled by a flag.

"Built-in" where exactly ? How would this "column" represent the state of a point in time when that point in time might encompass any number of revisions ? How would its value relate to an "alembic upgrade" operation that ran through dozens of revisions, would it store the state before the command itself ran or before a particular set of upgrade operations ran, what would happen if I ran two upgrade operations for two different branches, it would just blow one away ? What if I happened to want to rollback to the previous three upgrade operations?

There are issues with this request both in how extremely specific it is to one very particular use case you have that is very arbitrary, as well as that it seems to not take into account the real world data model that alembic uses. We've also supplied three different ways you can achieve this use case with alembic right now - the third approach, to use a custom hook to write this "column" yourself, is almost exactly how we would begin implementing such a feature as you are requesting - that is to say you have the power to implement this right now and use it as a prototype to show us much more specifically what you had in mind.

[staticdev]

Maybe my description made it look super specific let me try to fix that:

Let's suppose you have a generic application with continuous deployment. Every new code merged is deployed. Before deployment I need to apply migrations from last change-set (it could be 0, 1, 2... migrations) with an alembic upgrade head. Then I do a deployment and run some smoke test, if tests fail I would need to go be to the migration before I did the alembic upgrade head.

Alembic only has current state, if it also saved last state previous to applying all new migrations then problem would be solved. Alembic needed to update this extra column with alembic current hash so, in case I need to go back to this migration, I will execute alembic downgrade LAST in a future stage of my CD pipeline. For that, we don't need timestamps or nothing fancy at all, actually cannot think of a more straightforward and simple solution. Also IMHO it is super generic, does not matter which tool you need for automated deployment actually if you follow this practice would be a great feature.

[zzzeek]

Maybe my description made it look super specific let me try to fix that:

Let's suppose you have a generic application with continuous deployment. Every new code merged is deployed. Before deployment I need to apply migrations from last change-set (it could be 0, 1, 2... migrations) with an alembic upgrade head. Then I do a deployment and run some smoke test, if tests fail I would need to go be to the migration before I did the alembic upgrade head.

yeah that's a really specific use case. easy to do by just calling "alembic current" ahead of time.

Alembic only has current state, if it also saved last state previous to applying all new migrations then problem would be solved. Alembic needed to update this extra column with alembic current hash so, in case I need to go back to this migration, I will execute alembic downgrade LAST in a future stage of my CD pipeline.

There is not a single hash for alembic current. it can be any number of hashes simultaneously.

For that, we don't need timestamps or nothing fancy at all, actually cannot think of a more straightforward and simple solution. Also IMHO it is super generic, does not matter which tool you need for automated deployment actually if you follow this practice would be a great feature.

We would implement #309 and then allow for new tags specified for alembic downgrade / alembic upgrade that allow for specific points in time from the history table.

for now please use alembic current, thanks

[aodj-kara]

@staticdev did you ever get anywhere with this issue?

[staticdev]

@aodj-kara I implemented my own by saving into database last successful migration applied as a job in my CD pipeline. If something fails it will also read from the DB and rollback.

[ysampell]

Hi @staticdev , I’m seeking a similar solution for my FASTAPI application deployed in AKS. Could you provide more details on how you implemented automatic rollback for Alembic versions? I’m considering using GitHub workflows but need guidance on the approach.