How to create computed columns ?

First Check

I added a very descriptive title to this issue.
I used the GitHub search to find a similar issue 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 Album(SQLModel):
    title: str
    slug: str
    description: str


# what i've tried but doesn't work e.g : 

#     slug: str = column_property(slugify(title))
# E   NameError: name 'title' is not defined

class Album(SQLModel):
    title: str
    slug: str = column_property(slugify(title))
    description: str

Description

I'm trying to generate a column named "slug" that is a slugified version of "title" so it should be persisted in database, and be updated when title changes.

But so far no luck, I've looked at column property but didn't manage to make it work with SQLModel. I saw that there are events "before update" ... but I don't think this is the way to go

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.9.2

Additional Context

No response

I tried using @hybrid_property decorator from sqlalchemy, but it raises :

E   fastapi.exceptions.FastAPIError: Invalid args for response field! Hint: check that <class 'sqlalchemy.ext.hybrid.hybrid_property'> is a valid pydantic field type

And if I set :

from pydantic import BaseConfig

BaseConfig.arbitrary_types_allowed = True

We now have :

ValueError: [TypeError("'wrapper_descriptor' object is not iterable"), TypeError('vars() argument must have __dict__ attribute')]

@sorasful

Hi @sorasful,

So basically you want the slug = slugify(title)?

To do so I would do the following:

from sqlmodel import SQLModel
from pydantic import root_validator
from typing import Optional

class Album(SQLModel):
    title: str
    slug: Optional[str]
    description: str

@root_validator
def create_slug(cls, values):
    title = values.get("title")
    slugify_title = slugify(title)
    values["slug"] = slugify_title
    return values

The input:

{
  "title": "My Album Title",
  "description": "This is a description"
}

The output I got:

{
  "title": "My Album Title",
  "description": "This is a description",
  "slug": "my-album-title"
}

@FabiEbert

@FabiEbert Hello, yeah that's what I ended up doing. But I don't feel like is the cleaner way you know. Also, if you want the slug to be updated each time you update the title you need to add this in your model

    class Config:
        validate_assignment = True

Any other thoughts on how to actually use the computed_property() from SQL alchemy? E.g.

class Parent(SQLModel):
....
    child_count: Optional[int] = Field(sa_column=column_property(
        select(func.count(Child.id)).where(Child.parent_id == id)
    ))

This results in in "column Parent.child_count does not exist."

Related to this issue #240

I think this is one of biggest SQLModel drawbacks I've encountered.
I tried the root-validator approach (which needs validate_assignment=True in order to get the computer column properly updated), but the root validator method is sometime invoked with an incomplete dict (having the additional _sa_instance_state field, but without other required fields such as required_field1 in the example below).

I am trying to use the root validator in a 'mix-in' class, in order to calculate an hash-value on a subset of the model fields.

class ModelWithHashField(BaseModel):
    final_hash: str  = ""
    
    @root_validator(pre=False,
                    skip_on_failure=True)
    def calculate_global_hash(cls, values) -> Dict:
        values["final_hash"] = sha256_hash_values(
            values['required_field1'], values['required_field2']
        )
        return values
        
class MyModel(SQLModel, ModelWithHashField, table=True):
    required_field1: str
    required_field2: str

Pydantic v2.0 adds support for computed fields, which should allow computed columns once Pydantic V2 support is added (related to #532, #621)

This worked for me

    @computed_field(return_type=str)
    @declared_attr
    def hello_world(self):
        return column_property(
            func.concat('hello ', "world")
        )

but could not do a query

Update, this is working:

    # from the Conversation table
    @computed_field(return_type=Optional[bool])
    @property
    def has_any_unread_message(self):
        if session := object_session(self):
            return (
                session.exec(
                    select(Message)
                    .where(Message.conversation_id == self.id)
                    .limit(1)
                ).first()
                is not None
            )

@samidarko

Workaround: Generating a dynamic property (this does NOT add a column to the database)

As @samidarko already mentioned, with pydantic 2.0's computed_field decorator, you can generate a dynamic property:

from sqlmodel import SQLModel
from pydantic import computed_field
from slugify import slugify

class Album(SQLModel):
    title: str
    description: str

    @computed_field
    @property
    def slug(self) -> str:
        return slugify(self.title)

Result:

>>> Album(title='A Title With Many Spaces', description='I am the description')
Album(title='A Title With Many Spaces', description='I am the description', slug='a-title-with-many-spaces')

Note that this solution computes the 'column' each time it is accessed and does not create a new column in the database. However, when disregarding efficiency considerations, I believe that this is a good workaround for many cases.

Application to other cases
You can replace the code in slug() with whatever your need is. You can even access relationship attributes:

class Article(SQLModel, table=True):
    __tablename__ = "food_article"

    id: int | None = Field(default=None, primary_key=True)
    price_history: list["PriceHistory"] = Relationship()

    @computed_field
    @property
    def unit_price_gross(self) -> float | None:
        """Returns the most recent price from self.price_history."""
        if self.price_history:
            prices_sorted = sorted(
                self.price_history,
                key=lambda item: item.valid_from,
                reverse=True
            )
            current_price = prices_sorted[0].unit_price_gross
            return current_price
        else:
            return None

@ErikFub

@ErikFub in your example slug is available as a property on class but is not actually made as a column in the database table. Is that what you are seeing as well?

I believe OPs original question wanted the slug field/value to be persisted to the database.

I'm using the latest sqlmodel version 0.14 and pydantic 2.0.

@gavinest

@gavinest True that, I missed the part that it should persist in the database - thanks for pointing that out! I've adjusted my answer accordingly

Use my PR #801

we want to do something similar where we have a

class Document:
  id: ...
  text: ...
  author: #expensively calculated by parsing the text through an LLM, ideally only ever happens once
  key_points: # same as above

it would be amazing if we could simply do some form of @computed annotation on those fields together with a function that gets as input the object and can then calculate the value which then gets returned and persisted so the next time it doesn't get re-calculated

Thanks all for discussion!

I think this issue should be closed. Author wanted to create real column in DB that would be automatically calculated (updated) on row update.
column_property doesn't do that. It doesn't create real column, but just calculates the value during the query time on DB side.

So, if it's strict that real column should be created, than let's start from trying to come up with the code example with pure SQLAlchemy.
If that requirement is not strict, let's start from defining requirements.
Anyway, I would suggest closing this and opening a discussion if needed.

If we understand that there is no appropriate way to achieve what is needed, we can create a feature request with clear description.

Navigation Menu

Repository navigation

How to create computed columns ? #150

First Check

Commit to Help

Example Code

Description

Operating System

Operating System Details

SQLModel Version

Python Version

Additional Context

Workaround: Generating a dynamic property (this does NOT add a column to the database)

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

How to create computed columns ? #150

Description

First Check

Commit to Help

Example Code

Description

Operating System

Operating System Details

SQLModel Version

Python Version

Additional Context

Activity

sorasful commented on Oct 30, 2021

FabiEbert commented on Nov 1, 2021

sorasful commented on Nov 2, 2021

dandiep commented on Dec 8, 2021

angel007sya commented on Feb 10, 2022

figaro-smartotum commented on May 30, 2023

Matthieu-LAURENT39 commented on Jul 21, 2023

samidarko commented on Feb 3, 2024

ErikFub commented on Feb 7, 2024

Workaround: Generating a dynamic property (this does NOT add a column to the database)

gavinest commented on Feb 11, 2024

ErikFub commented on Feb 11, 2024

50Bytes-dev commented on Feb 14, 2024

pascalwhoop commented on Jun 10, 2025

YuriiMotov commented on Aug 27, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions