Alembic with databses autogenerate empty migration file

[Lepiloff]

I'm trying to connect the alembic library to the databases and sqlalchemy libraries. As a guide, I use this example link

My projects file:

db.py

from databases import Database
from sqlalchemy import MetaData, create_engine

DATABASE_URL = "postgresql://....@localhost:5432/db"

engine = create_engine(DATABASE_URL)
metadata = MetaData()

database = Database(DATABASE_URL)

models.py

from sqlalchemy import Table, Column, Integer, String, DateTime
from sqlalchemy.sql import func

from db import metadata

notes = Table(
    "notes",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("title", String(50)),
    Column("description", String(50)),
    Column("created_date", DateTime, default=func.now(), nullable=False),
)

env.py (alembic settings)

from db import DATABASE_URL, metadata

....
#add new
target_metadata = metadata
...
#change
def run_migrations_online():

    config.set_main_option('sqlalchemy.url', str(DATABASE_URL))
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

When I run

alembic revision --autogenerate -m 'Add notest table'

the new file at migrations/versions this context is created

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###

I suppose it may be related to the use of the target_metadata = metadata variable. It seems to be all according to instructions, but the migrations do not work as expected.

[zzzeek]

hey there-

you need to also import models.py or otherwise make sure those Table() instructions are run, before env.py starts running the autogen.

[Lepiloff]

@zzzeek thank you for reply. Where exactly do I need to import data from models.py?

[zzzeek]

you could put it right under your "import db" in env.py

[Lepiloff]

It works like magic! I don't understand why it works, but it work. Thank you for your help, I've been sitting on it all day.

[YajJackson]

Ran into the same issue and found this thread. I'll share roughly what I ended up with in case it might be helpful for someone else.

app.models.model.py

from sqlalchemy.orm import registry

mapper_registry = registry()
Base = mapper_registry.generate_base()

app.models.user.py

from sqlalchemy.sql.schema import Column
from sqlalchemy.types import Integer, String
from app.models.model import Base

class UserModel(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String(30))

❌ env.py that generated empty migration

from app.models.model import Base

target_metadata = Base.metadata

✅ env.py that successfully detected my user model and generated an initial migration

from skatebase.models.model import Base
from skatebase.models.user import UserModel # Added this line, magic 🧙 
# Presumably, new models will need to be added to this file as needed for autogenerating schema

target_metadata = Base.metadata

[CaselIT]

That is just how the python import system works and there is nothing alembic can do to change that.

I suggest having a module where you import all your models, so that they are correctly loaded when using alembic

[emmanuelviniciusdev]

Importing all my models inside env.py solved the problem for me. Thank you guys.

[gsalinas0]

Hello there. A little advice here: Be carefull when running a linter that might deleted unused variables/import. The one I used deleted the import of the models and broke the env.py when I tried to update a table and then running a migration

[Jarmos-san]

We stumbled across this issue because of how our projects are structured. Here is an example of what we work with:

.
└── app/
    ├── migrations/
    │   ├── env.py
    │   ├── versions/
    │   │   └── ...
    │   └── ...
    ├── src/
    │   ├── __init__.py
    │   ├── dbschema/
    │   │   ├── __init__.py
    │   │   ├── base.py
    │   │   ├── customer.py
    │   │   ├── product.py
    │   │   └── ...
    │   └── ...
    ├── tests/
    │   ├── __init__.py
    │   └── ...
    ├── alembic.ini
    ├── pyproject.toml
    └── poetry.lock

We followed the recommendations of the Alembic documentations to autogenerate the migration scripts but it was creating empty content as mentioned in this thread.

As mentioned earlier on in the thread, importing the models to the env.py like the following did yield expected results;

# ....truncated code....

from src.dbschema.base import Base
from src.dbschema.product import ProductTable
from src.dbschema.customer import CustomerTable
# ...more such classes imported to the namespace...

# ....truncated code....

target_metadata = Base.metadata

The caveat with the approach is, our automated CI environments would sound the alarm (and even potentially remove) the unused import statements! This has been mentioned in the previous comment as well.

Instead a approach which worked for us is to add the following lines to the dbschema/__init__.py file;

from src.dbschema.product import ProductTable
from src.dbschema.customer import CustomerTable

 # ...add the rest of the SQLAlchemy models to the list as well
__all__ = ["ProductTable", "CustomerTable"]

Don't forget to remove the import statements from the migrations/env.py file after adding the aforementioned lines btw!

There could be a better approach to the solution here but it works because the dbschema/__init__.py file gets initialised when the import statement to load the Base class in the env.py file. During initialisation, the __all__ Object "exports" the specified models in the desired namespace and hence is available to alembic when it is needed.

For the uninitiated, a good article to learn more about this behaviour is this blog - Python's __all__: Packages, Modules, and Wildcard Imports.

With this approach, any addition of new models to the __all__ object, the autogen will correctly identify all the necessary changes to produce in the migration scripts. As is recommended in the official documentations, do verify the generated contents of the scripts for any inconsistencies before running them.