[Question] Why are pydantic validation results ignored for models with `table=True`? Where are the benefits?

[Cielquan]

First Check

EDIT: See #406 (comment)

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue 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 pydantic import BaseModel, ValidationError
from sqlmodel import Field, SQLModel


class EnumValues(str, enum.Enum):

    FOO = "foo"
    BAR = "bar"


class TableModel(BaseModel):
    value: EnumValues


print(TableModel(value="bar"))  # value=<EnumValues.BAR: 'bar'>
try:
    print(TableModel(value="baz"))
except ValidationError as e:
    print(e)
    # 1 validation error for TableModel
    # value
    #   value is not a valid enumeration member; permitted: 'foo', 'bar' (type=type_error.enum; enum_values=[<EnumValues.FOO: 'foo'>, <EnumValues.BAR: 'bar'>])


class TableBase(SQLModel):
    value: EnumValues


print(TableBase(value="bar"))  # value=<EnumValues.BAR: 'bar'>
try:
    print(TableBase(value="baz"))
except ValidationError as e:
    print(e)
    # 1 validation error for TableBase
    # value
    #   value is not a valid enumeration member; permitted: 'foo', 'bar' (type=type_error.enum; enum_values=[<EnumValues.FOO: 'foo'>, <EnumValues.BAR: 'bar'>])


class Table(TableBase, table=True):
    id: int | None = Field(default=None, nullable=False, primary_key=True)  # noqa: VNE003


print(Table(value="bar"))  # value=<EnumValues.BAR: 'bar'> id=None
try:
    print(Table(value="baz"))  # id=None
except ValidationError as e:
    print(e)  # NEVER REACHED

Description

• make a instance of the TableModel which is a pure pydantic model with a valid string in the enum -> success (as it should be)
• make a instance of the TableModel which is a pure pydantic model with an invalid string in the enum -> error (as it should be)
• make a instance of the TableBase which is not table=True with a valid string in the enum -> success (as it should be)
• make a instance of the TableBase which is not table=True with an invalid string in the enum -> error (as it should be)
• make a instance of the Table which is table=True with a valid string in the enum -> success (as it should be)
• make a instance of the Table which is table=True with an invalid string in the enum -> success (should be error, but creates an instance which is totally missing the non-optional and non-default value field)

Operating System

Linux

Operating System Details

Ubuntu

SQLModel Version

0.0.6

Python Version

3.10.4

Additional Context

No response

[Cielquan]

Even if a add a custom validator (below) to the classes the outcome does not change. Only the error message from pydantic changes to Invalid value (type=value_error).

    @validator("value", pre=True)
    @classmethod
    def valid_value(cls, value: t.Any) -> EnumValues:  # noqa: ANN401
        if isinstance(value, EnumValues):
            return value

        if isinstance(value, str):
            if value.upper() in (e.name for e in EnumValues):
                return EnumValues[value.upper()]

        raise ValueError("Invalid value")

[Cielquan]

The issue is much worse as it seems as no validation is running on creation of table=True models at all. Or did I overlook that this is state somewhere?!

class Table2(SQLModel, table=True):
    id: int | None = Field(default=None, nullable=False, primary_key=True)  # noqa: VNE003
    value: str


try:
    print(Table2())  # id=None
except ValidationError as e:
    print(e)  # NEVER REACHED -- should be: field required (type=value_error.missing)

[Cielquan]

The validation is ignored when the model is a table: https://github.com/tiangolo/sqlmodel/blob/main/sqlmodel/main.py#L499-L504

But I did not see it being mentioned in the docs.

But why are errors ignored? I cannot see any benefit for this rather only downsides, because the check runs anyways. So why hide the result?

[Cielquan]

After some thinking I came to the conclusion that maybe the validation error raising for table=True models is disabled to prevent errors on DB querying?

Thinking experiment:

When I have an SQLModel with table=True with a field/column which has an String-Enum as type and thus only allows e.g. "Foo" and "Bar" as values. But the database of choice does not support the enum type and so the actual DB type would be e.g. a VARCHAR.
I then manually inject a DB entry and enter "FooBar" as value for the prior mentioned column.
Then I query this entry via the SQLModel. I would assume that if I don't get any error I have a valid DB entry in the SQLModle instance, but I have not.

This also applies the other way around. If I for example don't use a "Create" model for prior data-validation in a FastAPI route and simply create a table=True model instance with invalid data I could write those invalid values into the database while thinking everything is fine.

Proposal

I would propose to add a config switch that changes the validation silencing behavior.

I would further propose to state the current behavior in the docs.

[JonasR]

Since this is explicitly worded as a question, maybe you are aware but if not see issue (and discussion) #52

[Cielquan]

Thanks for sharing. Actually I was not aware of the linked issue. As you can see in the feed above the issue was initially not a question, but more like a bug report.

I guess this is now a duplicate. Gonna close it.