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)
        ...

[tepelbaum]

Hi all!

If it can help others:

class SQLModelWithVal(SQLModel):
    """
    Helper class to ease validation in SQLModel classes with table=True
    """
    @classmethod
    def create(cls, **kwargs):
        """
        Forces validation to take place, even for SQLModel classes with table=True
        """
        return cls(**cls.__bases__[0](**kwargs).model_dump())

and then

class FooBase(SQLModelWithVal):
    """
    Example base class to illustrate the table validation behaviour of SQLModelWithVal
    """
    bar: int

    @field_validator('bar', mode='before')
    def convert_bar(cls, bar):
        """
        Example validation
        """
        return int(bar / 1_000_000)


class Foo(FooBase, table=True):  # type: ignore
    """
    Example class to illustrate the table validation behaviour of SQLModelWithVal
    """
    id: Optional[int] = Field(default=None, primary_key=True)

Now:

• foo = Foo(bar=3_000_000)will give a bar of 3_000_000
• foo = Foo.create(bar=3_000_000)will give a bar of 3

You can go take a look at https://github.com/FR-PAR-ECOACT/ecodev-core if you like, where they are other helpers of the sort around tiangolo awesome libraries (and also around pydantic) :)

[demux79]

Thank you for the helpful comments. I merged @AlexTraveylan and @tepelbaum into this for pydantic V2.

from sqlmodel import SQLModel
from sqlmodel._compat import SQLModelConfig


class SQLModelValidation(SQLModel):
    """
    Helper class to allow for validation in SQLModel classes with table=True
    """

    model_config = SQLModelConfig(
        from_attributes=True, validate_assignment=True 
    )

Inherit from this class instead of SQLModel to allow for validation.

[realchandan]

For anyone using FastAPI, here's a code snippet that can be helpful -

from typing import Annotated

from fastapi import FastAPI
from pydantic import AfterValidator
from sqlmodel import Field, SQLModel


class User(SQLModel, table=True):
    name: str = Field(primary_key=True)


app = FastAPI()


@app.post("/user1")
async def user1(body: User):
    # This one doesn't validate
    print(body)


@app.post("/user2")
async def user2(body: Annotated[User, AfterValidator(User.model_validate)]):
    # This one validates
    print(body)

[bestend]

It's really amazing!

For anyone using FastAPI, here's a code snippet that can be helpful -

from typing import Annotated

from fastapi import FastAPI
from pydantic import AfterValidator
from sqlmodel import Field, SQLModel


class User(SQLModel, table=True):
    name: str = Field(primary_key=True)


app = FastAPI()


@app.post("/user1")
async def user1(body: User):
    # This one doesn't validate
    print(body)


@app.post("/user2")
async def user2(body: Annotated[User, AfterValidator(User.model_validate)]):
    # This one validates
    print(body)

[Lim0H]

In the new version it works for me

class BaseModel(SQLModel):
    def __init__(self, **data):
        is_table = self.model_config["table"]
        self.model_config["table"] = False
        super().__init__(**data)
        self.model_config["table"] = is_table

[espdev]

Using validate_assignment=True with table=True models is not a good idea. In this case, the validators start working very strangely. For example, model_validator will be called when setting a value for each field, which is not correct behavior.

Here is an example:

from pydantic import ConfigDict, model_validator
from sqlmodel import SQLModel, Field

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

    a: int = Field(primary_key=True)
    b: str | None = None
    c: str | None = None

    @model_validator(mode='after')
    def _validate(self):
        print(f'validate: {self!r}')
        return self

class Table(Model, table=True):
    pass

m = Model(a=1, b='b')
t = Table(a=1, b='b')

Output:

validate: Model(a=1, b='b', c=None)
validate: Table(a=1)
validate: Table(a=1, b='b')
validate: Table(a=1, b='b', c=None)

As you can see, the model validator in the case of the Table class is called three times when the model is not yet fully constructed, which leads to inconsistent state and incorrect validators in most cases.

Just imagine a validator doing something like this:

    @model_validator(mode='after')
    def _validate(self):
        if self.b is None and self.c is None:
            raise ValueError
        return self

You will not be able to construct the model in this case because SQLModel breaks validation behavior:

