No changes detected in Alembic autogeneration of migrations with Flask-SQLAlchemy

Question

I'm having trouble getting Alembic to autogenerate candidate migrations from changes to classes using db.Model (Flask-SQLAlchemy) instead of Base.

I've modified env.py to create my Flask app, import all relevant models, initialize the database, and then run migrations:

...
uri = 'mysql://user:password@host/dbname?charset=utf8'
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = uri
app.config['SQLALCHEMY_ECHO'] = True
db.init_app(app)
with app.test_request_context():
    target_metadata = db.Model.metadata
    config.set_main_option('sqlalchemy.url', uri)
    if context.is_offline_mode():
        run_migrations_offline()
    else:
        run_migrations_online()
...

This approach works fine for drop_all(), create_all() (for example, when recreating a test db for unit testing), but it seems to fall flat in this case. The auto generated version scripts always have empty upgrade and downgrade methods, e.g.,

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    pass
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    pass
    ### end Alembic commands ###

My changes have included renaming columns, changing column definitions, etc., not just changes to indices and foreign keys.

Is anyone out there using Alembic with Flask-SQLAlchemy? Any idea where I'm going wrong?

Thanks much!

score 68 · Accepted Answer · edited Mar 01 '19 at 15:28

Alembic cannot automatically detect table or column renames. By default it will not look for column type changes either, but the compare_type option can be enabled for this.

Excerpt from the Alembic documentation:

Autogenerate will by default detect:

Table additions, removals.
Column additions, removals.
Change of nullable status on columns.

Autogenerate can optionally detect:

Change of column type. This will occur if you set compare_type=True on EnvironmentContext.configure(). The feature works well in most cases, but is off by default so that it can be tested on the target schema first. It can also be customized by passing a callable here; see the function’s documentation for details.
Change of server default. This will occur if you set compare_server_default=True on EnvironmentContext.configure(). This feature works well for simple cases but cannot always produce accurate results. The Postgresql backend will actually invoke the “detected” and “metadata” values against the database to determine equivalence. The feature is off by default so that it can be tested on the target schema first. Like type comparison, it can also be customized by passing a callable; see the function’s documentation for details.

Autogenerate can not detect:

Changes of table name. These will come out as an add/drop of two different tables, and should be hand-edited into a name change instead.
Changes of column name. Like table name changes, these are detected as a column add/drop pair, which is not at all the same as a name change.
Special SQLAlchemy types such as Enum when generated on a backend which doesn’t support ENUM directly - this because the representation of such a type in the non-supporting database, i.e. a CHAR+CHECK constraint, could be any kind of CHAR+CHECK. For SQLAlchemy to determine that this is actually an ENUM would only be a guess, something that’s generally a bad idea. To implement your own “guessing” function here, use the sqlalchemy.events.DDLEvents.column_reflect() event to alter the SQLAlchemy type passed for certain columns and possibly sqlalchemy.events.DDLEvents.after_parent_attach() to intercept unwanted CHECK constraints.

Autogenerate can’t currently, but will eventually detect:

Free-standing constraint additions, removals, like CHECK, UNIQUE, FOREIGN KEY - these aren’t yet implemented. Right now you’ll get constraints within new tables, PK and FK constraints for the “downgrade” to a previously existing table, and the CHECK constraints generated with a SQLAlchemy “schema” types Boolean, Enum.
Index additions, removals - not yet implemented.
Sequence additions, removals - not yet implemented.

UPDATE: some of the items in this last list are supported in the Alembic 0.7.x releases.

Thanks for the info, but if you could check out my question here (http://stackoverflow.com/questions/17201800/alembic-autogenerates-empty-flask-sqlalchemy-migrations), you'll see that Alembic is not detecting entire tables. This isn't one of the exceptions described above. — element119, Jun 22 '13 at 00:35
If you read this OP's question he's saying that the things that Alembic isn't detecting are (quote) "renaming columns, changing column definitions, etc.". At least from his description he isn't having this problem for entire tables like you. — Miguel Grinberg, Jun 22 '13 at 00:53
Ah, looks like you're right! Should have read the question more carefully. Anyhow, thanks for the answer. — element119, Jun 22 '13 at 02:19
Alembic can now detect FK changes (since version 0.71). See https://bitbucket.org/zzzeek/alembic/issue/178/implement-autogenerate-detection-for — rkrzr, Dec 22 '14 at 15:22
This answer really help me when working with alembic, thanks @Miguel :) — Tri, Sep 02 '19 at 07:19

score 6 · Answer 2 · answered Aug 06 '19 at 14:20

I also faced this issue and using this way to solve that:

