SQLAlchemy - Pytest - One or more mappers failed to initialize - can't proceed with initialization of other mappers

Question

I am trying to create a DB with user and project tables having a many-to-many relation. A user can be a part of many projects and a project can have many users. I created the models for the user, project and the association tables and am able to create the database using alembic without any issues.

However, when i try to use the models. it throws an exception

sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Triggering mapper: 'mapped class User->users'. Original exception was: reverse_property 'user' on relationship User.projects references relationship ProjectUsers.user, which does not reference mapper mapped class User->users

Here is the code for the models

from app.modules.database import BaseModel

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

from sqlalchemy_json import mutable_json_type


class Project(BaseModel):
    __tablename__ = "projects"
    __table_args__ = {'extend_existing': True}

    project_name = Column(String, unique=True)
    project_desc = Column(String)


    users = relationship('app.modules.projects.project_models.ProjectUsers', back_populates='project')

    owner_id = Column(Integer, ForeignKey('users.id'))


class ProjectUsers(BaseModel):
    __tablename__ = "project_users"
    __table_args__ = {'extend_existing': True}

    user_id = Column(Integer, ForeignKey('users.id'), primary_key=True)
    project_id = Column(Integer, ForeignKey('projects.id'), primary_key=True)

    user = relationship("app.modules.users.user_models.User", back_populates="projects")
    project = relationship("app.modules.projects.project_models.Project", back_populates="users")

The User model is :

from app.modules.database import BaseModel

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


@dataclass
class User(BaseModel):
    __tablename__ = "users"
    __table_args__ = {'extend_existing': True}

    email = Column(String, unique=True)
    username = Column(String, unique=True)
    f_name = Column(String)
    l_name = Column(String)
    m_name = Column(String)

    role_id = Column(Integer, ForeignKey('roles.id'))
    #user_role = relationship('app.modules.roles.role_models.Role', backref="users")

    # A user can have access to many projects where they are not the owners of the project.
    #projects = relationship('app.modules.projects.project_models.Project', secondary="project_users", back_populates="users")

    projects = relationship('app.modules.projects.project_models.ProjectUsers', back_populates='user')


    def __repr__(self):
        return '<User: {}>'.format(self.username)

Alembic creates the corresponding tables with the proper associations without any issues, but running a simple test like creating a new User() or Project() throws the above exception.

Answer 1

I think the issue might come from using a dataclass without using the correct mapping method. There are three ways to apply ORM Mappings to a dataclass. You seem to use declarative mapping so I'll just apply the declarative mapping method for SQLAlchemy 1.4.

@mapper_registry.mapped
@dataclass
class User:
    __tablename__ = "users"
    __table_args__ = {'extend_existing': True}
    __sa_dataclass_metadata_key__ = "sa"

    email: str | None = field(init=False, metadata={"sa": Column(String, unique=True)})
    username: str | None = field(init=False, metadata={"sa": Column(String, unique=True)})
    f_name: str | None = field(init=False, metadata={"sa": Column(String)})
    l_name: str | None = field(init=False, metadata={"sa": Column(String)})
    m_name: str | None = field(init=False, metadata={"sa": Column(String)})

    role_id: int | None = field(init=False, metadata={"sa": Column(Integer, ForeignKey('roles.id'))})
    #user_role = relationship('app.modules.roles.role_models.Role', backref="users")

    # A user can have access to many projects where they are not the owners of the project.
    #projects = relationship('app.modules.projects.project_models.Project', secondary="project_users", back_populates="users")

    projects: list[Project] = field(default_factory=list, metadata={"sa": relationship('app.modules.projects.project_models.ProjectUsers', back_populates='user')})

    # define repr by using the dataclasses.field repr kwarg

If that alone does not solve your problem, please add the definition of app.modules.database.BaseModel to your question.