SQLModel doesn't raise ValidationError

[elben10]

First Check

• 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

from typing import Optional

from pydantic import BaseModel
from sqlmodel import Field, SQLModel

class User1(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True)
    name: str

class User2(BaseModel):
    name: str

User1()
User2()

Description

• Create SQLModel
• Create equivalent pydantic model
• Create instance of SQLModel without providing required arguments
• Create instance of pydantic model without providing required arguments

The pydantic model raises an ValidationError whereas the SQLModel doesn't raise a ValidationError even though an required argument is not provided. I would expect a ValidationError if not all required arguments is provided.

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

Python 3.8.5

Additional Context

No response

[obassett]

I was just going through this aswell!

If the model as table=True then validations are disabled, and it assume SQLalchemy will pick up any issues. It then basically drops those values.

I haven't figured out the best way to handle this.

[sathoune]

I just checked and I experience the same behaviour with Ubuntu 20.04.03 and python 3.10

[maku2903]

May it have connection with pydantic's class config: validate_assignment = True? Still discovering sqlmodel and i dont know how to setup "pydantic things" from classes inheriting SQLModel so cant check hypothesis empirically.

[obassett]

Ok so I have been looking at work-arounds for this - as I don't want to double up on models just to do validation.

If the model is defined with table=True then when you create an instance it doesn't raise the validations. This is probably fine if you are reading from a database, but if you are writing to one, then it drops the fields that fail validation, which is challenging, and could lead to some silently dropping data errors.

From the init of the SQL Model Class:

        # Only raise errors if not a SQLModel model
        if (
            not getattr(__pydantic_self__.__config__, "table", False)
            and validation_error
        ):
            raise validation_error

You can manually validate when importing the data using the 'Model.validate()` class method for assignment, but for that we need to pass in a dict with all the values, but at least at this point in will validate the data.

from typing import Optional
from sqlmodel import SQLModel, Field, Column, VARCHAR
from pydantic import EmailStr


class UserExample(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True)
    username: EmailStr = Field(sa_column=Column("username", VARCHAR, unique=True))
    full_name: Optional[str] = None
    disabled: Optional[bool] = None


# Create and instance
test_user1 = UserExample(
    username="jdoe",  # oops this should be an email address
    full_name="John Doe",
    disabled=False,
)
print("Test User 1:", test_user1)

Which gives us

Test User 1: id=None full_name='John Doe' disabled=False

