Mapper "NoReferencedTableError" exception when trying to implement many-to-many ORM data model with SQLAclhemy+Flask

I'm volunteering for a small local community's project while learning Python at the same time. It's first time with Flask and SQLAlchemy for me (MySQL is used to store the data). ORM I chose was working fine at first, but when I tried to switch to many-to-many model between two tables following this official guide, this error "NoReferencedTableError" started to appear during main Flask app's initialization.

Here is full error:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'assoc_tbl_1_user_X_challenge.challengeId' could not find table 'challengesTbl' with which to generate a foreign key to target column 'id'
...
sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Triggering mapper: 'Mapper[User(usersTbl)]'. Original exception was: Foreign key associated with column 'assoc_tbl_1_user_X_challenge.challengeId' could not find table 'challengesTbl' with which to generate a foreign key to target column 'id'

I can't spot an error in my definitions no matter how hard I try. I've tried to google for that error, but other cases seem to be about people making some typo in table or class names - what I double-checked, no typos here. I start to suspect it may have something to do with the fact that association_table1 variable is defined before the classes which define other tables, and when it's instantiated it can't find them. But even if I move it to the end of the file, it still doesn't work, now because it's referenced from the other tables classes' definitions. Declaring it first and initializing at the end of the file didn't help either.

I also noted that when I try to run an external script that pre-initializes my database using the same ORM file, only three tables seem to be created:

mysql> show tables;
+------------------------------+
| Tables_in_nekoko$ABsitedb    |
+------------------------------+
| challengesTbl                |
| rideRecordingsTbl            |
| usersTbl                     |
+------------------------------+
3 rows in set (0.00 sec)

I don't see association table assoc_tbl_1_user_X_challenge, shouldn't it be created at the same time as well?

My current data model is this:

from . import db
from flask_login import UserMixin
from sqlalchemy import text

class Base(db.DeclarativeBase):
    pass

association_table1 = db.Table(
    "assoc_tbl_1_user_X_challenge",
    Base.metadata,
    db.Column("userId", db.ForeignKey("usersTbl.id"), primary_key=True),
    db.Column("challengeId", db.ForeignKey("challengesTbl.id"), primary_key=True),
)

class User(UserMixin, db.Model):
    __tablename__ = "usersTbl"
    id = db.Column(db.Integer, primary_key=True) # primary keys are required by SQLAlchemy
    email = db.Column(db.String(100), unique=True)
    password = db.Column(db.String(1000))
    givenName = db.Column(db.String(100))
    sn = db.Column(db.String(100))
    totalDistanceKm = db.Column(db.Float, server_default=text("0.0"))
    groups = db.Column(db.JSON, nullable=False)
    isActive = db.Column(db.Boolean, default=True, nullable=False, index=True)
    challenges = db.relationship('Challenge', secondary=association_table1, back_populates="participants")
    rides = db.relationship('Ride', backref='usersTbl', lazy=True)

    def __repr__(self):
        return f'<User "{self.email}">'

class Ride(db.Model):
    __tablename__ = "rideRecordingsTbl"
    id = db.Column(db.Integer, primary_key=True)
    rideRecording = db.Column(db.LargeBinary)
    #rideRecording = db.Column(db.LONGBLOB, nullable=False)
    rideName = db.Column(db.String(150))
    distanceKm = db.Column(db.Float, server_default=text("0.0"))
    duration = db.Column(db.Time)
    uploadDateTime = db.Column(db.DateTime)
    stream_hash = db.Column(db.String(1000))
    ownerId = db.Column(db.Integer, db.ForeignKey('usersTbl.id'), nullable=False)

    def __repr__(self):
        return f'<Ride "{self.rideName[:20]}...">'

class Challenge(db.Model):
    __tablename__ = "challengesTbl"
    id = db.Column(db.Integer, primary_key=True)
    rideRecording = db.Column(db.LargeBinary)
    challengeName = db.Column(db.String(150))
    challengeTargetDistanceKm = db.Column(db.Float, server_default=text("0.0"))
    challengeCurrnetTotalDistanceKm = db.Column(db.Float, server_default=text("0.0"))
    challengeEnds = db.Column(db.DateTime)
    challengeStarts = db.Column(db.DateTime)
    createdByUserId = db.Column(db.Integer)
    isActive = db.Column(db.Boolean, default=True, nullable=False, index=True)
    participants = db.relationship('User', secondary=association_table1, back_populates="challenges")

    def __repr__(self):
        return f'<Challenge "{self.challengeName[:20]}...">'