open the migrations/env.py file, and on def run_migrations_online() function look at the context.configure, on Alembic 1.0.8 it should look like this:

with connectable.connect() as connection:
    context.configure(
        connection=connection,
        target_metadata=target_metadata,
        process_revision_directives=process_revision_directives,
        **current_app.extensions['migrate'].configure_args,
    )

Just remove or comment theprocess_revision_directives=process_revision_directives and then add the compare_type=True on that.

Like this:

with connectable.connect() as connection:
    context.configure(
        connection=connection,
        target_metadata=target_metadata,
        # process_revision_directives=process_revision_directives,
        **current_app.extensions['migrate'].configure_args,
        compare_type=True
    )

but could you explain what is the purpose of such changes – andilabs Oct 15 '19 at 17:17 — andilabs, Oct 15 '19 at 17:17

score 5 · Answer 3 · answered Aug 08 '16 at 13:34

5

My mistake was to try creating my initial migration with the db already in the final state, thinking it would notice it had no existing versions and base it on the models. I got empty versions until I deleted all tables in the db and then it worked fine.

answered Aug 08 '16 at 13:34

Brad M

405
6
17

1

Thank you Brad, that actually made a lot of sense and worked fine in my case. (after spending a long time trying to understand why Alembic isn't detecting metadata) – cnikolaou Jan 15 '18 at 10:38
1

Does this mean that we need to run migrations on a pre migrated db in order for it to detect changes? – Callam Delaney Oct 02 '19 at 11:08
2

@cal97g close: need to _generate_ the migrations on a pre-migrated db – Brad M Oct 13 '19 at 03:30

score 2 · Answer 4 · answered Sep 29 '20 at 02:40

This may be a silly suggestion, but I was having similar issues. All my env files were pointed to the right places and such, yet I still couldn't generate this new table. I was using the command sequence "alembic revision -m 'comment'" and then "alembic upgrade head" and I got 'empty' revision files.

Finally after deleting all the migration files, destroying the docker images, reinstating the migration files, re-attempting the above sequence of cli commands and just before manually hand coding the migration, I tried "alembic revision --autogenerate" and it found and created the migrations for the table.

This may have been due to all the other steps I previously took. My apologies if it isn't very helpful.

That fixed my bug! I forgot to include the "autogenerate" in the command. — yzernik, Dec 28 '20 at 06:22

score 0 · Answer 5 · answered Aug 05 '22 at 17:10

Ran into a similar problem, my solution was to change the env.py file and insert

include_schemas=True

in both of the run_migrations_* functions.

It may still result in unwanted changes (especially with postgis db and it's tables), but that can be manually removed!

score 0 · Answer 6 · answered Dec 12 '22 at 17:49

By default, Alembic doesn't recognize the change on the existing column attribute changes.

You should add compare_type=True when you create the Migrate class

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

After doing the above changes, try to regenerate your migration file, and it should have the code as per your changes.

score 0 · Answer 7 · answered Jul 03 '23 at 09:04

Edit the alembic/env.py file and add 'compare_type=True' at two places:

def run_migrations_offline():
    ...
    context.configure(
        url=url, target_metadata=target_metadata, literal_binds=True,
        compare_type=True # <--- here
    )
    ...


def run_migrations_online():
    ...
    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            compare_type=True # <--- here
        )
    ...

score -1 · Answer 8 · answered Sep 19 '12 at 09:03

Try flask-alembic https://github.com/tobiasandtobias/flask-alembic

I tried it yesterday. It works fine for me except drop operations. They doesn't work on sqlite (https://bitbucket.org/zzzeek/alembic/issue/21/column-renames-not-supported-on-sqlite).

The way I've used it. First I used python manage.py migrate revision --autogenerate to create empty tables in sqlite db. It'll produce such migration

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('users_user',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=50), nullable=True),
    sa.Column('email', sa.String(length=120), nullable=True),
    sa.Column('password', sa.String(length=20), nullable=True),
    sa.Column('role', sa.SmallInteger(), nullable=True),
    sa.Column('status', sa.SmallInteger(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('email'),
    sa.UniqueConstraint('name')
)
### end Alembic commands ###

def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('users_user')
    ### end Alembic commands ###

Then - python manage.py migrate upgrade head

Then I added a new column test = db.Column(db.String(20)) to User model and ran this command python manage.py migrate revision --autogenerate -m 'test field at users'

This produced such migration:

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.add_column('users_user', sa.Column('test', sa.String(length=20), nullable=True))
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('users_user', 'test')
    ### end Alembic commands ###

No changes detected in Alembic autogeneration of migrations with Flask-SQLAlchemy

8 Answers8

Linked