Implement fulltext search with SQLModel and PostgresSQL

[ppvan]

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, create_engine


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)

SQLModel.metadata.create_all(engine)

Description

Hi, I read all the docs and user guide but can't figure out how to implement fulltext search in sqlmodel. For example, search all hero contains name "spider". I'd like to perform some analayis like ascii folding if possible.

Thanks in advanced.

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.14

Python Version

Python 3.11.6

Additional Context

No response

[machinehead]

I was wondering about the same thing and just found this SO answer: https://stackoverflow.com/a/9552871/12423765

Translated it into roughly this sqlmodel code below, and it seems to work!

(I omitted all the irrelevant details of my code from this, please let me know if I failed to include something important)

from typing import Sequence, Self
from sqlalchemy.dialects.postgresql.ext import to_tsquery
from sqlmodel import Field, SQLModel, select, col
from sqlmodel.ext.asyncio.session import AsyncSession

class Note(SQLModel, table=True):
    content: str = Field(nullable=False)

    @classmethod
    async def search_by_text(cls, session: AsyncSession, query: str) -> Sequence[Self]:
        return (
            await session.exec(
                select(cls).filter(col(cls.content).op("@@")(to_tsquery(query)))
            )
        ).all()