Alembic --autogenerate tries to recreate every table

Question

I am trying to autogenerate an alembic revision for the first time against a pre-existing database but when I run the following command

alembic revision --autogenerate

It generates a migration which attempts to create every table and index in my database. Similar to this:

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('table1',
    sa.Column('id', sa.SmallInteger(), nullable=False),
    sa.Column('name', sa.String(length=100), nullable=True),
    sa.Column('desc', sa.Text(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('name'),
    schema='schema1'
    )
    op.create_index(op.f('ix_index1'), 'table1', ['name'], unique=False, schema='schema1')
    ... all my other tables/indexes ..


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_index1'), table_name='table1', schema='schema1')
    op.drop_table('table1', schema='schema1')
    ... all my other tables/indexes ..

Then if I try and run the migration it fails because the objects already exist:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) relation "table1" already exists

So it looks to me like alembic thinks that my database doesn't contain any tables, but it does.

Any ideas why this might be happening?

Answer 1

Configure alembic to look at your database

Have you set the target_metadata to your Base meta data?

From the documentation.

To use autogenerate, we first need to modify our env.py so that it gets access to a table metadata object that contains the target. Suppose our application has a declarative base in myapp.mymodel. This base contains a MetaData object which contains Table objects defining our database. We make sure this is loaded in env.py and then passed to EnvironmentContext.configure() via the target_metadata argument. The env.py sample script used in the generic template already has a variable declaration near the top for our convenience, where we replace None with our MetaData. Starting with:

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata 
target_metadata = None

we change to:

from myapp.mymodel import Base
target_metadata = Base.metadata