Can I create a variable that is not a column in a table but is a field in a model?

[recsater]

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 Test(SQLModel, table=True):
    id: str = Field(primary_key=True) # This is table column.
    age: int = ?? # this is not table column, but can be serialized with functions like model_dump_json.

I also tried https://sqlmodel.tiangolo.com/tutorial/fastapi/multiple-models/#multiple-models-with-inheritance

class HeroBase(SQLModel):
    name: str = Field(primary_key=True)
    secret_name: str
    age: int | None = Field(default=None, index=True)
class Hero(HeroBase, table=True):
    id: int | None = Field(default=None)

class HeroUpdate(HeroBase):
    pass # name, secret_name can be None. but HeroUpdate() raises validation error.

Description

I'd like to retrieve data from a database and provide additional fields to the user. How can I achieve this?

thank you.

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.14

Python Version

3.11.6

Additional Context

And I tried PrivateAttr as well, but that doesn't serialize either.

[wangxin688]

seems it's not support hybrid_property or colunm_property in sqlmodel yet. I guess that's you want in sqlalchemy.

[PipeKnight]

Well, I'm using column_property with SQLModel without any issues. Like this:

QuestionPool.questions_count = column_property(
    select(func.count(LinkPoolQuestion.question_version_id))
    .where(LinkPoolQuestion.question_pool_id == QuestionPool.id)
    .correlate(QuestionPool)
    .scalar_subquery(),
)

[PipeKnight]

I checked and hybrid_property could be used in the same way. Described here:
#299 (comment)

[Jijun-TANG]

@PipeKnight Thanks so much for the reply, it helps a lot!

[PipeKnight]

See my reply here: #729 (reply in thread)
Both column_property and hybrid_property are supported is you're using them straight-forward and not like decorators

[recsater]

I still can't create a serializable variable that isn't a column in a table.

[seedor87]

Also cannot figure this out. Was looking for a workaround for a deficiency of Relationship fields, but without this the pydantic model and the sa model need to remain broken apart. It would be very nice to have this added as a feature, or at least more clearly documented.

[redb0]

@recsater

It's not entirely clear what you want to get as a result.

In your example, the name field cannot be None, it is explicitly annotated as str. To make it optional, you need to make an annotation foo: str | None = Field(default=None)

[seedor87]

SQLModel will require a column for the Field called foo to be created/exist in the table for HeroBase. This is not what is desired by op.
I am almost sure that using default=None will NOT have the effect of making the validation of the db table structure for the column foo elective.
EDIT: I just looked into the SQLModel Field code a little closer and it appears to relay the value directly to pydantic's Field,

default: Default value if the field is not set.

[redb0]

@seedor87

If I understand correctly, you want to make a field that will not be in the database, but it will be in the model. You will have to make an additional model with this field. But then you will have to fill in this field manually

from typing import Optional
from sqlmodel import Field, SQLModel


class Base(SQLModel):
    name: str = Field(
        max_length=100,
        description='Name',
    )


class Table(Base):
    id: Optional[int] = Field(
        default=None,
        primary_key=True,
        description='ID',
    )

class TablePublic(Base):
    age: Optional[int] = Field(
        default=None,
    )


a = Table(id=1, name='Foo')

b = TablePublic.model_validate(a)
print(f'{b = }')  # <-- TablePublic(name='Foo', age=None)

c = TablePublic.model_validate(a, update={'age': 42})
print(f'{c = }')  # <-- TablePublic(name='Foo', age=42)

[rt87]

Same problem...

However, if I declare an attribute as private (e.g. by a leading underscore), it will NOT be considered a column, i.e. it is a mere Python object attribute, but no table column.