This is bad, since we have silently dropped our required email address field - which also needs to be unique. If I write this to the database it will fail, which is fine but it would be nice to catch this at the assignment point rather than trying to write it to the DB. Also if this was an optional field, it would silently drop the data, and we wouldn't know until we tried to read it and it wasn't there ( it wouldn't break anything since it is optional, but it would be data loss)

so now if I do it this way:

from typing import Optional
from sqlmodel import SQLModel, Field, Column, VARCHAR
from pydantic import EmailStr


class UserExample(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True)
    username: EmailStr = Field(sa_column=Column("username", VARCHAR, unique=True))
    full_name: Optional[str] = None
    disabled: Optional[bool] = None

# This time create the instance and pass in a dict of the values by call .validate
test_user2 = UserExample.validate(
    {"username": "jdoe", "full_name": "John Doe", "disabled": False}
)
print("Test User 2:", test_user2)

I get this:

Traceback (most recent call last):
  File "/workspaces/thetattooedtechnologist.com/temp/example_validation.py", line 22, in <module>
    test_user2 = UserExample.validate(
  File "/workspaces/thetattooedtechnologist.com/.venv/lib/python3.9/site-packages/sqlmodel/main.py", line 580, in validate
    raise validation_error
pydantic.error_wrappers.ValidationError: 1 validation error for UserExample
username
  value is not a valid email address (type=value_error.email)

So this works and gives me my validation error.

So it can be used as a workaround for those times where you are getting data ready to put in the DB, though it does mean that data needs to be in a dict format - but most likely that wont be that much of a problem.

Hopefully this helps as a workaround.

[SunsetOrange]

My god @obassett, I've been pulling my hair out for hours trying to figure out why my validators were running, but doing nothing.

table=True

Much thanks

[lovetoburnswhen]

Ok so I have been looking at work-arounds for this - as I don't want to double up on models just to do validation.

If the model is defined with table=True then when you create an instance it doesn't raise the validations. This is probably fine if you are reading from a database, but if you are writing to one, then it drops the fields that fail validation, which is challenging, and could lead to some silently dropping data errors.

From the init of the SQL Model Class:

        # Only raise errors if not a SQLModel model
        if (
            not getattr(__pydantic_self__.__config__, "table", False)
            and validation_error
        ):
            raise validation_error

You can manually validate when importing the data using the 'Model.validate()` class method for assignment, but for that we need to pass in a dict with all the values, but at least at this point in will validate the data.

from typing import Optional
from sqlmodel import SQLModel, Field, Column, VARCHAR
from pydantic import EmailStr


class UserExample(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True)
    username: EmailStr = Field(sa_column=Column("username", VARCHAR, unique=True))
    full_name: Optional[str] = None
    disabled: Optional[bool] = None


# Create and instance
test_user1 = UserExample(
    username="jdoe",  # oops this should be an email address
    full_name="John Doe",
    disabled=False,
)
print("Test User 1:", test_user1)

Which gives us

Test User 1: id=None full_name='John Doe' disabled=False

This is bad, since we have silently dropped our required email address field - which also needs to be unique. If I write this to the database it will fail, which is fine but it would be nice to catch this at the assignment point rather than trying to write it to the DB. Also if this was an optional field, it would silently drop the data, and we wouldn't know until we tried to read it and it wasn't there ( it wouldn't break anything since it is optional, but it would be data loss)

so now if I do it this way:

from typing import Optional
from sqlmodel import SQLModel, Field, Column, VARCHAR
from pydantic import EmailStr


class UserExample(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True)
    username: EmailStr = Field(sa_column=Column("username", VARCHAR, unique=True))
    full_name: Optional[str] = None
    disabled: Optional[bool] = None

# This time create the instance and pass in a dict of the values by call .validate
test_user2 = UserExample.validate(
    {"username": "jdoe", "full_name": "John Doe", "disabled": False}
)
print("Test User 2:", test_user2)

I get this:

Traceback (most recent call last):
  File "/workspaces/thetattooedtechnologist.com/temp/example_validation.py", line 22, in <module>
    test_user2 = UserExample.validate(
  File "/workspaces/thetattooedtechnologist.com/.venv/lib/python3.9/site-packages/sqlmodel/main.py", line 580, in validate
    raise validation_error
pydantic.error_wrappers.ValidationError: 1 validation error for UserExample
username
  value is not a valid email address (type=value_error.email)

So this works and gives me my validation error.

So it can be used as a workaround for those times where you are getting data ready to put in the DB, though it does mean that data needs to be in a dict format - but most likely that wont be that much of a problem.

Hopefully this helps as a workaround.

One caveat of using .validate() is for some reason it breaks passing related objects directly into the constructor as described here.

The team_id column will be None, and team doesn't get returned at all from .validate()

[alexferrari88]

In the documentation, it recommends to create a base model and inherit from it for the database model (table=True).
Please check here:

• https://sqlmodel.tiangolo.com/tutorial/fastapi/multiple-models/
• https://sqlmodel.tiangolo.com/tutorial/fastapi/relationships/

[memark]

This is really sad. A prime reason to use SQLModel is to be able to continue to use your Pydantic models, just with some added SQL goodness. But if all validation is ignored, the models are hardly useful as Pydantic models anymore.

Working around this limitation either by doubling the number of classes or passing all data as dictionaries is not particularly appealing.

Really hoping for a simpler way to enable the built-in validation. Recently chipped in a small sponsorship hoping this gets some well-deserved attention by @tiangolo!

😞

[andremmori]

Any updates on this issue? I found a workaround that raises the validation error while also having table=True:

from sqlmodel import SQLModel, Field


class BaseSQLModel(SQLModel):
    def __init__(self, **kwargs):
        self.__config__.table = False
        super().__init__(**kwargs)
        self.__config__.table = True

    class Config:
        validate_assignment = True



class MyTable1(BaseSQLModel, table=True):
    a: int = Field(primary_key=True)
    b: int

class MyTable2(BaseSQLModel, table=True):
    a: int = Field(primary_key=True)
    b: float


t1 = MyTable1(a=1, b=2)  # ok
t2 = MyTable2(b="text")   # Raises ValidationError
# pydantic.error_wrappers.ValidationError: 2 validation errors for MyTable2
# a
#  field required (type=value_error.missing)
# b
#  value is not a valid float (type=type_error.float)

By creating a base class and defining the __init__ method, we can set table to false and call the super __init__ method, validating the args passed. After that, we can set table back to true.

With this approach, creating multiple models for different classes to inherit or calling the .validate() method with a dict is not necessary.

Edit: Added Config class to BaseSQLModel with the validate_assignment option set to true so it validates fields when updated.

[memark]

@andremmori This works excellently and solves this long standing problem. Thank you!

[will-afs]

@andremmori This works excellently and solves this long standing problem. Thank you!

I personnaly don't feel this way. It's more of a hack, and actually doesn't even enable to validate fields on update (not at initialization of the model).

And defining a BaseModel, from which the SQLModel should inherit, seems a bit over-complexifying things to me...

I'd be glad if there was an actual clean and easy way to validate a SQLModel, the same way it is proposed for a BaseModel.

[phi-friday]

I don't think it's a good solution, but it seems to work for now.

from typing import Tuple, Dict, Any, Union, Type
from traceback import format_exception_only
from sqlmodel.main import (
    SQLModelMetaclass,
    __dataclass_transform__,
    Field,
    FieldInfo,
    default_registry,
)
from sqlmodel import SQLModel


@__dataclass_transform__(kw_only_default=True, field_descriptors=(Field, FieldInfo))
class ValidSQLModelMetaclass(SQLModelMetaclass):
    def __new__(
        cls,
        name: str,
        bases: Tuple[Type[Any], ...],
        class_dict: Dict[str, Any],
        **kwargs: Any,
    ) -> Any:
        valid: bool = kwargs.pop("valid", False)
        new = super().__new__(cls, name, bases, class_dict, **kwargs)
        if valid and kwargs.pop("table", False):
            setattr(
                new.__config__,
                "_pydantic_model",
                SQLModelMetaclass.__new__(cls, name, bases, class_dict, **kwargs),
            )
        else:
            setattr(new.__config__, "_pydantic_model", new)
        setattr(new.__config__, "_valid_sqlmodel", valid)
        return new


class ValidSQLModel(
    SQLModel, metaclass=ValidSQLModelMetaclass, registry=default_registry
):
    def __new__(cls, *args: Any, **kwargs: Any) -> Any:
        new = super().__new__(cls, *args, **kwargs)
        if getattr(new.__config__, "_valid_sqlmodel", False) and getattr(
            new.__config__, "table", False
        ):
            getattr(new.__config__, "_pydantic_model").validate(kwargs)
        return new


class TestBase(ValidSQLModel):
    id: Union[int, None] = Field(default=None, primary_key=True)
    a: int = Field(gt=100)
    b: float = Field(gt=-1, lt=1)
    c: str = Field(min_length=1, max_length=10)


class InvalidateTest(TestBase, table=True):
    ...


class ValidateTest(TestBase, table=True, valid=True):
    ...


def test(target: Type[TestBase], *, a: int, b: float, c: str, end: str):
    print(f"{target=}", end=":: ")
    try:
        print(target(a=a, b=b, c=c), end="\n" + end)
    except ValueError as exc:
        print("".join(format_exception_only(type(exc), exc)), end=end + "\n")


for value in [
    {"a": 1, "b": 1, "c": ""},
    {"a": 1, "b": 0.5, "c": ""},
    {"a": 1, "b": 0.5, "c": "1"},
    {"a": 101, "b": 0.5, "c": "1"},
    {"a": 101, "b": 1, "c": "100_000_000"},
]:
    print(f"{value=}")
    test(InvalidateTest, **value, end="")
    test(ValidateTest, **value, end="=" * 50)

"""
value={'a': 1, 'b': 1, 'c': ''}
target=<class '__main__.InvalidateTest'>:: id=None
target=<class '__main__.ValidateTest'>:: pydantic.error_wrappers.ValidationError: 3 validation errors for ValidateTest
a
  ensure this value is greater than 100 (type=value_error.number.not_gt; limit_value=100)
b
  ensure this value is less than 1 (type=value_error.number.not_lt; limit_value=1)
c
  ensure this value has at least 1 characters (type=value_error.any_str.min_length; limit_value=1)
==================================================
value={'a': 1, 'b': 0.5, 'c': ''}
target=<class '__main__.InvalidateTest'>:: id=None b=0.5
target=<class '__main__.ValidateTest'>:: pydantic.error_wrappers.ValidationError: 2 validation errors for ValidateTest
a
  ensure this value is greater than 100 (type=value_error.number.not_gt; limit_value=100)
c
  ensure this value has at least 1 characters (type=value_error.any_str.min_length; limit_value=1)
==================================================
value={'a': 1, 'b': 0.5, 'c': '1'}
target=<class '__main__.InvalidateTest'>:: id=None b=0.5 c='1'
target=<class '__main__.ValidateTest'>:: pydantic.error_wrappers.ValidationError: 1 validation error for ValidateTest
a
  ensure this value is greater than 100 (type=value_error.number.not_gt; limit_value=100)
==================================================
value={'a': 101, 'b': 0.5, 'c': '1'}
target=<class '__main__.InvalidateTest'>:: id=None a=101 b=0.5 c='1'
target=<class '__main__.ValidateTest'>:: id=None a=101 b=0.5 c='1'
==================================================value={'a': 101, 'b': 1, 'c': '100_000_000'}
target=<class '__main__.InvalidateTest'>:: id=None a=101
target=<class '__main__.ValidateTest'>:: pydantic.error_wrappers.ValidationError: 2 validation errors for ValidateTest
b
  ensure this value is less than 1 (type=value_error.number.not_lt; limit_value=1)
c
  ensure this value has at most 10 characters (type=value_error.any_str.max_length; limit_value=10)
==================================================
"""

[andremmori]

@andremmori This works excellently and solves this long standing problem. Thank you!

I personnaly don't feel this way. It's more of a hack, and actually doesn't even enable to validate fields on update (not at initialization of the model).

And defining a BaseModel, from which the SQLModel should inherit, seems a bit over-complexifying things to me...

I'd be glad if there was an actual clean and easy way to validate a SQLModel, the same way it is proposed for a BaseModel.

My previous comment is just a workaround while this issue isn't fixed on SQLModel's side.

Thanks for pointing out that it doesn't validate fields on updates; I edited my last comment with a fix for this. It is (strangely) Pydantic's BaseModel default behavior not to validate assignments. To do so, it requires a Config class on the model with the validate_assignment attribute set to true (as seen here). Creating a 'BaseModel' with this Config class (which also contains some other useful options) would remove the need to define it in every model.

Unfortunately, we still have to wait for this to be addressed in a future release to have a proper solution. PR #227 was already created to fix this but hasn't gotten the attention it deserves.

[oyvinev]

I feel that the proposed solution by @andremmori is quite hackish. I don't know SQLModel well enough (yet) to feel confident about setting table=False when initializing it, when I in reality want table=True.

I find this workaround safer:

from sqlmodel import SQLModel
from pydantic import validate_model

class SQLValidationModel(SQLModel):
    def __init__(self, **data) -> None:
        super().__init__(**data)
        _, _, validation_error = validate_model(self.__class__, data)
        if validation_error:
            raise validation_error

[tiangolo]

Hey all! Please read the docs: https://sqlmodel.tiangolo.com/tutorial/fastapi/multiple-models/

The model with table=True can't do validation on instantiation, because SQLAlchemy needs to be able so assign values after instantiation. A good example is relationships. It can't validate relationships that you haven't created, and if you define the relationship in both sides, you need to be able to create one of the sides without passing the relationship on instantiation.

If you need to validate data, declare a base model with table=False (the default) and then inherit from that model in one with table=True`.

Validate the data with the data model and copy the data to the table model after validation. And using inheritance you avoid duplicating code.

[elben10]

@tiangolo thanks for clarifying. Closing the issue now.

[tiangolo]

Thanks for reporting back and closing the issue 👍

[deajan]

@tiangolo Thank you for clarifying the validation process in https://sqlmodel.tiangolo.com/tutorial/fastapi/multiple-models/
As a FastAPI user since 0.5.x days, I recently discovered SQLModel after trying various solutions, with the promise of not having duplicated code between Schemas and Models.
While SQLModel is fantastic, I didn't find it straightforward that table=True models don't do validations, even if it's obvious once you think about it. Perhaps adding a quick note at features page in https://sqlmodel.tiangolo.com/#sqlalchemy-and-pydantic would make things more clear for newcommers like me ;)

[the-citto]

thank you @andremmori , using your workaround as it seems the best to me to achieve table declaration and validation at the same time, which is why I'm using SQLModel in the first place

I found myself removing

    class Config:
        validate_assignment = True

as it was validating twice
noticed while performing field manipulation with pre=True

[volfpeter]

If you need to validate data, declare a base model with table=False (the default) and then inherit from that model in one with table=True.

Validate the data with the data model and copy the data to the table model after validation. And using inheritance you avoid duplicating code.

@tiangolo That approach is fine (and often necessary) from a data creation point of view, but that's only one side of the coin. Often you simply need to do some transformation/validation on the data you get from the database (e.g. when dealing with potentially incorrectly formatted, legacy data, or turning a naive datetime to UTC, etc.) and it is extremely inconvenient to declare yet another model and manually do the conversion just to get Pydantic validation working.

I see two ways for improvement without breaking the existing functionality: adding an extra class argument to SQLModel so developers can decide whether they want Pydantic validation in the table model or not; or making it possible to inherit a non-table model from a table model and use that for querying (I tried a naive implementation of the latter with a mixin class, but unfortunately I haven't yet found a way to avoid SQLAlchemy inspection errors).

If one of these proposals is acceptable, I'd be happy to start working on a PR.

[ml31415]

Sorry, that I have to kindly express quite some criticism on the status quo and support @volfpeter . Also, I just recently started to dive into SQLmodel, so sorry in advance, if I overlooked something. This comes with all due respect to the authors of this package. Thanks for your work, highly appreciated!

This package promises, to fuse validation and database definition model into one thing. Unfortunately, this issue seems to defeat this main goal to quite some extent. Reading #52 (comment) : Unfortunately I still don't see, why it shouldn't be possible to execute at least the most common pydantic validations. Checking for ordinary constraints, range, size, no extra fields and so on. The number of different issues for this topic alone (#324, #148, #225, #406, #453 and #134) shows, how counter-intuitive the current behavior is for users. For sqlalchemy, having to set fields after the initialization, some delayed validation, __post_init__, etc. might work. And if there really might remain some unsolvable cases with relationship validation: better to check everything else than nothing at all.

For a user the current situation means:

• Counter-intuitive behavior because the package rather silently doesn't do its main job
• Lots of repetitive code (multiple classes, that could be only one)
• Lots of overhead and wasted CPU cycles when creating one model from another
• Lots of boiler-plate, even if the "multiple-model-approach" should be the only solution as demonstrated in https://sqlmodel.tiangolo.com/tutorial/fastapi/multiple-models/

When validation would just work for all SQLModel derived classes, also table=True could disappear, as for validation-only, a pydantic.BaseModel, seems just as fine. If the root cause for all this should be, that there might be validation issues when reading from the database: There should just be a configurable option, to disable the validation for database reads.

About the boiler-plate in the example with the auto-incremented ID: This is such a common behavior, that I'd wish I wouldn't have to create three separate classes for it. If all the multiple models should really be necessary, it could all happen under the hood, e.g. by offering these different model classes automatically created as MyModel.Read, MyModel.Create or MyModel.Table.

It might be argued, that these different classes are "more correct". Yes, maybe. But if defining Optional[int] for the auto-increment ID is not good enough, I'd rather suggest having something like AutoGenerated[int] as a syntax to specify the behavior, that the field is present on reads, but optional on writes. And we'd be back again to only one definition class.

[ml31415]

@tiangolo I know it was considered a closed issue and maybe considered finally settled. But as you see above, I don't seem to be the only one rather unhappy with the current status quo. Would you mind to comment on this again and maybe consider one of the suggestions above?

[armujahid]

With v0.0.16 @andremmori workaround is raising AttributeError: 'MyTable1' object has no attribute '__config__' issue. I have updated it like this. This seems to be working not sure if both are equivalent or not.

from sqlmodel import SQLModel, Field
from pydantic import ConfigDict

# class BaseSQLModel(SQLModel):
#     def __init__(self, **kwargs):
#         self.__config__.table = False # AttributeError: 'MyTable1' object has no attribute '__config__'
#         super().__init__(**kwargs)
#         self.__config__.table = True

#     class Config:
#         validate_assignment = True

class BaseSQLModel(SQLModel):        
    model_config = ConfigDict(validate_assignment=True)

class MyTable1(BaseSQLModel, table=True):
    a: int = Field(primary_key=True)
    b: int

class MyTable2(BaseSQLModel, table=True):
    a: int = Field(primary_key=True)
    b: float


t1 = MyTable1(a=1, b=2)  # ok
t2 = MyTable2(b="text")   # Raises ValidationError  Input should be a valid number, unable to parse string as a number [type=float_parsing, input_value='text', input_type=str]

P.S. below solution suggested by @tiangolo doesn't work

If you need to validate data, declare a base model with table=False (the default) and then inherit from that model in one with table=True

[memark]

@armujahid What versions are you using? I'm trying to upgrade to sqlmodel 0.0.16 and sqlalchemy 2.0.28, and neither the old nor your new workaround now works.

With your workaround I get:

ValueError: <class 'dict'> has no matching SQLAlchemy type

[armujahid]

@armujahid What versions are you using? I'm trying to upgrade to sqlmodel 0.0.16 and sqlalchemy 2.0.28, and neither the old nor your new workaround now works.

With your workaround I get:

ValueError: <class 'dict'> has no matching SQLAlchemy type

v0.0.16 as mentioned in my comment.
I just did pip install sqlmodel in a clean virtual environment to ensure that the latest versions are fetched for each without specifying any version.

[copdips]

class BaseSQLModel(SQLModel):        
    model_config = ConfigDict(validate_assignment=True)

Thanks a lot for this workaround, I wasn't aware that validate_assignment is set to False by default, it should be True as I need to validate every time.
Now that it works, but I'm curious, why model instance creation in terms of SQLModel, considered as a model change which is detected by validate_assignment @tiangolo @armujahid ?

[manulera]

I agree with most of the comment of @ml31415 #52 (comment)

I think it would be great to have computed fields in the class MyModel.Read, MyModel.Create or MyModel.Table. It would really streamline the development.

I was also very surprised when validation was not being applied.

[AlexTraveylan]

class BaseSQLModel(SQLModel):
    model_config = ConfigDict(validate_assignment=True)


class Personage(BaseSQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(max_length=20, min_length=2)
    age: PositiveInt

@copdips thanks, with this subclass, It works exactly as I expected the library to work.
Maybe it will need to check if it doesn't break other functionalities, but it saves well in the database when everything is okay, and raises an error when the Pydantic schema is not okay.
Exactly what I wanted.

I have tested to modify the lib code like this :

It work too ... a new version with this update should be cool if it's ok !

[fniko]

Do I understand correctly, that this also makes it impossible to use values transformation on SQLModel class init? Or don't I understand?

For example I would like to decrypt column value and make it available as new attribute.

    # Decrypt config on class init
    def __init__(self, **data):
        # THIS IS NEVER CALLED
        super().__init__(**data)
        if self.config:
            self._config_obj = self.decrypt_config(self.config)
        ...