I am creating a FastAPI application on top of an existing database (AzureSQL), the SQL table ID column (primary key) is populated by:
CONVERT([varchar](36),newid())
My SQLModel class definition for the table is:
from sqlmodel import Field, SQLModel
from uuid import UUID
class MyItem(SQLModel, table=True):
id: UUID = Field(primary_key=True, index=True)
My issue comes when I query the table for a specific id;
@router.get("/{item_id}", response_model=MyItem)
async def get_item(*, item_id: str, session: Session = Depends(get_session)):
item = session.get(MyItem, item_id)
if not item:
raise HTTPException(status_code=404, detail="Item not found")
return item
If there is a record in the database with ID of AAAAAAAA-1111-2222-3333-444444444444 (Note: In the SQL table, the UUIDs all appear uppercase), if I hit this URL endpoint with the UUID (upper or lowercase), it will 404; the session.get(MyItem, item_id) is returning None.
I've tried typing the endpoint with item_id: UUID, converting the item_id to uppercase and a few other janky solutions; but nothing seems to work.
What does work is if I change the SQLModel id type to a str, then the query returns a match, i.e.:
class MyItem(SQLModel):
id: str = Field(primary_key=True, index=True)
However, I don't really like it.
Is there a way to use the UUID type and for it to work nicely with SQLModel/SQLAlchemy?
