Best way to do polymorphism / tagged unions with an object in a field in an SQLModel?

[PhilippeGSK]

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

class ChannelUserMessage(BaseModel):
    type: Literal["user"]
    contents: str


class ChannelUserMessageWrite(BaseModel):
    type: Literal["user"]
    contents: str


class ChannelActionMessage(BaseModel):
    type: Literal["action"]
    contents: str
    character: CharacterPublic


class ChannelActionMessageWrite(BaseModel):
    type: Literal["action"]
    contents: str
    character_id: int


class ChannelDialogueMessage(BaseModel):
    type: Literal["dialogue"]
    contents: str
    character: CharacterPublic


class ChannelDialgueMessageWrite(BaseModel):
    type: Literal["dialogue"]
    contents: str
    character_id: int


ChannelMessageData = ChannelUserMessage | ChannelActionMessage | ChannelDialogueMessage
ChannelMessageDataAdapter = TypeAdapter[ChannelMessageData](ChannelMessageData)
ChannelMessageDataWrite = (
    ChannelUserMessageWrite | ChannelActionMessageWrite | ChannelDialgueMessageWrite
)


class ChannelMessage(SQLModel, table=True):
    id: int = Field(default=None, primary_key=True)
    channel_id: int = Field(foreign_key="channel.id")
    author_id: int = Field(foreign_key="user.id")
    author: User = Relationship()

    # Message data
    type: str = Field(default=None)
    contents: str | None = Field(default=None)
    character_id: int | None = Field(default=None, foreign_key="character.id")
    character: Character | None = Relationship()

    @computed_field
    @property
    def data(self) -> ChannelMessageData:
        return ChannelMessageDataAdapter.validate_python(self, from_attributes=True)

    @data.setter
    def data(self, val: ChannelMessageDataWrite):
        self.type = getattr(val, "type")
        self.contents = getattr(val, "contents", None)
        self.character_id = getattr(val, "character_id", None)


class ChannelMessagePublic(SQLModel):
    id: int
    channel_id: int

    author: UserPublic
    data: ChannelMessageData

Description

I'm making a small chat feature.

I'm trying to create a system where a field (data) in my SQLModel table (ChannelMessage) gets populated differently based on a type string. My current setup is in the code above. It works. I was wondering, though, if there were any cleaner and more automated ways to achieve the same result. I'm kind of new to the world of ORMs and things like that in general so I don't know about all the possibilities.

• I can create new "child" tables or reorganize my models if that will lead to a cleaner solution.
• I'd like to get a solution that doesn't involve me writing the same field names in so many places without type checking, because that's kind of a recipe for disaster for me personally.
• I'm following discord's model where a message response will contain a User object field instead of a user id. I will still have to decide on what will be an id. I'm using FastAPI so I can simply set response_model=list[ChannelMessagePublic] and it takes care of eliminating the fields I don't want it to include.

Operating System

Linux

Operating System Details

Debian GNU/Linux 12 (bookworm) x86_64

SQLModel Version

0.0.24

Python Version

Python 3.13.5

Additional Context

No response

[Avalon2222]

Your approach (flat columns plus a computed data field) is a good fit for a chat table. You can remove most of the repetition and the getattr guessing with discriminated unions, so pydantic picks the right shape from type, and with a setter driven by the write model's model_dump():

from typing import Annotated, Literal, Optional, Union
from pydantic import BaseModel, Field as PField, TypeAdapter, computed_field
from sqlmodel import Field, Relationship, SQLModel

# read shapes
class UserMsg(BaseModel):
    type: Literal["user"]
    contents: str

class ActionMsg(BaseModel):
    type: Literal["action"]
    contents: str
    character: CharacterPublic

class DialogueMsg(BaseModel):
    type: Literal["dialogue"]
    contents: str
    character: CharacterPublic

MessageData = Annotated[Union[UserMsg, ActionMsg, DialogueMsg], PField(discriminator="type")]

# write shapes: action and dialogue share one model
class UserMsgWrite(BaseModel):
    type: Literal["user"]
    contents: str

class CharacterMsgWrite(BaseModel):
    type: Literal["action", "dialogue"]
    contents: str
    character_id: int

MessageDataWrite = Annotated[Union[UserMsgWrite, CharacterMsgWrite], PField(discriminator="type")]

_read = TypeAdapter(MessageData)
_write = TypeAdapter(MessageDataWrite)
DATA_COLUMNS = ("type", "contents", "character_id")    # the only place column names are listed


class ChannelMessage(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    # ... channel_id / author as before
    type: str
    contents: Optional[str] = None
    character_id: Optional[int] = Field(default=None, foreign_key="character.id")
    character: Optional[Character] = Relationship()

    @computed_field
    @property
    def data(self) -> MessageData:
        return _read.validate_python(self, from_attributes=True)

    def set_data(self, val) -> None:
        dumped = _write.validate_python(val).model_dump()   # accepts a model or a dict, checks the tag
        for col in DATA_COLUMNS:
            setattr(self, col, dumped.get(col))             # columns the new type doesn't use become None


class ChannelMessagePublic(SQLModel):
    id: int
    data: MessageData

I ran it on SQLModel 0.0.48 with SQLite:

{'id': 1, 'data': {'type': 'user', 'contents': 'hi'}}
{'id': 2, 'data': {'type': 'action', 'contents': 'waves', 'character': {'id': 1, 'name': 'Ana'}}}

set_data({"type": "dialogue", "contents": "x"}) fails validation because character_id is missing. Switching a message from action to user resets character_id to None.

What this buys you:

• One source of truth per shape. Field names live in the pydantic models. DATA_COLUMNS is the only list of columns, and a typo there raises a ValueError on the first set_data() call, since SQLModel rejects unknown attributes.
• Better errors and OpenAPI. With discriminator="type", pydantic validates only the branch named by type (clearer messages than "none of the union members matched"), and FastAPI's schema gets a proper oneOf + discriminator for response_model=list[ChannelMessagePublic].
• I used a method instead of a property setter for writes. It keeps the read-only computed field separate from the write path and makes the write explicit (msg.set_data(payload) in your endpoint), but your setter works too if you prefer it.

If the types diverge a lot later (many type-specific columns), joined-table inheritance in SQLAlchemy is the next step up. For a handful of shared columns like this, a single table plus discriminated unions is simpler.