Alembic - sqlalchemy does not detect existing tables

Question

I've asked a question (Alembic - sqlalchemy initial migration) on how to detect tables by using

target_metadata = Base.metadata

for

alembic revision --autogenerate -m "initial migration"

After I've imported my models to env.py file it seemed to work fine but it does not detect actually existing tables so it creates a migration file with all tables, for example:

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('Brand',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=True),
    sa.Column('slug', sa.String(), nullable=True),
    sa.Column('date_created', sa.DateTime(), nullable=True),
    sa.Column('date_updated', sa.DateTime(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    schema='Products'
    )

def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('ProductFile', schema='Products')

I've tried:

alembic stamp head

but after running that and running autogenerate command the system generates all models once again.

from project.apps.users.models import *
from project.apps.orders.models import *
from project.apps.products.models import *

from project.base import Base, metadata

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config

# Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)

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

How do I avoid that problem?

Edit:

ENV.py:

https://gist.github.com/pypetey/bb65807ce773d8baeaf1

I dropped the db and ran a migration

(env) D:\projekty\test>alembic revision --autogenerate
INFO  [alembic.migration] Context impl MSSQLImpl.
INFO  [alembic.migration] Will assume transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table u'Users.Country'
INFO  [alembic.autogenerate.compare] Detected added table u'Products.Brand'
INFO  [alembic.autogenerate.compare] Detected added table u'Users.User'
INFO  [alembic.autogenerate.compare] Detected added table u'Products.Product'
INFO  [alembic.autogenerate.compare] Detected added table u'Products.ProductFile
'
INFO  [alembic.autogenerate.compare] Detected added table u'Orders.Order'
INFO  [alembic.autogenerate.compare] Detected added table u'Products.Category'
INFO  [alembic.autogenerate.compare] Detected added table u'Products.Review'
INFO  [alembic.autogenerate.compare] Detected added table u'Users.UserAddress'
INFO  [alembic.autogenerate.compare] Detected added table u'Orders.OrderItem'
INFO  [alembic.autogenerate.compare] Detected added table u'Orders.OrderStatus'
Generating D:\projekty\test\alembic\versions\1c6337c144a7_.py ... done

(env) D:\projekty\test>alembic upgrade head
INFO  [alembic.migration] Context impl MSSQLImpl.
INFO  [alembic.migration] Will assume transactional DDL.
INFO  [alembic.migration] Running upgrade None -> 1c6337c144a7, empty message

(env) D:\projekty\test>alembic revision --autogenerate
INFO  [alembic.migration] Context impl MSSQLImpl.
INFO  [alembic.migration] Will assume transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table u'Users.Country'
INFO  [alembic.autogenerate.compare] Detected added table u'Products.Brand'
INFO  [alembic.autogenerate.compare] Detected added table u'Users.User'
INFO  [alembic.autogenerate.compare] Detected added table u'Products.Product'
INFO  [alembic.autogenerate.compare] Detected added table u'Products.ProductFile
'
INFO  [alembic.autogenerate.compare] Detected added table u'Orders.Order'
INFO  [alembic.autogenerate.compare] Detected added table u'Products.Category'
INFO  [alembic.autogenerate.compare] Detected added table u'Products.Review'
INFO  [alembic.autogenerate.compare] Detected added table u'Users.UserAddress'
INFO  [alembic.autogenerate.compare] Detected added table u'Orders.OrderItem'
INFO  [alembic.autogenerate.compare] Detected added table u'Orders.OrderStatus'
Generating D:\projekty\test\alembic\versions\5abb204549f_.py ... done

Have you tried running the migration on an empty database first? Say by running `alembic revision --autogenerate ...`, editing the migration file if necessary, running `alembic upgrade head`, and running `alembic revision --autogenerate ...` again to confirm an empty migration file is generated? — dgilland, Oct 09 '14 at 14:56
@dgilland I've crecreated the db, ran the migration and ran the autogenerate again. It did not help. Check my updated post. — Efrin, Oct 10 '14 at 10:38
After you run the `alembic upgrade head`, what does `alembic current` return? Also, after running the upgrade, can you inspect the database and confirm whether the tables were created, whether the `alembic_version` table exists, and what the contents of `alembic_version` are? From what you describe, it seems like none of the tables are being created. Do you have permission to create tables on the database? — dgilland, Oct 10 '14 at 14:36
They were created from scratch. But even after another autogenerate it did try to create them again. I do have all permissions. Alembic current returned that migration was applied (I don't have exact console logs here) and it really was but still tried to create tables again — Efrin, Oct 10 '14 at 15:41
Very strange. Can you run the migration process for a SQLite database to see how that goes? — dgilland, Oct 10 '14 at 19:08

score 48 · Accepted Answer · edited Mar 28 '23 at 04:11

48

I had this exact same issue - I don't know if it still affects you. For me, the problem was caused because the schema I was using was not the default - I think the same thing is happening for you, since you're using a "Products" schema. I posted an issue at:

https://bitbucket.org/zzzeek/alembic/issue/281/autogenerate-fails-to-detect-existing

And with a little bit of guidance, managed to resolve the problem by passing a parameter include_schemas=True to the EnvironmentContext.configure call in both run_migrations_* functions in the alembic/env.py module.

See the docs:

If True, autogenerate will scan across all schemas located by the SQLAlchemy get_schema_names() method, and include all differences in tables found across all those schemas. When using this option, you may want to also use the EnvironmentContext.configure.include_object option to specify a callable which can filter the tables/schemas that get included.

edited Mar 28 '23 at 04:11

David Scarlett

3,171
2
12
28

answered Mar 13 '15 at 20:23

LordSputnik

655
7
6

This is an aside, but related: ability to control what db tables are considered "existing": http://alembic.zzzcomputing.com/en/latest/api/runtime.html#alembic.runtime.environment.EnvironmentContext.configure.params.include_object and some older discussion about this: https://groups.google.com/forum/#!topic/sqlalchemy-alembic/2HJ9J6PiQsk – TaiwanGrapefruitTea Dec 17 '16 at 16:04
This option does what I want for the specified schema, but it also checks for differences in all other schemas. Is there a way that I can have it be aware of the specified schema but ignore all others? – RogerKint Jan 19 '23 at 16:34

score 5 · Answer 2 · answered Jan 28 '22 at 07:44

I have an observation.
Metadata imported to the env.py in alembic, must have all model metadata. So all models must be loaded before calling Base. So,
db_setup.py will have,

...
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
...

and models.py will have,

from .db_setup import Base
from sqlalchemy import Column, String
class TestModel(Base):
    __tablename__ = "test"
    field_1 = Column(String)
    ...

Now, in the main.py,

from flask import Flask
...
from models.setup import Base
app = Flask(__name__)
...

Finally, in the alembic env.py,

...
...
from main import Base # Not from db_setup!
target_metadata = Base.metadata
...
...

This worked for me, by far the easiest answer to implement. TLDR: Import your base.metadata from wherever your tables are initilized. — Lafftar, May 06 '22 at 20:45
Oh, thank you very much, I've been struggling with this stuff for a while already, and this is exactly the solution I was looking for! — AlexShein, Aug 17 '22 at 07:18

score 0 · Answer 3 · answered Apr 01 '22 at 11:02

0

In case this helps anybody else; if you are using the fastapi template, make sure the new model is added to db/base.py file.

answered Apr 01 '22 at 11:02

omufeed

144
5

1

You should specify which template because there are many FastAPI templates – devaerial Feb 08 '23 at 10:17

Alembic - sqlalchemy does not detect existing tables

3 Answers3

Linked