BUG: SQLModels with table=True aren't properly restored from JSON

[DanielGibson]

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

# Minimal reproducer for bugs and inconsistencies around SQLModel->Json->SQLModel roundtrips

import json
import traceback
import warnings

from uuid import uuid4, UUID

from pydantic import BaseModel
from sqlmodel import SQLModel, Field

class InnerModelSQLNoTable(SQLModel, table=False):
    id: UUID = Field(default_factory=uuid4, primary_key=True)

class InnerModelSQL(InnerModelSQLNoTable, table=True):
    pass

class OuterModel(BaseModel):
    im: InnerModelSQL

class OuterModelNoTable(BaseModel):
    im: InnerModelSQLNoTable


def repro_impl(obj, use_model_validate_json):
    cls_model = type(obj)
    m = "model_validate_json(j)" if use_model_validate_json else "model_validate(json.loads(j))"
    print(f"\nChecking for {cls_model.__name__}, creating copy from json with {m}")

    j = obj.model_dump_json()
    if use_model_validate_json:
        obj2 = cls_model.model_validate_json(j)
    else:
        obj2 = cls_model.model_validate(json.loads(j))

    im = obj2 if cls_model.__name__.startswith("Inner") else obj2.im
    if isinstance(im.id, UUID):
        print("... ok")
    else:
        print(f"!!! ERROR: InnerModel*.id is not a UUID but {type(im.id)}")

    try:
        obj2.model_dump_json()
    except:
        print("obj2.model_dump_json() failed with")
        traceback.print_exc()

def repro(with_table):
    if with_table:
        print("\n### Testing with an InnerModel derived from SQLModel with table=True")
        im = InnerModelSQL()
        om = OuterModel(im=im)
    else:
        print("\n### Testing with an InnerModel derived from SQLModel with table=False")
        im = InnerModelSQLNoTable()
        om = OuterModelNoTable(im=im)

    repro_impl(im, True)
    repro_impl(om, True)

    repro_impl(im, False)
    repro_impl(om, False)


if __name__ == "__main__":
    #warnings.simplefilter("error") # turn UserWarnings into exceptions
    repro(False)
    repro(True)

Description

Basically: For SQLModels with table=True, model_validate_json(jstr) is broken, and so is model_validate( json.loads(jstr) ), but in slightly different ways.

model_validate_json(jstr) does not restore non-JSON types (meaning, ones not natively supported by JSON) properly when used with a table=True SQLModel.
For example, serialized UUID and datetime objects remain strings.

model_validate( json.loads(jstr) ) does restore those types properly when used directly on an SQLModel table=True type, but when that SQLModel is a member of another type it's broken in the same way as the former case (e.g. UUIDs remain strings).

Weirdly, for SQLModels with table=False everything seems to work.

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.37

Python Version

3.13.9

Additional Context

Of course this issue also creates problems when using affected types with FastAPI.

The output when running the aforementioned code looks like this:

Details

$ python3 repro.py 

### Testing with an InnerModel derived from SQLModel with table=False

Checking for InnerModelSQLNoTable, creating copy from json with model_validate_json(j)
... ok

Checking for OuterModelNoTable, creating copy from json with model_validate_json(j)
... ok

Checking for InnerModelSQLNoTable, creating copy from json with model_validate(json.loads(j))
... ok

Checking for OuterModelNoTable, creating copy from json with model_validate(json.loads(j))
... ok

### Testing with an InnerModel derived from SQLModel with table=True

Checking for InnerModelSQL, creating copy from json with model_validate_json(j)
!!! ERROR: InnerModel*.id is not a UUID but <class 'str'>
/path/to/project/.venv/lib/python3.13/site-packages/pydantic/main.py:528: UserWarning: Pydantic serializer warnings:
  PydanticSerializationUnexpectedValue(Expected `uuid` - serialized value may not be as expected [field_name='id', input_value='b7b755e0-84ee-4f99-81c7-423e48d6cb49', input_type=str])
  return self.__pydantic_serializer__.to_json(

Checking for OuterModel, creating copy from json with model_validate_json(j)
!!! ERROR: InnerModel*.id is not a UUID but <class 'str'>

Checking for InnerModelSQL, creating copy from json with model_validate(json.loads(j))
... ok

Checking for OuterModel, creating copy from json with model_validate(json.loads(j))
!!! ERROR: InnerModel*.id is not a UUID but <class 'str'>

[YuriiMotov]

This is a known limitation, but it's not clearly documented yet.

You should NOT use table-models for validation. You should create separate model for validation (use inheritance to reduce code duplication) as shown in https://sqlmodel.tiangolo.com/tutorial/fastapi/multiple-models/#multiple-models-with-inheritance

[DanielGibson]

This really should be documented (or fixed, is there a good reason for this or is it just a bug?), this has cost me (and probably others) days to figure out, it's not obvious at all.

Also note how https://sqlmodel.tiangolo.com/tutorial/fastapi/simple-hero-api/#create-heroes-path-operation implies that using table-models with FastAPI is fine - that's extremely misleading (even if it might work in that particular case because all involved types can be natively represented by JSON).

The tutorial you linked suggests using inheritance when having additional fields in the table model that aren't needed (or shouldn't be) in the the other one (the id in that case) - it does not mention that this is how it must be done.