How to create computed columns ?

[sorasful]

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

[sorasful]

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')]

[FabiEbert]

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"
}

[sorasful]

@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

[dandiep]

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."

[angel-langdon]

Related to this issue #240

[figaro-smartotum]

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

[Matthieu-LAURENT39]

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

[samidarko]

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
            )

[ErikFub]

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

[gavinest]

@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.

[ErikFub]

@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

[50Bytes-dev]

Use my PR #801

[pascalwhoop]

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