Why is my foreign key always None?

[Severon96]

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

import enum
from datetime import datetime
from uuid import UUID

from sqlmodel import SQLModel, Field, Relationship

class HardwareCategory(enum.Enum):
    CONTROLLER = 'controller'
    LIGHT = 'light'
    CABLE_XLR = 'cable_xlr'
    PLUG_COLD_APPLIANCE = 'plug_cold_appliance'
    LAPTOP_STAND = 'laptop_stand'
    OTHER = 'other'


class User(SQLModel, table=True):
    __tablename__ = "users"

    id: UUID | None = Field(default=None, primary_key=True)
    username: str
    first_name: str | None = None
    last_name: str | None = None
    created_at: datetime | None = None
    updated_at: datetime | None = None

    hardware: list["Hardware"] = Relationship(
        back_populates="owner"
    )


class Hardware(SQLModel, table=True):
    __tablename__ = "hardware"

    id: UUID | None = Field(default=None, primary_key=True)
    name: str
    serial: str
    image: str | None = None
    category: HardwareCategory
    owner_id: UUID = Field(foreign_key="users.id")
    created_at: datetime | None = None
    updated_at: datetime | None = None

    owner: User = Relationship(back_populates="hardware")



def create_hardware(hardware: Hardware) -> Hardware:
    now = datetime.now()

    hardware.id = uuid.uuid4()
    hardware.created_at = now
    hardware.updated_at = now

    validated_model = hardware.model_validate(hardware)

    with util.get_db_session() as session:
        print('validated hardware model', validated_model)
        session.add(validated_model)
        session.commit()
        session.refresh(validated_model)

    return validated_model

def create_user(user: User) -> User:
    now = datetime.now()

    user.id = uuid.uuid4()
    user.created_at = now
    user.updated_at = now

    validated_model = user.model_validate(user)

    session = util.get_db_session()

    session.add(validated_model)
    session.commit()
    session.refresh(validated_model)

    return validated_model

user = create_user(User(
        id=uuid.uuid4(),
        username='testuser',
        first_name='Test',
        last_name='User',
        created_at=datetime.now(),
        updated_at=datetime.now()
    ))


hardware = create_hardware(Hardware(
        id=uuid.uuid4(),
        name='test hardware',
        serial=f'hdw-{uuid.uuid4()}',
        image=None,
        category=HardwareCategory.CONTROLLER,
        owner=user,
        owner_id=user.id,
        created_at=datetime.now(),
        updated_at=datetime.now()
    ))

Description

Hello there,

i'm trying to create a relationship by using SQLModel and setting a UUID as foreign in the hardware model.
The field i'm talking about is the owner_id on the Hardware Model.

The value is sent as JSON to the endpoint and i've validated on multiple places in the code if owner_id is set. It is.
But every time i'm trying to write the data to the database i'm getting the following error:
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: hardware.owner_id
[SQL: INSERT INTO hardware (id, name, serial, image, category, owner_id, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)]
[parameters: ('0ac8aa2e6afa4d53a13c59064ac73090', 'test hardware', 'hdw-a0ed4b5b-f845-4a29-aa1b-e911313b11ca', None, 'CONTROLLER', None, '2024-07-21 20:56:07.016448', '2024-07-21 20:56:07.016448')]

I've followed the tutorial in the SQLModel Docs but I can't find the issue. Maybe someone sees something i'm overseeing but I don't know what else to do. As far as I can tell my model and my logic is correct.

I did some additional debugging and found, that in SQLAlchemy, in the persistence.py, there is a function called save_obj. Which is collecting the states_to_insert. When appending this array it's adding the state of the hardware, which at this point still has the owner_id. After adding it, it loses it. Maybe this helps, I don't know.

Thanks for your help in advance. If you need anymore information, feel free to ask.

Greetings from Germany

Operating System

macOS

Operating System Details

macOS Sonoma 14.3

SQLModel Version

0.0.19

Python Version

3.12.0

Additional Context

No response

[mbrulatout]

you most definitely don't want to have an optional field (xx | None) as a primary_key

[Severon96]

That's how SQLModel shows it in it's docs

[Terseus]

@mbrulatout AFAIK with SQLModel you need to have the primary key as optional, because in SQLAlchemy when you create a new row it'll not have primary key until it's saved and refreshed from the session.

If you know a workaround for this please share it, because it's one of the things I hate the most in SQLModel.

[joy13975]

Facing the same issue, even with PK defined as NOT NULL.

Edit: found that if you manually set the relationship field by doing a query before sesison.add() and session.commit(), the problem goes away