Migrate detects changes in sqlite but not in Postgres

[kjohnsen]

I'm not sure what's going on. I've looked at issues #4, #50, and #94 to try and figure out why flask db migrate wasn't working, and I made sure the database was empty and that models were imported. I even inserted a Test model class right above where migrate is defined, and flask db migrate simply wouldn't detect any changes no matter what schema or database change I did.

However, I can confirm that Flask-SQLAlchemy, at least, is detecting models just fine, since using flask shell I can drop_all() and create_all() and confirm that the database is changed as expected.

For some reason using a sqlite database instead of Postgres works (i.e., a script is generated).

Another thing: is there always supposed to be a No changes in schema detected message when there are no changes? I haven't delved into the guts of Alembic to see exactly where it's failing, but I found that context.run_migrations() on line 80 of the migrations/env.py file fails. I wonder whether it's supposed to simply fail, or generate an empty script and the No changes message.

Here's a minimum working example:

from flask import Flask
from flask_migrate import Migrate
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = \
        'postgresql+psycopg2://postgres:dbpw@0.0.0.0:5432/test'
### this works fine:
### app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///migrate-test.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db = SQLAlchemy(app)

class Test(db.Model):
    id = db.Column(db.String, primary_key=True)

migrate = Migrate(app, db)

When I run flask db migrate, I expect adding the table test to be detected, since the DB is empty, but instead I get the following:

user@COMPUTER:~/migrate-test$ flask db migrate
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.

To reiterate, the No changes in schema detected message doesn't show up--it simply exits without a return code of 0.

[miguelgrinberg]

Are you using a schema other than the default in your Postgres database? Alembic does not look on custom schemas by default.

[kjohnsen]

I understand that "public" is the default schema, which is what I'm using.

[kjohnsen]

Update: I started using a normal, (not in-memory) sqlite database, and it worked just fine, leaving just the Postgres problem. I edited the example above to reflect this.

[miguelgrinberg]

Are you using a 0.0.0.0 IP address in your URL? Try changing that to the valid IP address of your postgres host. If you have it on your own machine, then use 127.0.0.1.

[kjohnsen]

I tried 127.0.0.1 and nothing changed. Flask-SQLAlchemy can connect just fine (i.e., create_all() and drop_all() work), but migrate doesn't do anything.

[miguelgrinberg]

Not sure what can be the problem. Using your example script above I do get a migration generated.

(venv) ~ $ flask db migrate
/home/miguel/venv/lib/python3.6/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.
  """)
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'test'
  Generating /home/miguel/migrations/versions/a564ca8c96f0_.py ... done

[kjohnsen]

Hmm...I'm going to try and run this again using a different environment and a fresh database and see if that works.

[kjohnsen]

I found what was causing it to break: the alembic_version table was out of sync with the migrations/versions directory. When I tried with a new database, it worked fine until I deleted migration scripts, which brought me back to the same problem. Then when I deleted the alembic_version table, it started working fine again.

I assume this is an issue with Alembic, so I'll report it there, because it would be nice if an error message popped up instead of the database inexplicably becoming unmigratable.

[kjohnsen]

Thank you for your help!

[kjohnsen]

I tried Alembic by itself (so no Flask-SQLAlchemy or Flask-Migrate) and I got the following. Do you know why this wouldn't pop up when using Flask-Migrate?

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
ERROR [alembic.util.messaging] Can't locate revision identified by '334bbe02f85d'
FAILED: Can't locate revision identified by '334bbe02f85d' 

[miguelgrinberg]

Yes, because Flask-Migrate configures alembic for you. Some defaults are different in Alembic, for example, it expects the alembic.ini file in the current directory.

[kjohnsen]

I found why: the default env.py in Flask-Migrate puts the run_migrations() function in a try block, so you don't get the error message. It isn't in a try block in Alembic's default env.py.

[kjohnsen]

If anyone else runs into this problem, I'd suggest adding the except block to the try/finally statement on line 78 of env.py. This at least allows the user to see what's going wrong.

    try:
        with context.begin_transaction():
            context.run_migrations()
    except Exception as exception:
        logger.error(exception)
        raise exception
    finally:
        connection.close()

Now when I run flask db migrate without the alembic_version table in my DB, for example, I get this:

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
ERROR [alembic.env] Target database is not up to date.    

@miguelgrinberg, is there any reason not to update the env.py template with something like this to save people like me time in the future?

[miguelgrinberg]

@kjohnsen that is a good change. I took the env.py file from the Alembic project long time ago and this is what that project did then, I haven't looked at how things look over there these days, your change looks good to me, but I should also review the current env.py from Alembic and bring any other updates as well.

[kjohnsen]

Awesome. Thanks again for the help!

[ravshanof]

DROP TABLE alembic_version; helped me to fix the issue.

[miguelgrinberg]

I updated the env.py files in this repo from the latest Alembic versions. Released as v2.4.0. Closing.