Adding table=True disables type coercion

[Atleago]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question 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 sqlmodel import Field, Session, SQLModel, create_engine


class HeroTable(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None


class Hero(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None


hero = Hero(id="3", name="foo", secret_name="secret_name", age="5")
hero_t = HeroTable(id="3", name="foo", secret_name="secret_name", age="5")

Description

Here, hero gets it's types coerced so during the rest of the application in which hero is scoped, both id and age are integers. With table=True, we observe that hero_t does not get this type coercion. This is non-obvious, and leads to bugs where one accesses properties of HeroTable objects expecting certain types and getting others.
The example is adjusted from actual production code that was using prior versions of pydantic, in which coercion was always done, regardless of table=True.

I understand that table=True disables validations; however, I believe this is both a regression and undocumented behavior.

Not having types of the objects one is working with makes sqlmodel only usable for the simplest of crud projects; which is sad as I have used it with great joy until the newer versions (of sqlmodel/pydantic).

Operating System

Linux, Windows, macOS

Operating System Details

No response

SQLModel Version

0.0.16

Python Version

3.10.12

Additional Context

No response

[Atleago]

This can be added as a test with:

from typing import Optional

from sqlmodel import Field, SQLModel


def test_sqlmodel_table_param_is_consistent():
    class Hero(SQLModel):
        id: Optional[int] = Field(default=None, primary_key=True)
        name: str
        secret_name: str
        age: Optional[int] = None

    class HeroTable(SQLModel, table=True):
        id: Optional[int] = Field(default=None, primary_key=True)
        name: str
        secret_name: str
        age: Optional[int] = None

    class HeroInheritanceTable(Hero, table=True):
        pass

    hero = Hero(id="3", name="foo", secret_name="secret_name", age="5")
    hero_t = HeroTable(id="3", name="foo", secret_name="secret_name", age="5")
    hero_it = HeroInheritanceTable(id="3", name="foo", secret_name="secret_name", age="5")

    assert hero.model_dump() == hero_t.model_dump()
    assert hero.model_dump() == hero_it.model_dump()

[grafail]

I have encountered the same issue. This seems to be behaviour that is only happening under Pydantic V2.

from datetime import datetime

from pydantic import BaseModel
from sqlmodel import SQLModel, Field


class DatetimeModel(BaseModel):
    timestamp: datetime

class DatetimeSQLModel(SQLModel):
    id: str = Field("dummy", primary_key=True)
    timestamp: datetime

class DatetimeSQLTableModel(SQLModel, table=True):
    id: str = Field("dummy", primary_key=True)
    timestamp: datetime

t = '2024-11-13T12:30:12.584245Z'
m1 = DatetimeModel(timestamp=t)
m2 = DatetimeSQLModel(timestamp=t)
m3 = DatetimeSQLTableModel(timestamp=t)

print(type(m1.timestamp))
print(type(m2.timestamp))
print(type(m3.timestamp))

if __name__ == '__main__':
    pass

Pydantic 1.10.19 Output:

<class 'datetime.datetime'>
<class 'datetime.datetime'>
<class 'datetime.datetime'>

Pydantic 2.9.2 Output:

<class 'datetime.datetime'>
<class 'datetime.datetime'>
<class 'str'>

[monchin]

It is an old issue. Pydantic does not actually validate when table is True. You can see PR #1041 and some related issue links there.

[Atleago]

@monchin perhaps an old issue, though this thread here is older than what you reference: it is an undocumented regression in sqlmodel1 to sqlmodel2. Pydantic itself always does validations in all major versions.