Traceback (most recent call last):
  File "scratch_87.py", line 27, in <module>
    t = Table(a=1, b='b')
        ^^^^^^^^^^^^^^^^^
  File "<string>", line 4, in __init__
  File "/.../lib/python3.11/site-packages/sqlalchemy/orm/state.py", line 571, in _initialize_instance
    with util.safe_reraise():
  File "/.../lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/.../lib/python3.11/site-packages/sqlalchemy/orm/state.py", line 569, in _initialize_instance
    manager.original_init(*mixed[1:], **kwargs)
  File "/.../lib/python3.11/site-packages/sqlmodel/main.py", line 811, in __init__
    sqlmodel_init(self=__pydantic_self__, data=data)
  File "/.../lib/python3.11/site-packages/sqlmodel/_compat.py", line 355, in sqlmodel_init
    sqlmodel_table_construct(
  File "/.../lib/python3.11/site-packages/sqlmodel/_compat.py", line 274, in sqlmodel_table_construct
    setattr(self_instance, key, value)
  File "/.../lib/python3.11/site-packages/sqlmodel/main.py", line 824, in __setattr__
    super().__setattr__(name, value)
  File "/.../lib/python3.11/site-packages/pydantic/main.py", line 881, in __setattr__
    self.__pydantic_validator__.validate_assignment(self, name, value)
pydantic_core._pydantic_core.ValidationError: 1 validation error for Table
  Value error,  [type=value_error, input_value=Table(a=1), input_type=Table]
    For further information visit https://errors.pydantic.dev/2.9/v/value_error

The validation behavior is completely broken in SQLModel for table models. And definitely validate_assignment=True is not a fix for this issue.

[mg3146]

came here because this is the most redic bug / missing feature that should be stated upfront.

Using model_config = ConfigDict(from_attributes=True, validate_assignment=True) does not seem to work when you use model_validators 'after'

[ta-soft]

came here because this is the most redic bug / missing feature that should be stated upfront.

Using model_config = ConfigDict(from_attributes=True, validate_assignment=True) does not seem to work when you use model_validators 'after'

Not only that, but this workaround also doesn't fix the fact you only get 1 field_validator error at a time when table=True is set - which means if you're using forms for example, you're only going to show the user 1 error per submit (the user is going to have to click on the submit button multiple times and fix one error each time).

So I concur with @mg3146 and others: despite the fact sqlmodel / FastAPI are great, this is an issue that should be re-considered to see if we can get to a solution that both retains the power of Pydantic (same validation behaviours with table=True and otherwise) while also keeping the basics of validation and ORM simple (no need to play with fancy flags and object duplication and STILL not get the same behaviour)

[ta-soft]

came here because this is the most redic bug / missing feature that should be stated upfront.
Using model_config = ConfigDict(from_attributes=True, validate_assignment=True) does not seem to work when you use model_validators 'after'

Not only that, but this workaround also doesn't fix the fact you only get 1 field_validator error at a time when table=True is set - which means if you're using forms for example, you're only going to show the user 1 error per submit (the user is going to have to click on the submit button multiple times and fix one error each time).

So I concur with @mg3146 and others: despite the fact sqlmodel / FastAPI are great, this is an issue that should be re-considered to see if we can get to a solution that both retains the power of Pydantic (same validation behaviours with table=True and otherwise) while also keeping the basics of validation and ORM simple (no need to play with fancy flags and object duplication and STILL not get the same behaviour)

Just to illustrate what I said, here's a sample code and its output. Sorry for the lack of code re-use and sophistication, but I just wanted to make the code simple and easy to understand.

from sqlmodel import SQLModel, Field
from pydantic import ConfigDict, BaseModel, field_validator
from typing import Optional

class Man(BaseModel):
    name: str
    age: int
    @field_validator("name")
    @classmethod
    def validate_name(cls, value):
        if len(value) < 3:
            raise ValueError("Name must be at least 3 characters")
    @field_validator("age")
    @classmethod
    def validate_age(cls, value):
        if value < 18:
            raise ValueError("Age must be at least 18")
        
class ManSQLModel(SQLModel):
    name: str
    age: int
    @field_validator("name")
    @classmethod
    def validate_name(cls, value):
        if len(value) < 3:
            raise ValueError("Name must be at least 3 characters")
    @field_validator("age")
    @classmethod
    def validate_age(cls, value):
        if value < 18:
            raise ValueError("Age must be at least 18")

class ManTable(SQLModel, table=True):
    model_config = ConfigDict(validate_assignment=True, from_attributes=True)
    __table_args__ = {
        "extend_existing": True
    }
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    age: int
    @field_validator("name")
    @classmethod
    def validate_name(cls, value):
        if len(value) < 3:
            raise ValueError("Name must be at least 3 characters")
    @field_validator("age")
    @classmethod
    def validate_age(cls, value):
        if value < 18:
            raise ValueError("Age must be at least 18")


if __name__ == "__main__":
    print("Creating Man")
    try:
        Man()
    except Exception as e:
        print(e)
    print("Creating Man with the 2 field validators errors")
    try:
        Man(name="a", age=17)
    except Exception as e:
        print(e)
    print("Creating ManTable")
    try:
        ManTable()
        print("ManTable no errors")
    except Exception as e:
        print(e)
    #run validations for ManTable
    print("Manual validation ManTable")
    try:
        ManTable.model_validate(ManTable().model_dump())
    except Exception as e:
        print(e)
    print("Manual validation ManTable with 2 field validators errors")
    try:
        ManTable.model_validate(ManTable(name="a", age=17).model_dump())
    except Exception as e:
        print(e)
    print("Creating ManSQLModel")
    try:
        ManSQLModel()
    except Exception as e:
        print(e)
    print("Creating ManSQLModel with the 2 field validators errors")
    try:
        ManSQLModel(name="a", age=17)
    except Exception as e:
        print(e)

Output:

Creating Man
2 validation errors for Man
name
  Field required [type=missing, input_value={}, input_type=dict]
    For further information visit https://errors.pydantic.dev/2.9/v/missing
age
  Field required [type=missing, input_value={}, input_type=dict]
    For further information visit https://errors.pydantic.dev/2.9/v/missing
Creating Man with the 2 field validators errors
2 validation errors for Man
name
  Value error, Name must be at least 3 characters [type=value_error, input_value='a', input_type=str]
    For further information visit https://errors.pydantic.dev/2.9/v/value_error
age
  Value error, Age must be at least 18 [type=value_error, input_value=17, input_type=int]
    For further information visit https://errors.pydantic.dev/2.9/v/value_error
Creating ManTable
ManTable no errors
Manual validation ManTable
2 validation errors for ManTable
name
  Field required [type=missing, input_value={'id': None}, input_type=dict]
    For further information visit https://errors.pydantic.dev/2.9/v/missing
age
  Field required [type=missing, input_value={'id': None}, input_type=dict]
    For further information visit https://errors.pydantic.dev/2.9/v/missing
Manual validation ManTable with 2 field validators errors
1 validation error for ManTable
name
  Value error, Name must be at least 3 characters [type=value_error, input_value='a', input_type=str]
    For further information visit https://errors.pydantic.dev/2.9/v/value_error
Creating ManSQLModel
2 validation errors for ManSQLModel
name
  Field required [type=missing, input_value={}, input_type=dict]
    For further information visit https://errors.pydantic.dev/2.9/v/missing
age
  Field required [type=missing, input_value={}, input_type=dict]
    For further information visit https://errors.pydantic.dev/2.9/v/missing
Creating ManSQLModel with the 2 field validators errors
2 validation errors for ManSQLModel
name
  Value error, Name must be at least 3 characters [type=value_error, input_value='a', input_type=str]
    For further information visit https://errors.pydantic.dev/2.9/v/value_error
age
  Value error, Age must be at least 18 [type=value_error, input_value=17, input_type=int]
    For further information visit https://errors.pydantic.dev/2.9/v/value_error

[neuralsignal]

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.

I know I am late to this issue as I just started using SQLModel, but couldn't the other fields that are not relationships to other tables still be validated? The fields that are foreign key references would be ignored during validation as they are validated in the foreign table anyway.

For example:

class Table1(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    
    table2s: list["Table2"] = Relationship(back_populates="table1")
    
class Table2(SQLModel, table=True):
    id: int = Field(foreign_key="table1.id", primary_key=True)
    field1: int
    
    table1: Table1 = Relationship(back_populates="table2s")
    
 entry1 = Table1(name="asdf")
 entry2 = Table2(table1=entry1, field1=123)   
 
 with Session(engine) as session:
    session.add(entry1)
    session.add(entry2)
    session.commit()

This should validate the id (if given) and name in Table1 and the field1 in Table2. The id in Table2 is ignored as a table1 instance was passed to the table 1 relationship.

[sawyna]

I got stuck by this issue and while going through the comments, I realised that there is no solution for this. The proposed solution by the author is to create a data model and then copy them. I think that works fine when there is actually a data model needed. For cases where I am dealing with the database directly, there's no data model and introducing one feels not right.

The other proposal is to set validate_assignment=True as that'll validate even when assigning/setting the attribute. It looks like this has issues while dealing with AfterValidators.

I'm using the following pattern. Not sure if this is the best and if this works all the time (I think it does).

class MyTable(SQLModel, table=True):
    name: str
    # field_defs


# This will not do any validation.
entity = MyTable(name='name')

# Make a call to model_validate after creating the entity and re-create the entity.
entity = MyTable.model_validate(entity)

[buiapp]

I wasted too much time not to comment on this... I actually expected validate_assignment = True to be the default behavior.

I followed the SQL-Database tutorial on the website, thinking that extending the Hero model with a datetime or UUID property would be no problem. Saving it to the database gave me error messages, I thought I had dealt with problems coming from Sqlalchemy.