flask migrate with multidb gets sqlalchemy.exc.NoReferencedTable error with flask-sqlalchemy 3.x but not 2.5.1

[louking]

I put a question on the pallets discord for this but got no response -- https://discord.com/channels/531221516914917387/531571912619458591/1101567562535424090

After a couple of days scratching my head for louking/mysql-docker#3 I remembered I'd seen it before. My comments at the time were louking/webmodules#26 (comment), pointing at flask-sqlalchemy's version 3.x. Having said that the error happens during flask-migrate, so I'm raising the question here.

This seems fairly basic, so I'm not sure what I'm doing wrong. I have a simple model for the default database, and a "users" model a la flask-security-too for the users database, bound via SQLALCHEMY_BINDS. When I try to do the initial migration, there are errors while migrating the default database indicating the software is looking at the users database rather than the default.

This happens with https://github.com/louking/mysql-docker/tree/2e1d8b2f6468257fae5268801797cfed82ea3c22

Sorry about the added complexity due to docker.

Or maybe stackoverflow is a better place for this?

[miguelgrinberg]

Flask-Migrate is a thin wrapper for Alembic, there is very little database-specific logic. Is there any chance this is actually a problem in your model definitions? It seems you are upgrading both SQLAlchemy and Flask-SQLAlchemy over major versions. Maybe something change there? Have you read the error message that you are getting? It seems to indicate there is a problem with your model definitions.

Also, it goes without saying that if you are upgrading SQLAlchemy and Flask-SQLAlchemy you should also upgrade Flask-Migrate and Alembic, regardless of this issue.

[louking]

Perhaps this is more a question for flask-sqlalchemy than flask-migrate.

I installed all the packages fresh for this project, so I think these were the latest. Here's what I had which didn't work for flask db migrate.

Flask-Migrate==4.0.4
Flask-SQLAlchemy==3.0.5
SQLAlchemy==2.0.17
alembic==1.11.1

and I needed to replace with the following which works for flask db migrate and the application.

Flask-SQLAlchemy==2.5.1
SQLAlchemy==1.4.47

I think the model is very simple and unless I really don't understand how binds works, is correct. But see [1].

The users database tables all have bind_key defined.

The error message is about the apps_interests table which has bind_key defined, but is generated during the <default> migration.

# this is here just to make flask-migrations happy
class StubTable(Base):
    __tablename__ = 'stubtable'
    id      = Column(Integer(), primary_key=True)
    title   = Column(Text)

and the users database has

userinterest_table = Table('users_interests', Base.metadata,
                           Column('user_id', Integer, ForeignKey('user.id')),
                           Column('interest_id', Integer, ForeignKey('interest.id')),
                           info={'bind_key': 'users'},
                          )

appinterest_table = Table('apps_interests', Base.metadata,
                           Column('application_id', Integer, ForeignKey('application.id')),
                           Column('interest_id', Integer, ForeignKey('interest.id')),
                           info={'bind_key': 'users'},
                          )

approle_table = Table('apps_roles', Base.metadata,
                           Column('application_id', Integer, ForeignKey('application.id')),
                           Column('role_id', Integer, ForeignKey('role.id')),
                           info={'bind_key': 'users'},
                          )

class Interest(Base):
    __tablename__ = 'interest'
    __bind_key__ = 'users'
    id                  = Column(Integer(), primary_key=True)
    interest            = Column(String(INTEREST_LEN))
    users               = relationship("User",
                                       secondary=userinterest_table,
                                       backref=backref("interests"))
    applications        = relationship("Application",
                                       secondary=appinterest_table,
                                       backref=backref("interests"))
    description         = Column(String(DESCR_LEN))
    public              = Column(Boolean)

    version_id = Column(Integer, nullable=False, default=1)
    __mapper_args__ = {
        'version_id_col': version_id
    }

class Application(Base):
    __tablename__ = 'application'
    __bind_key__ = 'users'
    id              = Column(Integer(), primary_key=True)
    application     = Column(String(APPLICATION_LEN))

    version_id = Column(Integer, nullable=False, default=1)
    __mapper_args__ = {
        'version_id_col': version_id
    }

# user role management
# adapted from
#   https://flask-security-too.readthedocs.io/en/stable/quickstart.html (SQLAlchemy Application)

class RolesUsers(Base):
    __tablename__ = 'roles_users'
    __bind_key__ = 'users'
    id = Column(Integer(), primary_key=True)
    user_id = Column('user_id', Integer(), ForeignKey('user.id'))
    role_id = Column('role_id', Integer(), ForeignKey('role.id'))

class Role(Base, RoleMixin):
    __tablename__ = 'role'
    __bind_key__ = 'users'
    id                  = Column(Integer(), primary_key=True)
    name                = Column(String(ROLENAME_LEN), unique=True)
    description         = Column(String(USERROLEDESCR_LEN))
    applications        = relationship("Application",
                                       secondary=approle_table,
                                       backref=backref("roles"))

    version_id = Column(Integer, nullable=False, default=1)
    __mapper_args__ = {
        'version_id_col': version_id
    }

copy_local_user_attrs = ['name', 'email', 'given_name', 'active' ]
class LocalUserMixin(object):
    user_id             = Column(Integer)
    email               = Column( String(EMAIL_LEN) )
    name                = Column( String(NAME_LEN) )
    given_name          = Column( String(NAME_LEN) )
    active              = Column(Boolean)

class User(Base, UserMixin):
    __tablename__ = 'user'
    __bind_key__ = 'users'
    id                  = Column(Integer, primary_key=True)
    email               = Column( String(EMAIL_LEN), unique=True )  # = username
    password            = Column( String(PASSWORD_LEN) )
    name                = Column( String(NAME_LEN) )
    given_name          = Column( String(NAME_LEN) )
    last_login_at       = Column( DateTime() )
    current_login_at    = Column( DateTime() )
    last_login_ip       = Column( String(100) )
    current_login_ip    = Column( String(100) )
    login_count         = Column( Integer )
    active              = Column( Boolean() )
    fs_uniquifier       = Column( String(UNIQUIFIER_LEN), nullable=False )
    confirmed_at        = Column( DateTime() )
    roles               = relationship('Role', secondary='roles_users',
                          backref=backref('users', lazy='dynamic'))

    version_id = Column(Integer, nullable=False, default=1)
    __mapper_args__ = {
        'version_id_col': version_id
    }

[1] But looking at the syntax for apps_interests I wonder if the flask-sqlalchemy syntax to define bind_key with a Table() has changed with rev 3? Not sure why I didn't think about this before. I'll look into that and report back.

[louking]

It looks like the Table() definition has changed for binds with flask-sqlalchemy 3.x. Compare https://flask-sqlalchemy.palletsprojects.com/en/2.x/binds/

user_favorites = db.Table('user_favorites',
    db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
    db.Column('message_id', db.Integer, db.ForeignKey('message.id')),
    info={'bind_key': 'users'}
)

with https://flask-sqlalchemy.palletsprojects.com/en/3.0.x/binds/

user_table = db.Table(
    "user",
    db.Column("id", db.Integer, primary_key=True),
    bind_key="auth",
)

Not sure why it took me pasting my model into this question to consider that possibility.

I do need to test this, but will close this question at this point because it seems likely this is resolved.

Thanks for listening.