Flask-migrate: Should be easier to run upgrades with one transaction per version

[quantoid]

When starting from a blank database, some data migration versions fail because they rely on tables created by previous versions. This seems to be because all version upgrades are done in a single transaction.

Example:

• Version 1 initialises the database by creating a "contact" table for the Contact class.
• Version 2 creates a bunch of pre-defined Contact objects in the database.

When starting from a blank (PostgreSQL) database, version 2 fails because the relation "contact" does not exist yet. If you run the two versions as separate upgrades then it works fine.

It's possible to get Alembic to run migrations with one transaction per version. This makes a lot of sense because each version should stand alone, so if one fails you want to roll-back that one but not all the previous versions that succeeded. However, it's difficult to work out how to configure flask-migrate to do a transaction per version.

IMHO one transaction per version should be the default, but I'd be happy just with an easy way to enable it.

[quantoid]

Suggested solution: add an option to the upgrade command.

flask db upgrade --commit-versions

[miguelgrinberg]

You should post this on the Alembic bug tracker, Flask-Migrate is just a wrapper that has no way to alter how migrations are created or executed.

[quantoid]

Sorry, I thought the migrations/env.py module was a flask-migrate thing, not provided out of the box by Alembic but created specifically for the integration with Flask.

My understanding is that you'd have to change that module to get per-version transactions. It currently runs them like this, creating a single transaction across all versions:

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

[quantoid]

The Alembic documentation on Runtime Objects mentions a transaction_per_migration option, but I couldn't work out how to pass that through the flask-migrate wrapper.

[miguelgrinberg]

The env.py that ships with Flask-Migrate is the default env.py from Alembic with some minor changes that help the Flask integration. It is totally fine for you to make changes to it.

You can add the transaction_per_migration=True argument in env.py, right in this place.

[quantoid]

Excellent, thanks I will give that a try.

Looks like I could also include the option in current_app.extensions['migrate'].configure_args but where is that set up?

[quantoid]

Also, the spirit of this issue is to make it easier for people to work this out, so even though you've explained how to do it, I think the package needs to have this information in the docs or comments, or better still as an option on the db upgrade command.

[quantoid]

Digging through the code I reckon the option can be included here:

migrate = Migrate(app, db, transaction_per_migration=True)

Maybe just add a docstring to Migrate.__init__ explaining that the kwargs are MigrationContext options?

[FJAbellan]

I have added the transaction_per_migration=True argument in env.py, but I have one transaction per sql operation:

INFO [sqlalchemy.engine.base.Engine] COMMIT
INFO [sqlalchemy.engine.base.Engine] ALTER TABLE client ADD COLUMN name VARCHAR(80)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] COMMIT
INFO [sqlalchemy.engine.base.Engine] ALTER TABLE client MODIFY company VARCHAR(16) NULL
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] COMMIT
INFO [sqlalchemy.engine.base.Engine] ALTER TABLE client MODIFY external_id INTEGER(11) NULL
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] COMMIT

Why would this be, any idea?

[miguelgrinberg]

@FJAbellan I honestly don't know. You are asking the wrong person, this is the issue board for Flask-Migrate, which is just a Flask friendly wrapper for Alembic. It seems you are having an issue with Alembic.

[miguelgrinberg]

This issue will be automatically closed due to being inactive for more than six months. Please reopen if you need more assistance.