Help with using custom types (TypesDecorator)

[madduck]

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 searched the Web "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 Any

from pydantic import GetCoreSchemaHandler
from pydantic_core import CoreSchema, core_schema
from sqlmodel import (
    Field,
    Integer,
    SQLModel,
    Session,
    TypeDecorator,
    create_engine,
    select,
)


class IntWhereZeroIsNone(int):
    @staticmethod
    def validator(value: int | None) -> int | None:
        return value if value != 0 else None

    @classmethod
    def __get_pydantic_core_schema__(
        cls, source_type: Any, handler: GetCoreSchemaHandler
    ) -> CoreSchema:
        return core_schema.no_info_after_validator_function(cls.validator, handler(int))


class IntWhereZeroIsNoneType(IntWhereZeroIsNone, TypeDecorator):
    impl = Integer

    def process_bind_param(self, value: int | None, dialect: Any) -> int | None:
        return self.validator(value)


class MyModel(SQLModel, table=True):
    x: IntWhereZeroIsNoneType = Field(default=None, primary_key=True, sa_type=Integer)


m = MyModel(x=1)
assert m.x == 1
m = MyModel(x=0)
assert m.x is None


engine = create_engine("sqlite:///")

with Session(engine) as session:
    session.add(MyModel(x=0))
    session.add(MyModel(x=None))
    session.commit()

    for row in session.exec(select(MyModel)):
        assert row.x is None

Description

When I look at this code, it seems straight-forward. The linters and type checkers complain about assigning None to x but more importantly, assert m.x is None fails.

This is a silly example: an int field that is None instead of 0 and otherwise just an int, but I am wondering how to get there.

How would you do it?

Operating System

Linux, Windows, macOS, Other

Operating System Details

No response

SQLModel Version

0.0.24

Python Version

3.13.3

Additional Context

No response

[kokoc30]

There are three separate issues in this example:

1. With SQLModel 0.0.24 and Pydantic v2, a table=True model's ordinary constructor uses the table-construction path rather than Pydantic validation. Use MyModel.model_validate(...) when you want validators to run (versioned implementation).
2. sa_type=Integer selects SQLAlchemy's ordinary integer type. It does not install your TypeDecorator. Keep the Python value type (int | None) separate from the SQL storage type.
3. x is the primary key. An autogenerated integer key cannot remain None after insertion; SQLite generates a key. Use a separate primary key if the value you want to store is nullable.

For that nullable-value example, the two conversion points can be expressed like this:

from pydantic import field_validator
from sqlalchemy import Column, Integer
from sqlalchemy.types import TypeDecorator
from sqlmodel import Field, SQLModel

def zero_as_none(value: int | None) -> int | None:
    return None if value == 0 else value

class ZeroAsNullInteger(TypeDecorator):
    impl = Integer
    cache_ok = True

    def process_bind_param(self, value, dialect):
        return zero_as_none(value)

    def process_result_value(self, value, dialect):
        return zero_as_none(value)

class MyModel(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    x: int | None = Field(
        default=None,
        sa_column=Column(ZeroAsNullInteger(), nullable=True),
    )

    @field_validator("x", mode="after")
    @classmethod
    def normalize_x(cls, value: int | None) -> int | None:
        return zero_as_none(value)

m = MyModel.model_validate({"x": 0})
assert m.x is None

The Pydantic validator normalizes validated input in memory. The SQLAlchemy bind hook normalizes values sent to the database, and the result hook also normalizes a stored zero when reading it. The latter hooks are database I/O hooks, so they do not rewrite an attribute immediately when you call MyModel(x=0) or assign m.x = 0. Use the validation entry point for that input path.

After inserting this revised model, check row.x is None; row.id should instead have its generated key. If your real requirement is a custom primary-key type, it needs different semantics from “zero becomes a stored NULL”.

References: Pydantic field validators, SQLAlchemy TypeDecorator bind/result hooks, and SQLModel's generated-ID explanation.