NoForeignKeysError when foreign key is pointing to different database

[andreasc-kuehn]

After upgrading from Flask-SQLAlchemy 2.5.1 to 3.0.0 with unchanged dependencies (same Flask 2.2.0, same SQLAlchemy 1.4.51, Python 3.9), I am getting NoForeignKeysError on formerly working foreign key definition in cases where the foreign key points to a table in a different database.

I am using Mariadb, where foreign keys can be defined across databases by using "database.table.field".

I would expect to not happen the exception.

I errantly posted the bug in SQLAlchemy and it was converted to a discussion:
sqlalchemy/sqlalchemy#11333

This is a shortened model definition that used to work:

class DataResourceRole(db.Model):
bind_key = 'resource_bind'
tablename = "DATA_RESOURCE_ROLE"
table_args = (
ForeignKeyConstraint(name="DRES_RLE_RLETYP", columns=["ROLE_TYPE_ID"], refcolumns=["contact.ROLE_TYPE.ROLE_TYPE_ID"], ondelete="RESTRICT", onupdate="CASCADE")
,{'schema': 'resource'}
)
DATA_RESOURCE_ID = Column("DATA_RESOURCE_ID", Integer, nullable=False, primary_key=True, )
CONTACT_ID = Column("CONTACT_ID", Integer, nullable=False, primary_key=True, )
ROLE_TYPE_ID = Column("ROLE_TYPE_ID", String(length=20), nullable=False, primary_key=True, )
ROLE_TYPE = relationship("RoleType")

class RoleType(db.Model):
bind_key = 'contact_bind'
tablename = "ROLE_TYPE"
table_args = {'schema': 'contact'}
ROLE_TYPE_ID = Column("ROLE_TYPE_ID", String(length=20), nullable=False, primary_key=True, )

And this is the exception that occurs. I have tracked down the cause to the fact that SQLAlchemy cannot find the primary key when the table_args = [..], {'schema': 'schemaname'} is involved.

