TechOverflow Logo
TechOverflow

sqlmodel: How to fix NoForeignKeysError: Could not determine join condition between parent/child tables on relationship

Problem

When running your Python script using sqlmodel with model definition code such as

from typing import List, Optional
from sqlmodel import ForeignKey, Relationship, SQLModel, Field
from sqlalchemy import create_engine
from sqlmodel import SQLModel

class Manufacturer(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(description="Manufacturer name")
    products: List["Product"] = Relationship(
        back_populates="manufacturer"
    )

class Product(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    mpn: Optional[str] = Field(description="Manufacturer part number", index=True)
    manufacturer: Optional[Manufacturer] = Relationship(
        back_populates="products"
    )

you see an error message such as

Traceback (most recent call last):
  File "/home/uli/Nextcloud/Experimental/InventreeLCSC/TestModel.py", line 25, in <module>
    product: Product = Product(mpn="123456")
                           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<string>", line 4, in __init__
  File "/home/uli/.local/lib/python3.12/site-packages/sqlalchemy/orm/state.py", line 566, in _initialize_instance
    manager.dispatch.init(self, args, kwargs)
  File "/home/uli/.local/lib/python3.12/site-packages/sqlalchemy/event/attr.py", line 497, in __call__
    fn(*args, **kw)
  File "/home/uli/.local/lib/python3.12/site-packages/sqlalchemy/orm/mapper.py", line 4396, in _event_on_init
    instrumenting_mapper._check_configure()
  File "/home/uli/.local/lib/python3.12/site-packages/sqlalchemy/orm/mapper.py", line 2388, in _check_configure
    _configure_registries({self.registry}, cascade=True)
  File "/home/uli/.local/lib/python3.12/site-packages/sqlalchemy/orm/mapper.py", line 4204, in _configure_registries
    _do_configure_registries(registries, cascade)
  File "/home/uli/.local/lib/python3.12/site-packages/sqlalchemy/orm/mapper.py", line 4245, in _do_configure_registries
    mapper._post_configure_properties()
  File "/home/uli/.local/lib/python3.12/site-packages/sqlalchemy/orm/mapper.py", line 2405, in _post_configure_properties
    prop.init()
  File "/home/uli/.local/lib/python3.12/site-packages/sqlalchemy/orm/interfaces.py", line 584, in init
    self.do_init()
  File "/home/uli/.local/lib/python3.12/site-packages/sqlalchemy/orm/relationships.py", line 1644, in do_init
    self._setup_join_conditions()
  File "/home/uli/.local/lib/python3.12/site-packages/sqlalchemy/orm/relationships.py", line 1884, in _setup_join_conditions
    self._join_condition = jc = JoinCondition(
                                ^^^^^^^^^^^^^^
  File "/home/uli/.local/lib/python3.12/site-packages/sqlalchemy/orm/relationships.py", line 2310, in __init__
    self._determine_joins()
  File "/home/uli/.local/lib/python3.12/site-packages/sqlalchemy/orm/relationships.py", line 2444, in _determine_joins
    raise sa_exc.NoForeignKeysError(
sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Manufacturer.products - there are no foreign keys linking these tables.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

Solution

When you encounter this error, the solution is simple: Explicitly specify the ForeignKey relationship by adding:

sa_relationship=ForeignKey("product.id")

as an argument to both sides of the Relationship field definition.

Full example:

from typing import List, Optional
from sqlmodel import ForeignKey, Relationship, SQLModel, Field
from sqlalchemy import create_engine
from sqlmodel import SQLModel

class Manufacturer(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(description="Manufacturer name")
    products: List["Product"] = Relationship(
        back_populates="manufacturer",
        sa_relationship=ForeignKey("product.id")
    )

class Product(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    mpn: Optional[str] = Field(description="Manufacturer part number", index=True)
    manufacturer: Optional[Manufacturer] = Relationship(
        back_populates="products",
        sa_relationship=ForeignKey("manufacturer.id")
    )