How to use LTree with SQLModel

[Lokeesh-27]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question 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 typing import Optional
from sqlmodel import Field, SQLModel

class HeroCategory(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    path: str  # Need to define the correct data type here

Description

⚙️ Challenge: The 'path' column in this model is intended to store hierarchical data, and I plan to use PostgreSQL's ltree data type for efficient querying. However, I'm uncertain about the correct data type to use in the SQLModel for the 'path' column that aligns with PostgreSQL's ltree.

📚 Reference: PostgreSQL ltree Documentation

🎯 Question:

1. What is the appropriate data type for the 'path' column in the SQLModel to ensure compatibility with PostgreSQL's ltree?
2. How can I effectively query this 'path' column in SQLModel, particularly for filtering based on hierarchical data?

Looking forward to insights and suggestions from the community!

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

3.10

Additional Context

No response

[cerealkill]

I have been very divided by using sqlmodel. It is a neat tool, but some basic stuff is missing - it does not support Array fields like text[] for example, and it does not give the user a simple way to extend the lib to add what it needed. ltree is a great example of a downfall. I am trying to have a where clause with a ltree expression and sqlmodel does not provide a simple way to do that. example: .where(Item.path @> path_str)

[cerealkill]

UPDATE: I was able to send direct where statements by using the following code:

from sqlalchemy import text
select(Item)
.where(text(f"item.path @> '{path_str}'")

So please document this in sqlmodel documentation.

PS: Array is still an issue I could not find a workaround.