Using hybrid_property of SQLAlchemy leads to CompileError

[deZakelijke]

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

from sqlalchemy.ext.hybrid import hybrid_property  
from typing import Optional  
from sqlmodel import SQLModel, Field, create_engine  
  
from datetime import datetime  
                                    
                                      
class ObjectTable(SQLModel, table=True):  
    object_id: Optional[int] = Field(primary_key=True, default=None)  
    first_detection_time: datetime = Field(index=True)  
    last_detection_time: datetime = Field(index=True)  
      
    @hybrid_property                                    
    def detection_time(self) -> float:  
        return (self.last_detection_time - self.first_detection_time).total_seconds()  
      
    class Config:                 
        arbitrary_types_allowed = True  
                                                                                       
     
if __name__ == "__main__":  
    engine = create_engine("sqlite:///database.db")  
    SQLModel.metadata.create_all(engine)

Description

I am trying to create a hybrid property in an SQLModel class to allow more complex querying. Following the steps as described in the sqlalchemy docs here: https://docs.sqlalchemy.org/en/14/orm/extensions/hybrid.html I assumed that this would work and create a valid table. However, this code gives the error:
sqlalchemy.exc.CompileError: (in table 'objecttable', column detection_time'): Can't generate DDL for NullType(); did you forget to specify a type on this Column?
At first, I assumed that a type hint was missing so I added the float return type to the hybrid_property. I am not sure what the problem is exactly but I assumed that this code would yield a valid table.

Operating System

Linux

Operating System Details

Ubuntu 20.04

SQLModel Version

0.0.6

Python Version

Python 3.8.10

Additional Context

No response

[alexbojko]

I faced with the same issue

[lhs950204]

me too. solved @alexbojko ...? please...

[alexbojko]

Nope. @tiangolo that would be really cool if you can take a look into it.

[kozickikarol]

Any update regarding this issue?

My model:

class Model(SQLModel: table=True):
    active_until: Optional[datetime.datetime]
    
    class Config:
        arbitrary_types_allowed = True
        
    @hybrid_property
    def is_active(self) -> bool:
        return self.active_until is not None and self.active_until > datetime.datetime.now()

On 0.0.8 I've got an error:

... python3.10/site-packages/sqlmodel/main.py", line 414, in get_sqlachemy_type
raise ValueError(f"The field {field.name} has no matching SQLAlchemy type")
ValueError: The field is_active has no matching SQLAlchemy type

[Augustinio]

Same issue over here @kozickikarol, any workarounds?

[antont]

I also struggled with column_property and hybrid_property. Am now using workaround where just call custom code to add properties in code to my "read" objects.

Am also using normal Python properties where suitable, actually, wouldn't that is_active work as a plain @property?

[Elyasomer]

@tiangolo Bro is there any problem in pull request if it be merged it would really help :)

[jjjacksn]

I found a somewhat simple workaround: annotate the property as a class var (which pydantic will ignore)

from typing import ClassVar, Optional  

from datetime import datetime  

from sqlalchemy.ext.hybrid import hybrid_property  
from sqlmodel import SQLModel, Field, create_engine  
                                      

def _detection_time(self) -> float:
    return (self.last_detection_time - self.first_detection_time).total_seconds()  

class ObjectTable(SQLModel, table=True):  
    object_id: Optional[int] = Field(primary_key=True, default=None)  
    first_detection_time: datetime = Field(index=True)  
    last_detection_time: datetime = Field(index=True)  
      
    detection_time: ClassVar[float] = hybrid_property(_detection_time)
     
if __name__ == "__main__":  
    engine = create_engine("sqlite:///database.db")  
    SQLModel.metadata.create_all(engine)

NOTE: I think in this example you need to add an expression for this to work as expected in queries.

[geudrik]

@tiangolo any update on this yet? The above feels like a work-around (and I'm so far not able to hybrid props and comparators working using sqlmodel and fast api)

[christianholland]

@jjjacksn Could you please elaborate how to add an expression to use the detection_time in a query?

For instance:

select(ObjectTable).where(ObjectTable.detection_time > 1000)

[50Bytes-dev]

@geudrik use my PR #801

[marianafmedeiros]

Hi, all! In case this is any help:

The hybrid_property function has a keyword argument expr which you can assign the expression function.

Therefore, I believe something like this could work:

def _detection_time(self) -> float:
    return (self.last_detection_time - self.first_detection_time).total_seconds()

def _detection_time_exp(cls):
    return (cls.last_detection_time - cls.first_detection_time) 

class ObjectTable(SQLModel, table=True):
    object_id: Optional[int] = Field(primary_key=True, default=None)
    first_detection_time: datetime = Field(index=True)
    last_detection_time: datetime = Field(index=True)

    detection_time: ClassVar[float] = hybrid_property(
        _detection_time,
        expr=_detection_time_exp
    )

I've done this myself in a project and it works:

def lookup_name_property(self) -> str:
    return generate_lookup_name(self.name)


def lookup_name_exp(cls):
    """
    Same as `generate_lookup_name(self.legal_name)`, but in SQL
    """
    return func.regexp_replace(
        func.regexp_replace(
            cls.name,
            r"\W+",
            "_",
            "g"
        ),
        r"(^_|_$)",
        "",
        "g"
    )


class MyTable(SQLModel, table=True):
    """table description"""

    __table_args__ = {"schema": schema}
    __tablename__ = "my_table"

    name: str
    lookup_name: ClassVar[str] = hybrid_property(
        lookup_name_property,
        expr=lookup_name_exp
    )

[sneakers-the-rat]

another workaround is to mark the hybrid_property and hybrid_method types as ignored - If you subclass the SQLModel class it can work for all your models without needing to change the hybrid property code

from pydantic import ConfigDict
from sqlmodel import SQLModel as SQLModel_

class SQLModel(SQLModel_):
    model_config = ConfigDict(ignored_types=(hybrid_method, hybrid_property))

# use hybrid props as normal...