sqlmodel: SQLModel doesn't raise ValidationError

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!

😞

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.

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 https://github.com/tiangolo/sqlmodel/issues/52#issuecomment-1311987732 : 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.

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.

@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 😉

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

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/

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

@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?

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

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.

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.

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

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

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