Traceback (most recent call last):
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2754, in _determine_joins
self.primaryjoin = join_condition(
File "", line 2, in join_condition
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/sql/selectable.py", line 1229, in _join_condition
raise exc.NoForeignKeysError(
sqlalchemy.exc.NoForeignKeysError: Can't find any foreign key relationships between 'DATA_RESOURCE_ROLE' and 'ROLE_TYPE'.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/home/develop/Projects/Python/ibench1/env/lib/python3.9/site-packages/flask/app.py", line 2486, in call
return self.wsgi_app(environ, start_response)
File "/home/develop/Projects/Python/ibench1/env/lib/python3.9/site-packages/flask/app.py", line 2466, in wsgi_app
response = self.handle_exception(e)
File "/home/develop/Projects/Python/ibench1/env/lib/python3.9/site-packages/flask/app.py", line 2463, in wsgi_app
response = self.full_dispatch_request()
File "/home/develop/Projects/Python/ibench1/env/lib/python3.9/site-packages/flask/app.py", line 1760, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/home/develop/Projects/Python/ibench1/env/lib/python3.9/site-packages/flask/app.py", line 1758, in full_dispatch_request
rv = self.dispatch_request()
File "/home/develop/Projects/Python/ibench1/env/lib/python3.9/site-packages/flask/app.py", line 1734, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
File "/home/develop/Projects/Python/ibench1/env/lib/python3.9/site-packages/flask_login/utils.py", line 284, in decorated_view
elif not current_user.is_authenticated:
File "/home/develop/Projects/Python/ibench1/env/lib/python3.9/site-packages/werkzeug/local.py", line 316, in get
obj = instance._get_current_object() # type: ignore[misc]
File "/home/develop/Projects/Python/ibench1/env/lib/python3.9/site-packages/werkzeug/local.py", line 516, in _get_current_object
return get_name(local()) # type: ignore
File "/home/develop/Projects/Python/ibench1/env/lib/python3.9/site-packages/flask_login/utils.py", line 25, in
current_user = LocalProxy(lambda: _get_user())
File "/home/develop/Projects/Python/ibench1/env/lib/python3.9/site-packages/flask_login/utils.py", line 372, in _get_user
current_app.login_manager._load_user()
File "/home/develop/Projects/Python/ibench1/env/lib/python3.9/site-packages/flask_login/login_manager.py", line 364, in _load_user
user = self._user_callback(user_id)
File "/home/develop/Projects/Python/ibench1/app/auth/models_userlogin.py", line 185, in load_user
return db.session.get(UserLogin, (int(uid)))
File "", line 2, in get
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/session.py", line 2853, in get
return self._get_impl(
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/session.py", line 2975, in _get_impl
return db_load_fn(
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/loading.py", line 530, in load_on_pk_identity
session.execute(
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/session.py", line 1717, in execute
result = conn._execute_20(statement, params or {}, execution_options)
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1710, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
return connection._execute_clauseelement(
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1569, in _execute_clauseelement
compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/sql/elements.py", line 532, in _compile_w_cache
compiled_sql = self._compiler(
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/sql/elements.py", line 567, in _compiler
return dialect.statement_compiler(dialect, self, **kw)
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 809, in init
Compiled.init(self, dialect, statement, **kwargs)
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 464, in init
self.string = self.process(self.statement, **compile_kwargs)
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 499, in process
return obj._compiler_dispatch(self, **kwargs)
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/sql/visitors.py", line 82, in _compiler_dispatch
return meth(self, **kw)
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 3403, in visit_select
compile_state = select_stmt._compile_state_factory(
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/sql/base.py", line 510, in create_for_statement
return klass.create_for_statement(statement, compiler, **kw)
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/context.py", line 701, in create_for_statement
_QueryEntity.to_compile_state(
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/context.py", line 2458, in to_compile_state
_MapperEntity(
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/context.py", line 2531, in init
entity._post_inspect
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 1184, in get
obj.dict[self.name] = result = self.fget(obj)
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 2199, in _post_inspect
self._check_configure()
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 1941, in _check_configure
_configure_registries({self.registry}, cascade=True)
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 3527, in _configure_registries
_do_configure_registries(registries, cascade)
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 3566, in _do_configure_registries
mapper._post_configure_properties()
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 1958, in _post_configure_properties
prop.init()
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/interfaces.py", line 231, in init
self.do_init()
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2152, in do_init
self._setup_join_conditions()
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2248, in _setup_join_conditions
self._join_condition = jc = JoinCondition(
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2643, in init
self.determine_joins()
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2776, in determine_joins
util.raise(
File "/home/develop/Projects/Python/ibench1/env/lib64/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise
raise exception
sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship DataResourceRole.ROLE_TYPE - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

[andreasc-kuehn]

Can you please provide some kind of explanation for having closed / converted the reported bug? I am pretty sure that the «, {'schema': 'resource'}» part is ignored in the table_args definition. If this is true or not, should be worth to do some research on.
Please provide some explanation for not leaving me in a stage not understanding what is going on - and for avoiding to re-post the same bug again (and again, if necessary).

[davidism]

You specified different bind_keys, which means the tables are tracked completely separate Metadata instances. There is a unique engine and metadata for each bind key. It sounds like SQLAlchemy wants them in the same metadata if they're connected, so you'll need to use the same bind key. If that's not possible with your setup, you may need to use a custom Session class to override get_bind with what you need.

[andreasc-kuehn]

Thank you a lot for your comment and the time you spent for explaining. In fact, when setting up the database some years ago, I may have missed to take into account the different wording of "database" and "schema".

What I did wrong:

• I setup an SQLALCHEMY_BINDS record where each database/schema was listed, in the form of mysql+pymysql://user:pass@host:port/schema. The connection part was all the same for each "schema".
• in each (declarative) model definition I added the associated bind key with __bind_key__ = '<bind_key>'
• I set __table_args__ = {'schema': 'mariadb_database_name'} only on some of the model declarations, but only where needed for foreign keys. This led to the wrong assumption, that a bind_key configuration was needed in order to get access to tables in the different databases/schemas.
• since everything worked fine for all those years, including Flask-SQLAlchemy 2.5.1, I never assumed a wrong use of bind.

What I changed now:

• I completely removed the SQLALCHEMY_BINDS record.
• in the model declarations I set bind_key = None, which was easier for me than removing it.
• I set SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://user:pass@host:port', skipping the database part of the URI.
• I set __table_args__ = {'schema': 'mariadb_database_name'} in all model declarations.

Like this the described error did not appear again after upgrading. Now I am on Flask-SQLAlchemy==3.1.1 and SQLAlchemy==2.0.30.

Your remark, that tables are separated completely, makes sense, but there seems to be a more loose definition of "separate" before Flask-SQLAlchemy 3.0.0. At least I have no problem with the fixes, I had to do, leading to a more concise configuration.

Thank you again for your time and efforts.