Using hybrid_property of SQLAlchemy leads to CompileError · Issue #299 · fastapi/sqlmodel · GitHub

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

I faced with the same issue

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

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

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

Same issue over here @kozickikarol, any workarounds?

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?

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

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.

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

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

@geudrik use my PR #801

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
    )

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

Using hybrid_property of SQLAlchemy leads to CompileError · Issue #299 · fastapi/sqlmodel · GitHub

Repository navigation

Using hybrid_property of SQLAlchemy leads to CompileError #299

First Check

Commit to Help

Example Code

Description

Operating System

Operating System Details

SQLModel Version

Python Version

Additional Context

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Using hybrid_property of SQLAlchemy leads to CompileError · Issue #299 · fastapi/sqlmodel · GitHub

Repository navigation

Using hybrid_property of SQLAlchemy leads to CompileError #299

Description

First Check

Commit to Help

Example Code

Description

Operating System

Operating System Details

SQLModel Version

Python Version

Additional Context

Activity

alexbojko commented on Apr 15, 2022

lhs950204 commented on May 14, 2022

alexbojko commented on May 14, 2022

kozickikarol commented on Sep 17, 2022

Augustinio commented on Sep 19, 2022

antont commented on Oct 20, 2022

Elyasomer commented on Oct 17, 2023

jjjacksn commented on Oct 30, 2023

geudrik commented on Jan 24, 2024

christianholland commented on Feb 8, 2024

50Bytes-dev commented on Feb 14, 2024

marianafmedeiros commented on Jul 11, 2024

sneakers-the-rat commented on Mar 1, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions