Why does a SQLModel class with `table=True` not validate data ?

deajan commented 2 years ago

First Check

[X] I added a very descriptive title to this issue.
[X] I used the GitHub search to find a similar issue and didn't find it.
[X] I searched the SQLModel documentation, with the integrated search.
[X] I already searched in Google "How to X in SQLModel" and didn't find any information.
[X] I already read and followed all the tutorial in the docs and didn't find an answer.
[X] I already checked if it is not related to SQLModel but to Pydantic.
[X] I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

[X] I commit to help with one of those options 👆

Example Code

from pydantic import BaseModel
from sqlmodel import SQLModel, Field

class BaseModelTest(BaseModel):
    id: int
    some_bool: bool
    desc: str

class SQLModelTest(SQLModel, table=True):
    id: int = Field(default=None, primary_key=True)
    some_bool: bool
    desc: str

# This will work as expected
sqlmodel_instance = SQLModelTest(id=3, some_bool=False, desc="This will work")
print(sqlmodel_instance)
# This will work as expected
pydantic_instance = BaseModelTest(id=3, some_bool=False, desc="This will work")
print(pydantic_instance)

# This will work but should not
sqlmodel_instance = SQLModelTest(id=3, some_bool="blob", desc=False)
print(sqlmodel_instance)
# This will not work as expected
pydantic_instance = BaseModelTest(id=3, some_bool="blob", desc=False)
print(pydantic_instance)

Description

When creating a SQLModel class instance, I thought that I'd have all the advantages of pydantic and SQLAlchemy classes toghether. I was quite surprised when I realized that SQLModel classes created with table=True do not validate data as a pydantic class would do (see my example code). Having read about all SQLModel documentation, I didn't find anywhere this is stated. Especially in https://sqlmodel.tiangolo.com/tutorial/fastapi/multiple-models/ it would make sense to explain that a table=True won't do data validation as a pydantic class would do.

Maybe I did not understand the purpose of SQLModel enough, but this is quite disturbing for me. I think it would be really nice to update the documentation regarding this particular point.

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

Python 3.10.7 x64

Additional Context

PS: Sorry, I'm comming here from Tortoise-ORM, and it's my first time using SQLModel, so I cannot guarantee that I will be quite helpful resolving issues here yet ;) I'll do my best.

phi-friday commented 2 years ago

To guess, when used as a table object, there is no verification process because it is used to map data that has already been verified in database. (I think because SQLModel use DeclarativeMeta when table=True. link) In particular, when calling many objects from database, there is no unnecessary verification process, so there is a performance advantage. (I haven't checked yet.) Of course, if the verification process is essential, SQLModel.validate method can be used.

I've asked the same question before, but I'm just using SQLModel.validate method for now. I want to know why, too.

deajan commented 2 years ago

I do understand that too, reads from database don't need validation. But writes do. Nevertheless it's not really clear in the documentation. And since SQLModel is supposed to reduce code duplication, having a class for ORM and a class for Pydantic validation.... doesn't help deduplicate.

phi-friday commented 2 years ago

@deajan I don't know the answer to why, but I found a simple way to solve it. link

5p4k commented 11 months ago

I think this is answered here: https://github.com/tiangolo/sqlmodel/issues/52#issuecomment-1311987732

For future reference, I found this while looking for why model_validate_json did not properly validate for a SQLModel with table=True.

realchandan commented 4 months ago

This feature/bug should be fully documented, as I (and many others) expect the SQL model to validate data. Only after doing a lot of digging to find out why one of my field_validator wasn't working did I come across this GitHub issue.

mg3146 commented 1 month ago

I agree - this just totally threw me off. I expected it to be fully validating my table data. why bother inheriting from BaseModel at all if not.

This feature/bug should be fully documented, as I (and many others) expect the SQL model to validate data. Only after doing a lot of digging to find out why one of my field_validator wasn't working did I come across this GitHub issue.

RamiroFartoBello commented 1 week ago

completely annoying bug. I've lost half a day trying to understand what was happening. I'll reconsider if SQLModel is actually the way to go. Thank you, guys!

iloveitaly commented 5 days ago

This was also very surprising to me. Looks like this PR may fix the issue, although it's unclear if it's the right approach.

fastapi / sqlmodel