unexpected behaviour: on commit fk turns into None and violates the constraints

[giovanni-bellini-argo]

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

# common
from sqlmodel import SQLModel, Session, select
from errors import ObjectNotFound
from sqlalchemy import func

class CommonFunc(SQLModel):
    def add(self, db: Session, object: SQLModel):
        obj = object.model_validate(self)
        db.add(obj)
        db.commit()
        db.refresh(obj) # updates the object with the new entries of the db
        return obj

# site
from .common import CommonFunc
from sqlmodel import Field
from log import *

class Site(CommonFunc):
    name: str
    tenant_id: int = Field(foreign_key='tenantsql.id')

# tables
class SiteSQL(site.Site, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

    tenant: TenantSQL = Relationship(
        back_populates='sites',
        sa_relationship_kwargs=dict(lazy='selectin'),
    )

    @staticmethod
    def list_of_types(db: Session):
        return [
            (0, 'ALL', getattr(TenantSQL.exists(db, 'ALL'), 'id', None)) # id, name, tenant_id
        ]

    @staticmethod
    def exists(db: Session, name: str|int):
        if isinstance(name, str):
            return db.exec(select(SiteSQL).filter_by(name=name)).first()
        if isinstance(name, int):
            return db.exec(select(SiteSQL).filter_by(id=name)).first()
            
    @classmethod
    def populate_table(cls, db: Session):
        for t in cls.list_of_types(db):
            if isinstance(t[2], int) and not cls.exists(db, t[0]):
                SiteSQL(
                    name=t[1],
                    tenant_id=t[2],
                    id=t[0],
                ).add(db, SiteSQL)

Description

the whole logic works as expected but when executing the add function and committing the db session, suddenly, tenant_id is turned into None (i already checked that in all other passages it dosen't happen so i'm pretty sure the problem is somewhere in the commit function but couldn't find the cause).

i use the same logic on other models (basically the objective is to initialize the db with mappings) and that is the only case in which a fk is turned into None.

if i try to do the same but start from the parent class (aka. site.Site) then use model_validate to cast it to a siteSQL class and assign all the values, everything works as expected.

is to note that the fk is the only one being turned into None

Operating System

Linux

Operating System Details

Ubuntu 22.04.4 LTS

SQLModel Version

0.0.16

Python Version

Python 3.10.12

Additional Context

No response

[giovanni-bellini-argo]

after some more tries i reached the conclusion that the problem is brought by the Relationship function

[joachimhuet]

My question might seem stupid but did you try in debug ? Going step-by-step you might find at which point the fk is set to None if it is.
If you can send a reproducible error I can try to help you a bit more

[giovanni-bellini-argo]

i can't get to find where it all goes wrong even when debugging, it gets too messy and i'm not really familiar with the library.

[giovanni-bellini-argo]

u could start by giving me some directions on how to read the Session object? i couldn't even find the transaction data

[joachimhuet]

First thing I would suggest would be to separate the pure models from the actions.
Meaning you have SiteSQL model, but then you also have a CRUDSiteSQL that contains the functions that hit the database (add, exists ...
Here is an example i found on github (https://github.com/petrgazarov/FastAPI-app/tree/main/fastapi_app), I did not check the whole project but you can see he has a crud folder containing the crud operations as a class for each model.

Give me the content of TenantSQL class. And give me some minimal data I can use to reproduce your problem. I can try to run it on my side and see if I find something.

[giovanni-bellini-argo]

those are all the crud operations i implemented on the class, Site ereditates from CommonFunc and then SiteSQL ereditates from Site (that's to handle id's)

class CommonFunc(SQLModel):
    def get_date(self):
        if hasattr(self, 'date'):
            return self.date
        return None
    
    def add(self, db: Session, object: SQLModel):
        obj = object.model_validate(self)
        db.add(obj)
        db.commit()
        db.refresh(obj) # updates the object with the new entries of the db
        return obj
    
    @staticmethod
    def update(obj, db: Session):
        db.add(obj)
        db.commit()
        db.refresh(obj)
        return obj
    
    @staticmethod
    def remove(obj, db: Session):
        db.delete(obj)
        db.commit()
    
    @staticmethod
    def search(id, db: Session, object):
        obj = db.get(object, id)

        if not obj:
            raise ObjectNotFound()

        return obj

    @staticmethod
    def return_all(db: Session, object):
        return db.exec(select(object)).all()
    
    @staticmethod
    def number_of_items(db: Session, object):
        return db.exec(select(func.count()).select_from(object)).one()

TenantSQL is pretty much the same

class Tenant(CommonFunc):
    name: str

    def list_of_types():
        return [
            (0, 'ALL')
        ]

class TenantSQL(tenant.Tenant, table=True):
    id: Optional[int] = Field(default=None, primary_key=True) 

    sites: List['SiteSQL'] = Relationship(
        back_populates='tenant',
        sa_relationship_kwargs=dict(lazy='selectin', cascade='delete'),
    )
    users: List['UserSQL'] = Relationship(
        back_populates='tenants',
        link_model=UserPermissionSQL,
        sa_relationship_kwargs=dict(lazy='selectin', cascade='delete', overlaps='sites'),
    )

    @staticmethod
    def exists(db: Session, name: str|int):
        if isinstance(name, str):
            return db.exec(select(TenantSQL).filter_by(name=name)).first()
        if isinstance(name, int):
            return db.exec(select(TenantSQL).filter_by(id=name)).first()

    @classmethod
    def populate_table(cls, db: Session):
        for t in cls.list_of_types():
            if not cls.exists(db, t[0]):
                cls(
                    id=t[0],
                    name=t[1]
                ).add(db, TenantSQL)

as for the data what i'm trying to accomplish is to create:

• TenantSQL

name	id
ALL	0

• SiteSQL

name	tenant_id	id
ALL	0	0

[joachimhuet]

Well, it's hard to reproduce your code as is, so here is what I suggest, I created an example project for you, you can find it here
The code using sqlmodel to define your model and stuff is in folder library, and from the notebook.ipynb file you can test it.

Briefly, here is what I did from what you send:

1. Separation of concerns, create models, crud and services packages. instead of having everything defined in models as you did.

• models contains only your models descriptions. I like to have a schema for Table, Create, Update and Read, even if sometimes the schema is exactly the same. Fell free to change it to your style. But I gave you some base code that can be very useful to you.
• crud contains the interface to the database, where you can create, read, update and delete objects from the database. Here the base.py file allows you to define methods that are common to all your models, same as your CommonFunc.
• services contains the services, for now only the service to populate your database. I feel it's a bit strange to put that in your models directly. But if you want you can define in your models the data that should be created on first run instead of defining it directly in the service like I did.

2. The notebook.ipynb file is a simple notebook I used to create your database and populate the data, if you want to know where the session comes from it's there. It creates a database.db file containing the sqlite database with you data.
3. My code contains base code that you can reuse so that you can add the models that are still missing.

Some precisions:

• You have a Optional[int] id, meaning that it will be generated by the server, I tried to replicate that and removed the id from the create schema. If you still want to be able to force an id on creation you can add the id field to the ...Create class.
• From what I saw while testing the code, database generated integer id by default starts with 1, that's why I changed your ids to 1.
• I tried to put some comments on my code to explain some things but if anything isn't clear feel free to ask.
• The crud instance for each model inherit crud base but from the basic operations I already added in crud base you can add model specific functions, like i did as an example with get_by_name on the TenantCRUD.

I hope this will help you. Tell me if anything isn't clear or if you want some more help.

[joachimhuet]

Or you could also put a relationship object and use sqlalchemy event catchers to do an action before inserting or updating. you can also only listens_for insert

class Example(SQLModel, table=True):
    f1: str
    f2: int
    obj_joined: "ModelWithMappings" = Relationship(primaryjoin="foreign(Example.f1)==ModelWithMappings.id")

    @classmethod
    def set_f2(self, obj_joined: ModelWithMappings): ...
    
# before insert and update you force the field to be set
@listens_for(Example, "before_insert")
@listens_for(Example, "before_update")
def set_f2_before_insert_update(mapper, connection, target):
    target.f2 = Example.set_f2(target.obj_joined) if target.obj_joined else None

[giovanni-bellini-argo]

just found out the existence of event handling in sqlalchemy 😮

i like that, i gotta make some transformations on f1 to map it to ModelWithMappings, but i think i know how to fix it; will try a few tings and come up with something.

thanks dude😎

[giovanni-bellini-argo]

i noticed now in your example that u use sqlalchemy session instead of sqlmodel session obj in cruds, is there a reason for that?

[joachimhuet]

Woops, nope you know I created that example quite fast to answer someones question. Probably auto-imported the wrong one ~~
I fixed it !

[giovanni-bellini-argo]

😆 perfect, thanks