Converting from Pydantic/SQLAlchemy to SQLModel Relationship issues

[jflad17]

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

The schema and model were made back a few years ago, but here's what I'm trying to use.

Pydantic Schema:
    to_airport: Optional[Airport]
    from_airport: Optional[Airport]

SQLAlchemy Schema:
    to_Airport_id = Column(Integer, ForeignKey("Airport.id"))
    from_Airport_id = Column(Integer, ForeignKey("Airport.id"))

    to_airport = relationship("Airport", foreign_keys=to_Airport_id)
    from_airport = relationship("Airport", foreign_keys=from_Airport_id)

SQL Model:
   to_airport: Airport = Relationship(sa_relationship=relationship(
           "Airport",
           primaryjoin="Flight.to_airport_id == Airport.id",
           lazy="joined",
       )
    )

Description

Migrating from 2-3 year old code that I abandoned, now bringing it back to upgrade everything to latest FastAPI, python, etc. That's when I found out about SQLModel, and figured at the same time I could migrate to that as well.

Everything that doesn't use relationship is working fine (I even managed to get fastapi_crudrouter to work after forking it since that was abandoned it looks like, so had to adjust for Pydantic changes, but overall everything I've found works. Migrated to latest on everything and even moved from MySQL to Postgres successfully.

Main issue is the relationships, I've spent countless hours, 50+ tabs, trying to understand why SQLModel won't display the properties but I can't seem to figure it out.

Here's what I've tried so far:

1. using back_populates on the Airport model using
   
   and on the other model side
   
   2nd model works fine but the first model returns a circular import error
2. Moving relationship to new model and having old model as base
   class model(base, table=true):
   to_airport: "Airport" | None = relationship("Airport", foreign_keys=to_airport_id)
   or to _airport: Airport | None = Relationship(sa_relationship_kwargs={"lazy": "joined",
   "primaryjoin": "model.to_airport_id==airport.id"}
3. Used other combinations of SQLModel Relationship with sa_relationship, sa_relationship_args, sa_relationship_kwargs, etc
4. Used uselist false and true, both did nothing
5. I'm running via debug mode and query is coming out correctly with a left outer join (maybe not the best, but still works)
6. If I try to use the sqlalchemy old way of relationship like what I used years ago, it returns a type error
7. Relationship setup like the example I have where I marked SQLModel, returns no errors, query actually looks correct, but the class property doesn't show up when I run the api call, that's the ultimate goal of this discussion, otherwise I could just revert back to pydantic/sqlalchemy, but I figured I'd try it out and see what I can work with.
8. Used pipe None or | None instead of Optional, figured this didn't matter since it's supposed to work the same

I'm not sure if I'm just missing something with SQLModel or what, but I think I tried just about everything I could think of. I think i kept confusing Copilot as I kept making so many changes for hours.

BTW most of my relationships are supposed to be one to many. I do have one that is many to many but I wanted to focus on the one to many ones first.

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.24

Python Version

Python 3.11.5 & just updated to Python 3.13.3

Additional Context

No response

[jflad17]

Hopefully this helps other people who are stuck on relationships.

I did manage to get it to work but via using schemas still (will read more about it to verify if setting table to false or just not including would replace the schemas like how I'm thinking)

I began by changing the file structure a bit. I now instead created a src folder under the main folder to hold each category of folders, then in each folder it has the models and schemas associated with each category. But the examples that I figured out works are adding this to the model you want the data in since I wanted to find out the user info of this row

Then adding this on the many model

User schema uncomment if I want it to go the other way. I currently do not want this, so commented out:

Other schema, not sure if it matters but it's inheriting base and just adding the relationship properties on the other class. I did this mainly for create/update schemas back when I first created this and probably will keep it that way.

Solution is probably not 100% correct either but looks like it works. I assumed initially with SQLModel conversion, I could fully remove schemas. But it looks like as of right now, no or at least not like I thought. I'm sure I could make it so that I base models off of the base SQLModel model class and not include the table, but I'll have to mess around with it more and see if I can get it working, now that I remember and understand how back_populates works as well (it just associates the sqlalchemy side to setup joins and everything, it doesn't tell the api to return it, that's what a regular model would do aka not including table)

This video also helped me https://www.youtube.com/watch?v=H-7NNblkzrg

[jflad17]

Btw I'll update when I get everything converted to SQLModel Non-table (Schemas).