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?

Could it be you haven't created an initial migration? [http://alembic.zzzcomputing.com/en/latest/tutorial.html#running-our-first-migration](http://alembic.zzzcomputing.com/en/latest/tutorial.html#running-our-first-migration) Judging by your question I'm assuming your database is already created. You need to tell alembic the current state of the DB so it knows what to auto generate. — JJK, Sep 09 '16 at 21:43
Duplicate of https://stackoverflow.com/questions/26275041/alembic-sqlalchemy-does-not-detect-existing-tables. When working with the non-standard schema ('schema1' instead of 'public'), you need to configure your context accordingly ('include_schmas=True'). — Peter Barmettler, Oct 13 '21 at 09:21

score 2 · Accepted Answer · answered Dec 13 '16 at 13:14

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

I did that but still. For example, I ran the autogeneret for a entity and it has created the table for that entity. After a while I added a new entity and run the with 'autogenerate' again. Was created a new migration file with the statements to create the new and also the existing one — Rodrigo Estevao, Aug 24 '22 at 07:56
@RodrigoEstevao, this answer is six years old, I haven't used alembic for years. Have you done `alembic upgrade`? Can you check your revision number? — Graeme Stuart, Aug 25 '22 at 10:26

Alembic --autogenerate tries to recreate every table

1 Answers1

Configure alembic to look at your database