[BUG] SQLAlchemy InvalidRequestError on relationships if one of the model is not yet imported

[ebreton]

Describe the bug

The application crashes at start-up, when initializing data if :

1. a relationship is defined ...
2. ... with a model not already imported at the time of execution.

backend_1        | INFO:__main__:Starting call to '__main__.init', this is the 2nd time calling it.
backend_1        | INFO:__main__:Service finished initializing
backend_1        | INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
backend_1        | INFO  [alembic.runtime.migration] Will assume transactional DDL.
backend_1        | INFO  [alembic.runtime.migration] Running upgrade  -> d4867f3a4c0a, First revision
backend_1        | INFO  [alembic.runtime.migration] Running upgrade d4867f3a4c0a -> ea9cad5d9292, Added SubItem models
backend_1        | INFO:__main__:Creating initial data
backend_1        | Traceback (most recent call last):
backend_1        |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/ext/declarative/clsregistry.py", line 294, in __call__
backend_1        |     x = eval(self.arg, globals(), self._dict)
backend_1        |   File "<string>", line 1, in <module>
backend_1        | NameError: name 'SubItem' is not defined
...
backend_1        | sqlalchemy.exc.InvalidRequestError: When initializing mapper mapped class Item->item, expression 'SubItem' failed to locate a name ("name 'SubItem' is not defined"). If this is a class name, consider adding this relationship() to the <class 'app.db_models.item.Item'> class after both dependent classes have been defined.
base-project_backend_1 exited with code 1

To Reproduce

create a new db_models/subitems.py (could be copied from item.py)

It is important that this new model has a relationship to another one, e.g Item

from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.orm import relationship

from app.db.base_class import Base


class SubItem(Base):
    id = Column(Integer, primary_key=True, index=True)
    title = Column(String, index=True)
    description = Column(String, index=True)
    item_id = Column(Integer, ForeignKey("item.id"))
    item = relationship("Item", back_populates="subitems")

Adapt db_models/item.py with the new relationship

...

class Item(Base):
    ...
    subitems = relationship("SubItem", back_populates="item")

Declare the new SubItem in db/base.py as per the documentation

# Import all the models, so that Base has them before being
# imported by Alembic
from app.db.base_class import Base  # noqa
from app.db_models.user import User  # noqa
from app.db_models.item import Item  # noqa
from app.db_models.subitem import SubItem  # noqa

Re-build and start the application. The full traceback follows

backend_1        | INFO:__main__:Creating initial data
backend_1        | Traceback (most recent call last):
backend_1        |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/ext/declarative/clsregistry.py", line 294, in __call__
backend_1        |     x = eval(self.arg, globals(), self._dict)
backend_1        |   File "<string>", line 1, in <module>
backend_1        | NameError: name 'SubItem' is not defined
backend_1        |
backend_1        | During handling of the above exception, another exception occurred:
backend_1        |
backend_1        | Traceback (most recent call last):
backend_1        |   File "/app/app/initial_data.py", line 21, in <module>
backend_1        |     main()
backend_1        |   File "/app/app/initial_data.py", line 16, in main
backend_1        |     init()
backend_1        |   File "/app/app/initial_data.py", line 11, in init
backend_1        |     init_db(db_session)
backend_1        |   File "/app/app/db/init_db.py", line 12, in init_db
backend_1        |     user = crud.user.get_by_email(db_session, email=config.FIRST_SUPERUSER)
backend_1        |   File "/app/app/crud/user.py", line 16, in get_by_email
backend_1        |     return db_session.query(User).filter(User.email == email).first()
backend_1        |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/scoping.py", line 162, in do
backend_1        |     return getattr(self.registry(), name)(*args, **kwargs)
backend_1        |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1543, in query
backend_1        |     return self._query_cls(entities, self, **kwargs)
backend_1        |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 168, in __init__
backend_1        |     self._set_entities(entities)
backend_1        |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 200, in _set_entities
backend_1        |     self._set_entity_selectables(self._entities)
backend_1        |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 231, in _set_entity_selectables
backend_1        |     ent.setup_entity(*d[entity])
backend_1        |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 4077, in setup_entity
backend_1        |     self._with_polymorphic = ext_info.with_polymorphic_mappers
backend_1        |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 855, in __get__
backend_1        |     obj.__dict__[self.__name__] = result = self.fget(obj)
backend_1        |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/mapper.py", line 2135, in _with_polymorphic_mappers
backend_1        |     configure_mappers()
backend_1        |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/mapper.py", line 3229, in configure_mappers
backend_1        |     mapper._post_configure_properties()
backend_1        |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/mapper.py", line 1947, in _post_configure_properties
backend_1        |     prop.init()
backend_1        |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/interfaces.py", line 196, in init
backend_1        |     self.do_init()
backend_1        |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/relationships.py", line 1860, in do_init
backend_1        |     self._process_dependent_arguments()
backend_1        |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/relationships.py", line 1922, in _process_dependent_arguments
backend_1        |     self.target = self.entity.persist_selectable
backend_1        |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 855, in __get__
backend_1        |     obj.__dict__[self.__name__] = result = self.fget(obj)
backend_1        |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/relationships.py", line 1827, in entity
backend_1        |     argument = self.argument()
backend_1        |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/ext/declarative/clsregistry.py", line 306, in __call__
backend_1        |     % (self.prop.parent, self.arg, n.args[0], self.cls)
backend_1        | sqlalchemy.exc.InvalidRequestError: When initializing mapper mapped class Item->item, expression 'SubItem' failed to locate a name ("name 'SubItem' is not defined"). If this is a class name, consider adding this relationship() to the <class 'app.db_models.item.Item'> class after both dependent classes have been defined.
base-project_backend_1 exited with code 1

