Obtain enriched MetaData from Sqlalchemy Model

Question

I'm trying to create a package that manages DB connection, ORM models and migration. It's separated from web service project such as a Flask application, so flask-sqlalchemy is not considered.

This is how I organize my project (only list out parts related to this question):

alembic.ini
src/
 * my_project/
    * db/
       - connections.py
    * models/
       * abstract/
          - abstract_base.py
       * realized/
          - realized_model.py
migrations/
 * versions/
 - env.py
 - script.py.mako

src/my_project/db/connections.py:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

ENGINE = create_engine("db://url")
ModelBase = declarative_base(bind=ENGINE)
Session = sessionmaker(ENGINE)

src/my_project/models/abstract/abstract_base.py:

from sqlalchemy import Column, Integer, DateTime
from my_project.db.connections import ModelBase

class BaseWithTimestamp(ModelBase):
    __abstract__ = True
    
    id = Column(Integer, primary_key=True, nullable=False)
    created_at = Column(DateTime, nullable=False)

src/my_project/models/realized/realized_model.py:

from sqlalchemy import Column, String
from my_project.models.abstract.abstract_base import BaseWithTimestamp

class Note(BaseWithTimestamp):
    __tablename__ = "notes"

    content = Column(String(300), nullable=True)

env.py (the same as alembic default except metadata setup):

# ...
from my_project.db.connections import ModelBase

# ...
target_metadata = ModelBase.metadata

# ...

Supposedly, when linked to an empty database, alembic should generate migration script that creates table notes with three columns specified in model Note when running revision command with auto-generation switched on. However, what I got is an empty migration script.

Hence I tried doing this in interactive shell to see what's stored in Base's metadata:

from my_project.db.connections import ModelBase

ModelBase.metadata.tables # => FacadeDict({})

Note/notes is expected to appear in metadata's table list, but above result shows that no table was memorized in Base's metatdata, which I think is the root cause for generating empty migration script. Is there anything I'm missing or doing wrong?

Answer 1

Seems that one needs to declare all related classes explicitly right after declarative base model is declared/imported, so that these extended models can get added to metadata:

migrations/env.py

# ...
from my_project.db.connections import ModelBase
from my_project.models.abstract.abstract_base import BaseWithTimestamp
from my_project.models.realized.realized_model import Note

# ...
target_metadata = ModelBase.metadata

# ...