Alembic: How can I do a rollback if the migration fails?

[LostInDarkMath]

Hi there,
I've made a migrations which failed. As a result of the cancellation, my database is in a state that requires a lot of manual work to fix it. From my point of view it would be helpful if in case of a failed migration, the status from before the migration would be automatically restored.

Is there any way of doing that? I use MariaDB.

Have a nice day :)

[zzzeek]

this depends on your database and with MariaDB you can't roll back DDL statements like CREATE TABLE or ALTER: https://mariadb.com/kb/en/start-transaction/#ddl-statements the overall term is called "transactional DDL".

when alembic runs you will see this in the log "will use transactional DDL", that indicates if the backend is known to support transactional DDL or not. if it does, like Postgresql, then it runs all the migrations in one big transaction that will be rolled back if any of them fail. if not, like MySQL, or SQLite with the default driver behavior, it runs each migration one at a time and updates alembic_version each time so that if any of those migrations fail, you have at most one of them to clean up.

the strategy with MariaDB / MySQL is to always fully test your migrations on a development database that is built using the identical schema as the production database, and addtionally to create a full logical dump (using mysqldump) of your MariaDB database before running the migrations, so that if there's a problem, you can revert the database completely using the dumpfile.

[LostInDarkMath]

Hi @zzzeek ,
thank you for your very fast answer! I was afraid this would be the case. The strategy you present is the way to go I think. I also think that it would be nice to find this approach in the alembic documentation as well.

I will also try to go one step further and integrate the dump creation and loading in case of failure in the alembic workflow to do this automatically.

[LostInDarkMath]

Hi @zzzeek ,
based on our conversation above I changed in my env.py the lines

 with context.begin_transaction():
        context.run_migrations()

to something like

 db_tool.create_dump(file_name=backup_name, db_name=db_name_production)
with context.begin_transaction():
    try:
        context.run_migrations()
    except (OperationalError, ProgrammingError, IntegrityError, IndexError) as ex:
        print(ex)
        db_tool.load_dump(file_name=backup_name, db_name=db_name_production)

This works fine but is also executed during the autogeneration. I'd like to use this backup logic only if I do flask db upgrade and not if I run flask db migrate. But If have no idea how I can figure out inside my env.py which of those commands was used. Do you have any suggestions for me?

[zzzeek]

all of the command options are available at cmd_opts: https://alembic.sqlalchemy.org/en/latest/api/config.html#alembic.config.Config.cmd_opts

here's what it looks like:

(Pdb) context.config.cmd_opts
Namespace(cmd=(<function upgrade at 0x7f42ed4885e0>, ['revision'], ['sql', 'tag']), config='alembic.ini', name='alembic', raiseerr=False, revision='head', sql=False, tag=None, x=None)

the command being run and the arguments:

(Pdb) context.config.cmd_opts.cmd
(<function upgrade at 0x7f42ed4885e0>, ['revision'], ['sql', 'tag'])

command name is:

(Pdb) context.config.cmd_opts.cmd[0].__name__
'upgrade'

[LostInDarkMath]

Thank you for your answer!
But in my case (I'm using Flask-Migrate) it does not work:

>>> context.config.cmd_opts.cmd[0].__name__
AttributeError: 'Namespace' object has no attribute 'cmd'

But I found out that the following works for me:

if hasattr(context.config.cmd_opts, 'autogenerate'):
    print('Command: flask db migrate')
else:
    print('Command: flask db upgrade')

This is not an optimal solution since this could possibly handle other cmd commands incorrectly. But for now, it works for me as expected. Maybe you have another idea :)

[zzzeek]

that would indicate you're using an alternative command runner, so that's the only option you have. just code it defensively and you should be OK, you could even code it to accommodate either command runner.