Solved! Here are the details:

First, @dann were correct about the issues with pieces being used before declared, their proposal was in the right direction. It still didn't work after that, until I also applied solution proposed by @matthew-moisen in this thread.

I still decided I'll mark answer from @dann as solution, as I think it did most of the job. Thanks!

So, finally the relevant part started to look like this (see dann's answer below for the complete context):

association_table1 = db.Table(
    "assoc_tbl_1_user_X_challenge",
    Base.metadata,
    #db.Column("userId", db.ForeignKey("usersTbl.id"), primary_key=True),
    #db.Column("challengeId", db.ForeignKey("challengesTbl.id"), primary_key=True)
    db.Column("userId", db.ForeignKey(User.id), primary_key=True),
    db.Column("challengeId", db.ForeignKey(Challenge.id), primary_key=True)
)

User.challenges = db.relationship('Challenge', secondary=association_table1, back_populates="participants")
Challenge.participants = db.relationship('User', secondary=association_table1, back_populates="challenges")

Solution

The error you provided usually happens because of the order in which tables and relationships are defined.

If you are defining the association table before the other tables, SQLAlchemy might not have all the information needed to resolve the foreign keys correctly. You need to make sure that the tables referenced by the foreign keys in the association table are defined before the association table itself is defined.

So, you could use declarative base approach with Base to ensure the proper ordering and then define the association table inside the class definitions where both the User and Challenge classes are already defined.

Therefore, first define association after both User and Challenge classes are defined. Then, to the same for relationships: add User.challenges and Challenge.participants after defining association_table1. So, your final code should be looking more or less like this:

from flask_sqlalchemy import SQLAlchemy
from flask_login import UserMixin
from sqlalchemy import text

db = SQLAlchemy()

class Base(db.Model):
    #this will make the base class abstract, so it won't be mapped to a table.
    __abstract__ = True

#define User and Challenge classes before the association table
class User(UserMixin, Base):
    __tablename__ = "usersTbl"
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(100), unique=True)
    password = db.Column(db.String(1000))
    givenName = db.Column(db.String(100))
    sn = db.Column(db.String(100))
    totalDistanceKm = db.Column(db.Float, server_default=text("0.0"))
    groups = db.Column(db.JSON, nullable=False)
    isActive = db.Column(db.Boolean, default=True, nullable=False, index=True)
    rides = db.relationship('Ride', backref='user', lazy=True)

    def __repr__(self):
        return f'<User "{self.email}">'

class Ride(Base):
    __tablename__ = "rideRecordingsTbl"
    id = db.Column(db.Integer, primary_key=True)
    rideRecording = db.Column(db.LargeBinary)
    rideName = db.Column(db.String(150))
    distanceKm = db.Column(db.Float, server_default=text("0.0"))
    duration = db.Column(db.Time)
    uploadDateTime = db.Column(db.DateTime)
    stream_hash = db.Column(db.String(1000))
    ownerId = db.Column(db.Integer, db.ForeignKey('usersTbl.id'), nullable=False)

    def __repr__(self):
        return f'<Ride "{self.rideName[:20]}...">'

class Challenge(Base):
    __tablename__ = "challengesTbl"
    id = db.Column(db.Integer, primary_key=True)
    rideRecording = db.Column(db.LargeBinary)
    challengeName = db.Column(db.String(150))
    challengeTargetDistanceKm = db.Column(db.Float, server_default=text("0.0"))
    challengeCurrentTotalDistanceKm = db.Column(db.Float, server_default=text("0.0"))
    challengeEnds = db.Column(db.DateTime)
    challengeStarts = db.Column(db.DateTime)
    createdByUserId = db.Column(db.Integer)
    isActive = db.Column(db.Boolean, default=True, nullable=False, index=True)
    
    def __repr__(self):
        return f'<Challenge "{self.challengeName[:20]}...">'

#define association table after User and Challenge classes
association_table1 = db.Table(
    "assoc_tbl_1_user_X_challenge",
    Base.metadata,
    db.Column("userId", db.Integer, db.ForeignKey("usersTbl.id"), primary_key=True),
    db.Column("challengeId", db.Integer, db.ForeignKey("challengesTbl.id"), primary_key=True),
)

#add relationships after defining the association table
User.challenges = db.relationship('Challenge', secondary=association_table1, back_populates="participants")
Challenge.participants = db.relationship('User', secondary=association_table1, back_populates="challenges")

Keep me updated, hope it works.