Expected behavior
The solution should have started normally

Additionnal context
In (most of) real use-cases, the application would have defined some CRUD operation on the new defined model, and consequently imported it here and there.

Thus making it available at the time when creating initial data.

Nevertheless, the error is so annoying and obscure (when it happens) that it deserves a safeguard (see my PR for a suggestion)

[tiangolo]

Thanks for the fix!

[ebreton]

you are more than welcome ! It's great to help on fastapi and its siblings 🐝 🐝 🐝

[tiangolo]

🎉 🍰

[Haider8]

@ebreton @tiangolo How can I do this exact change here in my repository as I also want to introduce relationships between the db_models

[dmontagu]

@Haider8 If you want to do this "exact change" you can just look at the files changed in #29 . If you want help with something more specific, you need to ask a more specific question.

[Haider8]

@dmontagu In my code, I have implemented 2 models named Venue and User like this:

class User(Base):
    id = Column(Integer, primary_key=True, index=True)
    username = Column(String(64), index=True, unique=True)
    email = Column(String(120), index=True, unique=True)
    state = Column(String(30))
    venues = relationship("Venue")
    role = Column(String(10), default="customer")

class Venue(Base):
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(64), index=True)
    owner_id = Column(Integer, ForeignKey("user.id"))

And, this is the error which I am getting:

sqlalchemy.exc.InvalidRequestError: When initializing mapper mapped class User->user, expression 'Venue' failed to locate a name ("name 'Venue' is not defined"). If this is a class name, consider adding this relationship() to the <class 'app.db_models.user.User'> class after both dependent classes have been defined.

[sebas-500]

@Haider8 were you able to solve your issue? I'm getting same error.

[Haider8]

No, I couldn't solve this. I was expecting some kind of reply from the maintainers.

[sebas-500]

No, I couldn't solve this. I was expecting some kind of reply from the maintainers.

@Haider8 I might know what is happening, just import "Venue" in your user file, that way the application will be aware of the Venue class...later you can change that import and put it some where else, in a path that the application executes at startup

[dmontagu]

I think @sebas-500 is right here; for what it's worth this is more of a sqlalchemy question than a fastapi question (or anything specific to this project generator).

Also, @Haider8 the error message you got describes precisely how to address the issue:

If this is a class name, consider adding this relationship() to the <class 'app.db_models.user.User'> class after both dependent classes have been defined.

[dmontagu]

@Haider8 This is documented in the sqlalchemy docs at the very end of the "Configuring Relationships" section here: https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/relationships.html (just above "Configuring Many-to-Many Relationships").

[gruvw]

I found this issue on google because I had a similar error. I found a solution for my problem but even if it is not really related to the initial issue, I still want to write my solution here in case someone else had the same problem as me:
I just had the same error message as discussed above, but it wasn't a bug. I just miss-typed the string assigned to the back_populate argument. So just have a look at your back_populate argument referencing the column in your other table, it could be as simple as that 😊

[princelySid]

I run into this problem when I was dynamically creating and using material views in Postgres. The first call using them would fail but subsequent ones would work. I'm not sure how exactly to describe how I solved it but I'm going to try just in case someone else comes here.
The error was not the same but similar:
sqlalchemy.exc.InvalidRequestError: Entity namespace for "table_name" has no property "column"

The material view is created using a raw query and then reflected using the standard procedure something like:

from sqlalchemy import Table, MetaData, create_engine
metadata = Metadata()
engine = create_engine(db_uri)
table = Table(table_name, metadata, autoload=True, autoload_with=engine)

So the problem was the fist time the application loaded for some reason table was not reflecting properly. Since this call was made only failed when the fastapi process was restarted, I added the table reflection to before the first call is made:

@app.on_event("startup")
def reflect_mv():
    Table(table_name, metadata, autoload=True, autoload_with=engine)

This is so it's not being reflected for the first time in one of my api calls. This solved my problem.
Hope this is helpful for someone.

[arghanath007]

@sebas-500 thanks for the fix man. Your suggestion fixed the problem I was having with relationships.

[dejoma]

I'm still having this issue, and this is 100% copy from the SQLAlchemy 2 docs.

# parent.py
class Parent(Base):
    __tablename__ = "parents"

    id: Mapped[int] = mapped_column(primary_key=True)
    ...
    children: Mapped[List["Child"]] = relationship(back_populates="parent")

# child.py
# Also tried from app.models.parent import Parent in this file, no effect.
# Also tried not using Mapped, and just parent = relationship("Parent", back_populates="parent"), also not working
class Child(Base):
    __tablename__ = "childs"  # 'classname + s'  in my case

    id: Mapped[int] = mapped_column(primary_key=True)
    ...
    parent: Mapped["Parent"] = relationship(back_populates="parent")