Accessing models in alembic migrations

Question

I'm using alembic migrations for a flask+sqlalchemy project and things work as expected till I try to query the models in alembic.

from models import StoredFile

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.add_column('stored_file', sa.Column('mimetype', sa.Unicode(length=32))
    for sf in StoredFile.query.all():
        sf.mimetype = guess_type(sf.title)

The above code gets stuck after adding column and never comes out. I guess the StoredFile.query is trying to use a different database connection than the one being used by alembic. (But why? Am I missing something in env.py?)

I could solve it by using the op.get_bind().execute(...) but the question is how can I use the models directly in alembic?

Answer 1

You should not use classes from models in your alembic migrations. If you need to use model classes, you should redefine them in each migration file to make the migration self-contained. The reason is that multiple migrations can be deployed in one command, and it's possible that between the time the migration was written and until it is actually performed in production, the model classes have been changed in accordance with a "later" migration.

For example, see this example from the documentation for Operations.execute:

from sqlalchemy.sql import table, column
from sqlalchemy import String
from alembic import op

account = table('account',
    column('name', String)
)
op.execute(
    account.update(). \
        where(account.c.name==op.inline_literal('account 1')). \
        values({'name':op.inline_literal('account 2')})
        )

Tip: You don't need to include the full model class, only the parts that are necessary for the migration.

Answer 2

I had the same problem. When you use StoredFile.query you are using a different session than Alembic is using. It tries to query the database but the table is locked because you're altering it. So the upgrade just sits there and waits forever because you have two sessions waiting for each other. Based on @SowingSadness response, this worked for me:

from models import StoredFile

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.add_column('stored_file', sa.Column('mimetype', sa.Unicode(length=32))

    connection = op.get_bind()
    SessionMaker = sessionmaker(bind=connection.engine)
    session = SessionMaker(bind=connection)
    for sf in session.query(StoredFile):
        sf.mimetype = guess_type(sf.title)
    session.flush()
    op.other_operations()