fts ?

[rraammiinn]

does it support full text search ?

[sinisaos]

@rraammiinn Piccolo ORM supports basic like/ilike for text search. For more complex searches (with indexes etc.), you should use raw sql queries where you can use any Postgres or SQLite database features.
EDIT: An example of using Postgres full-text search with Piccolo ORM, if anyone needs that functionality.

# example table
class Article(Table):
    title = Varchar()
    content = Text()
    created_at = Timestamptz()

# Create column search (tsvector type) column for fts on title snd content columns.
# This can be done via migrations or a script.
Article.raw(
    "ALTER TABLE article ADD search tsvector GENERATED ALWAYS AS "
    "(to_tsvector('english', title) || ' ' || to_tsvector('english', content)) STORED;"
).run_sync()
# create GIN index for search column
Article.raw("CREATE INDEX idx_search ON article USING GIN(search);").run_sync()

# usage with FastAPI
app = FastAPI()

# create Pydantic model for Article Table
ArticleModelOut: t.Any = create_pydantic_model(
    table=Article, include_default_columns=True, model_name="ArticleModelOut"
)

@app.get("/articles-fts/", response_model=t.List[ArticleModelOut])
async def articles_search_fts(q: str):
    # Run a full text search query with results ranked in descending order
    # (most relevant result goes to top), using Piccolo WhereRaw and OrderByRaw
    # to mix Piccolo queries with raw sql for full text search functions.
    results = (
        await Article.select()
        .where(
            WhereRaw(
                f"search @@ websearch_to_tsquery('english', '{q}')",
            )
        )
        .order_by(
            OrderByRaw(f"ts_rank(search, websearch_to_tsquery('english', '{q}'))"),
            ascending=False,
        )
    )
    return results

[sarvesh-deserve]

Thanks @sinisaos.
This is helpful