Establish relationship using Relationship feature in Postgres

[cabudies]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue 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

## code example
db_unit = models.CreateUnit(
  unit = unit.unit,
  unit_code = unit.unit_code,
  unit_multiples = unit.unit_multiples
)
db_unit.createunittype = unit_types
session.add(db_unit)
await session.commit()
await session.refresh(db_unit)

## models
class CreateUnitType(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    mass: str
    volume: int
    length: int
    electric_current: str

    units: List["CreateUnit"] = Relationship(back_populates='createunittype')

class CreateUnit(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    unit: str
    unit_code: str
    unit_multiples: int

    unit_type_id: Optional[int] = Field(default=None, foreign_key="createunittype.id")
    createunittype: Optional[CreateUnitType] = Relationship(back_populates="units")

Description

I have two different models CreateUnitType, CreateUnit attached with unit_type_id field as foreign key to CreateUnitType table and the whole database is in postgres. I want to establish the Relationship feature but somehow whenever I try to run the query I get this following error -

AttributeError: Could not locate column in row for column '_sa_instance_state'

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.7.8

Additional Context

No response

[JLHasson]

I'm not sure what exactly the problem with your code is. One small issue is that you're instantiating db_unit before you've declared CreateUnit. I've used the code below to try to replicate and I was unsuccessful:

from typing import Optional, List

from sqlmodel import Field, Session, SQLModel, create_engine, select, Relationship


class CreateUnitType(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    mass: str

    units: List["CreateUnit"] = Relationship(back_populates="createunittype")


class CreateUnit(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    unit: str

    unit_type_id: Optional[int] = Field(default=None, foreign_key="createunittype.id")
    createunittype: Optional[CreateUnitType] = Relationship(back_populates="units")


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)

if __name__ == "__main__":
    SQLModel.metadata.create_all(engine)

    unit = CreateUnit(unit="unit", createunittype=CreateUnitType(mass="1kg"))
    print(unit)

    with Session(engine) as session:
        session.add(unit)

        session.commit()
    with Session(engine) as session:
        statement = select(CreateUnit)
        results = session.exec(statement)
        for unit in results:
            print(unit)
        statement = select(CreateUnitType)
        results = session.exec(statement)
        for unit in results:
            print(unit)

Output:

2021-10-26 00:33:23,703 INFO sqlalchemy.engine.Engine SELECT createunit.id, createunit.unit, createunit.unit_type_id
FROM createunit
2021-10-26 00:33:23,703 INFO sqlalchemy.engine.Engine [no key 0.00014s] ()
unit='unit' id=1 unit_type_id=1
2021-10-26 00:33:23,705 INFO sqlalchemy.engine.Engine SELECT createunittype.id, createunittype.mass
FROM createunittype
2021-10-26 00:33:23,705 INFO sqlalchemy.engine.Engine [no key 0.00013s] ()
id=1 mass='1kg'
2021-10-26 00:33:23,705 INFO sqlalchemy.engine.Engine ROLLBACK

Granted I tested this with a SQLite DB but I'm not sure that it should make a different (afaik Postgres should behave the same way).

Can you try the code I've pasted above and let me know if it works for you?