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