Inconsistent UUID parsing behaviors between SQLModel (with table=True) and plain SQLModel/Pydantic BaseModel

[tokikanno]

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

from sqlmodel import SQLModel, Field
from pydantic import BaseModel

from uuid import UUID

class STableModel(SQLModel, table=True):
    id: UUID = Field(primary_key=True)

class SModel(SQLModel):
    id: UUID

class PModel(BaseModel):
    id: UUID

uuid: str = "8fd679f6-db1a-4181-b8f4-d4671e4e600b"

s = STableModel(id=uuid)
print("STableModel", s)

s = SModel(id=uuid)
print("SModel", s)

p = PModel(id=uuid)
print("PModel", p)

Description

The execution result of above codes will be

STableModel id='8fd679f6-db1a-4181-b8f4-d4671e4e600b'
SModel id=UUID('8fd679f6-db1a-4181-b8f4-d4671e4e600b')
PModel id=UUID('8fd679f6-db1a-4181-b8f4-d4671e4e600b')

The STableModel will parse input id value as plain str, but other models (plain SQLModel/Pydantic BaseModel) will parse id value as an UUID object.

This inconsistent UUID parsing behavior will also cause insert/update operation failed with below error message, because the SQLModel(with table=True) won't convert plain string into UUID object on creation.

sqlalchemy.exc.InvalidRequestError: Can't match sentinel values in result set to parameter sets; key '8fd679f6-db1a-4181-b8f4-d4671e4e600b' was not found. There may be a mismatch between the datatype passed to the DBAPI driver vs. that which it returns in a result row.  Ensure the given Python value matches the expected result type *exactly*, taking care to not rely upon implicit conversions which may occur such as when using strings in place of UUID or integer values, etc.

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.18

Python Version

Python 3.11.6 (main, Nov 29 2023, 03:49:24) [GCC 12.2.0] on linux

Additional Context

No response

[joachimhuet]

Hey, what database technology do you use ? Can you give a reproducible example so that I can run your code getting the sqlalchemy exception ?
And did you check this thread on sqlalchemy, it looks pretty much the same as your error: sqlalchemy/sqlalchemy#11063

[tokikanno]

The DB system was postgres, but I think there's nothing doing with the postgresql or sqlalchemy.
Because if I use UUID type value for SQLModel creation and SQLAlachemy insertion or update operation, it will just success.

If SQLModel(with table=True) can parse UUID string into UUID object(just like pydantic/plain SQLModel do), then there be nothing need to be fixed with postgres or sqlalchemy side.

Also I wonder if it's caused by the following section of code of something like it.
SQLModel checked if this model is marked as table (table=True) then using different init function

    def sqlmodel_init(*, self: "SQLModel", data: Dict[str, Any]) -> None:
        old_dict = self.__dict__.copy()
        if not is_table_model_class(self.__class__):
            self.__pydantic_validator__.validate_python(
                data,
                self_instance=self,
            )
        else:
            sqlmodel_table_construct(
                self_instance=self,
                values=data,
            )
        object.__setattr__(
            self,
            "__dict__",
            {**old_dict, **self.__dict__},
        )

[joachimhuet]

Ohh, I get what you are saying. Yes validation is not active for table=True models. Check that thread: #52

But what you can do is validate yourself like that:

from sqlmodel import SQLModel, Field
from pydantic import BaseModel

from uuid import UUID

class STableModel(SQLModel, table=True):
    id: UUID = Field(primary_key=True)

uuid: str = "8fd679f6-db1a-4181-b8f4-d4671e4e600b"

s = STableModel.model_validate(STableModel(id=uuid))
print("STableModel", s)

Or use the sqlalchemy validates annotation to be sure the string passed is a valid UUID before instantiating.

from sqlmodel import SQLModel, Field
from sqlalchemy.orm import validates
from uuid import UUID

class STableModel(SQLModel, table=True):
    id: UUID = Field(primary_key=True)

    @validates("id")
    def validate_id(self, key, id):
        if isinstance(id, str):
            return UUID(id)
        return id

And then what you should know is that the UUID type in several DB system is not represented as a 36 characters string but as the hex representation: uuid.hex #8fd679f6db1a4181b8f4d4671e4e600b

But I tested your use case as is in a mssql database (just added a name field) and it works fine.

# model file
from sqlmodel import SQLModel, Field
from uuid import UUID

class STableModel(SQLModel, table=True):
    id: UUID = Field(primary_key=True)
    name: str

# notebook
from sqlmodel import create_engine, Session, select

engine = create_engine(f"mssql+pyodbc://{conn_str}", echo=True)
SQLModel.metadata.create_all(engine)

uuid: str = "8fd679f6-db1a-4181-b8f4-d4671e4e600b"

# insert
with Session(engine) as session:
    session.add(STableModel(id=uuid, name="test"))
    session.commit()
"""
2024-05-08 20:32:07,985 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-05-08 20:32:07,988 INFO sqlalchemy.engine.Engine INSERT INTO stablemodel (id, name) VALUES (?, ?)
2024-05-08 20:32:07,988 INFO sqlalchemy.engine.Engine [generated in 0.00106s] ('8fd679f6db1a4181b8f4d4671e4e600b', 'test')
2024-05-08 20:32:08,005 INFO sqlalchemy.engine.Engine COMMIT
"""

# read
with Session(engine) as session:
    command = select(STableModel).where(STableModel.id == uuid)
    result = session.exec(command)
    print(result.all())
"""
2024-05-08 20:32:09,755 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-05-08 20:32:09,758 INFO sqlalchemy.engine.Engine SELECT stablemodel.id, stablemodel.name 
FROM stablemodel 
WHERE stablemodel.id = ?
2024-05-08 20:32:09,758 INFO sqlalchemy.engine.Engine [generated in 0.00107s] ('8fd679f6db1a4181b8f4d4671e4e600b',)
[STableModel(name='test', id=UUID('8fd679f6-db1a-4181-b8f4-d4671e4e600b'))]
2024-05-08 20:32:09,777 INFO sqlalchemy.engine.Engine ROLLBACK
"""

[tokikanno]

Thx.

But it's still very inconvenient if SQLModel can't be used as a plain pydantic model. Not only for the validation part, but also some data transformation parts.

Anyway, I think issue #52 contains everything I need to know about this limitation.