Error with Relationships: Foreign key associated with column [...] could not find table

[betterthanever2]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

# >>> project.database.models.model1.py

from typing import Optional, TYPE_CHECKING, List
from sqlmodel import Field, SQLModel, MetaData, Relationship, ARRAY
from datetime import datetime
from pydantic import ConfigDict
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.orm import RelationshipProperty

if TYPE_CHECKING:
    from project.database.models.model2 import Table2


class Table1(SQLModel, table=True):
    metadata = MetaData(schema="non-public-schema")
    model_config = ConfigDict(from_attributes=True, arbitrary_types_allowed=True)
    __tablename__ = "table1"

    id: Optional[int] = Field(primary_key=True, default=None)
    created_at: Optional[datetime] = Field(default=None)
    title: Optional[str] = None
    status: str = "uploaded"
    
    processing_details: List["Table2"] = Relationship(back_populates="table1_rec")

>>> project.database.models.model2.py

from datetime import datetime
from typing import Optional, TYPE_CHECKING, List
from sqlmodel import Field, SQLModel, MetaData, Relationship
from pydantic import ConfigDict
from sqlalchemy.orm import RelationshipProperty

if TYPE_CHECKING:
    from project.database.models.model1 import Table1


class Table2(SQLModel, table=True):
    metadata = MetaData(schema="non-public-schema")
    __tablename__ = "table2"
    model_config = ConfigDict(from_attributes=True, arbitrary_types_allowed=True)

    id: Optional[int] = Field(default=None, primary_key=True)
    created_at: Optional[datetime] = Field(default=None)
    job_start_timestamp: Optional[datetime] = None
    job_end_timestamp: Optional[datetime] = None
    table1_id: Optional[int] = Field(default=None, foreign_key="non-public-schema.table1.id")
    table1_rec: Optional["Table1"] = Relationship(back_populates="processing_details")

>>> main.py
from sqlmodel import SQLModel
from project.database.models.model1 import Table1
from project.database.models.model2 import Table2

def create_db_and_tables(db_client):
    SQLModel.metadata.create_all(db_client.engine)
	
def main():
	db_config = ...  # properly loaded config
	db_client = ...  # properly configured SQLAlchemy client/engine
	
	create_db_and_tables(db_client)

	with db_client.get_session() as session:  # SQLModel's session
	new_record = select(Table1).filter(Table1.status == "uploaded").order_by(Table1.created_at).limit(1)
	res = session.exec(new_record).first()

Description

My models are defined in separate files, and then brought together in main.py. The relationship is supposed to be one-to-one.

Running the example code results in an error:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'table2.table1_id' could not find table 'non-public-schema.table1' with which to generate a foreign key to target column 'id'

I tried adding uselist = False via sa_relathionship_kwargs, specifying table name with RelationshipProperty, as well as numerous other little things, but nothing works - I consistently get the above exception. Not sure what am I doing wrong, since the code (as far as I can see) conforms with the SQLModel documentation.

I realize this exception comes from SQLAlchemy, but I haven't found the answer there; maybe somebody here can help me at least understand what's going on here. Please, help.

Operating System

Windows

Operating System Details

Windows11

SQLModel Version

0.0.14

Python Version

3.11.4

Additional Context

No response

[charlie-corus]

EDIT:
I'm leaving the below in place for posterity, but I think the actual solution is simpler and I had some major misunderstandings when I wrote it. When you create a MetaData object and use it to set properties on the tables, it interferes with any calls that go via SQLModel.metadata, which is referencing its own MetaData object stored in a class variable on SQLModel. The simplest way to set the schema is to call

SQLModel.metadata.schema = "your-schema-name"

BEFORE the models are imported.

As an aside, I don't have a neat solution for controlling this dynamically. This blog post describes how to do it in SQLAlchemy, but Base.metadata seems to behave differently from SQLModel.metadata in ways I don't understand (and may be imagining).

ORIGINAL ANSWER:
I've looked into this for you and I think I have a solution. It seems that if you use a MetaData object in two different tables, it has to be the same object (this is hinted at in the SQLAlchemy docs). What happens in your example is that each table creates its own MetaData object with the same properties. For some reason this doesn't cut it.

You can fix this one of two ways:

1. Make a new file, project/database/database_info.py, containing:

from sqlmodel import MetaData

schema_info = MetaData(schema="non-public-schema")

In your model files add the line from ..database_info import schema_info. Then use metadata = schema_info in your model definitions instead of metadata = MetaData(schema="non-public-schema")

2. Set the schema with a table argument, i.e. use __table_args__ = {"schema": "non-public-schema"} instead of metadata = MetaData(schema="non-public-schema")

[betterthanever2]

Thank you @charlie-corus. I don't know how to express my gratitude - this has been sitting in my brain like a splinter for a while. You are a great releaver of suffering.
I tried the 2nd way (with __table_args__) and it works wonderfully. There is a small caveat, though: if you're using db connection as context manager, you need to set SQLAlchemy's param lazy to selectin on the relationships declaration, like so:

relationship_example: Optional["SomeOtherTable"] = Relationship(sa_relationship_kwargs={"lazy": "selectin", "back_populates": "some_field_name"})

The 1st way seems like it should work, but I think I'm gonna keep it simple :)

[charlie-corus]

Glad it helped. I came across your question while trying to fulfil the pledge by ticking the "Commit to Help" box when I posted my own one. Remember to pay if forward :)