SQLModel / SQLAlchemy Problem with model mapper while querying in empty tables

Question

Update: This is not a problem with Relationships in SQLModel! See the answer below for more explanation!

I am relatively new to SQLModel and SQLAlchemy, however I can't figure out this one.

Basically the problem: Using SQLite as a database I have a configs table that holds 3 UUID's for different sub-configs (server, user and business) all in their separate tables. I am declaring the models as follows:

class ServerConfigBase(SQLModel):
    """Base Server Configuration."""

    address: str
    port: int
    app_configs: List["Config"] = Relationship(back_populates="server")

class ServerConfig(ServerConfigBase, table=True):
    """Configuration model describing network communication fields"""

    id: Optional[UUID] = Field(default=None, primary_key=True)

class BusinessConfigBase(SQLModel):
    """Base Business Configuration."""
    ...
    app_configs: List["Config"] = Relationship(back_populates="business")


class BusinessConfig(BusinessConfigBase, table=True):
    """Configuration model describing Business related fields"""

    id: Optional[UUID] = Field(default=None, primary_key=True)

class UserConfigBase(SQLModel):
    """Base User Configuration."""

    name: str
    app_configs: List["Config"] = Relationship(back_populates="user")


class UserConfig(UserConfigBase, table=True):
    """Configuration model describing user settable fields"""

    id: Optional[UUID] = Field(default=None, primary_key=True)

class ConfigBase(SQLModel):
    """Base Configuration."""

    server: ServerConfig = Relationship(back_populates="app_configs")
    server_conf_id: UUID = Field(foreign_key=ServerConfig.id)
    business: BusinessConfig = Relationship(back_populates="app_configs")
    business_conf_id: UUID = Field(foreign_key=BusinessConfig.id)
    user: UserConfig = Relationship(back_populates="app_configs")
    user_conf_id: UUID = Field(foreign_key=UserConfig.id)
    random_list_of_string_field: List[Dict] = Field(sa_column=Column(JSON[List[Dict]]))

class Config(ConfigBase, table=True):
    """Table configuration."""

    id: Optional[UUID] = Field(default=None, primary_key=True)
    active: bool = False

When I check on startup if the active config exists via session.exec(select(Config).where(Config.active == True)).first() (in case theres a db file already populated) I get the following error:

sqlalchemy.exc.UnboundExecutionError: Could not locate a bind configured on mapper mapped class Config->config, SQL expression or this Session.

Same happens if I were to just select all on the table. Not sure why my model is not mapping correctly. Stack trace:

  File "/Users/somaf/repos/api/src/api/controllers/config.py", line 91, in read_configs
    return session.exec(select(Config)).all()
  File "/Users/somaf/repos/api/.venv/lib/python3.9/site-packages/sqlmodel/orm/session.py", line 60, in exec
    results = super().execute(
  File "/Users/somaf/repos/api/.venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1705, in execute
    bind = self.get_bind(**bind_arguments)
  File "/Users/somaf/repos/api/.venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2154, in get_bind
    raise sa_exc.UnboundExecutionError(
sqlalchemy.exc.UnboundExecutionError: Could not locate a bind configured on mapper mapped class Config->config, SQL expression or this Session.

I didn't forget to create the tables and the session is set up correctly as well. Any help would be much appreciated and sorry if this is a nooby question.

Edit 07.01.2024

As asked here's how the create_engine function is called:

engine = create_engine(
            "sqlite://",
            connect_args={"check_same_thread": False},
            poolclass=StaticPool
        )
SQLModel.metadata.create_all(engine)

These two lines are from the main.py where a lifespan function will do this and other setup related operations. The create_engine call is going to be different after this bug is squashed to check for existing files and worst case default to an empty in-mem database. And sorry for the late reply!

Answer 1

So, I found the problem and it was to do with the engine and session as the comments also suggested. I originally suspected the Relationship to be the problem because that was the major part of the refactor and not the tidying of imports and control flow. The problem was that we originally imported the engine from a database.py file. Before the refactor engine was declared as follows:

with initialize(version_base=None, config_path="../../../config", job_name="robin_app"):
    config = compose(config_name="default")

engine = create_database_engine(config)

In the refactor I set it to be initially None before calling the create_db_and_tables from the main.py file where its' value gets updated. The problem was that even though I updated the value, the imported reference is still the older None value. In debugger it shows:

By making engine private and using a getter solved the issue. Thanks again for all to have commented and sorry for not giving an update sooner.