How to add sqlalchemy functional indexes to columns?

[gregsifr]

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

# How to do the SQLModel equivalent of:
from sqlalchemy import func, Index

Index('someindex', func.lower(mytable.c.somecol))

Description

How to add functional indexes to a table?

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.3

Python Version

3.8.11

Additional Context

No response

[ludokriss]

You should be able to define the functional indexes as you would with sqlalchemy orm, basically doing the following:

from sqlmodel import Relationship, SQLModel, Field, create_engine, Index
from typing import Optional
import uuid

class User(SQLModel, table=True):
    __tablename__ = "users"
    id: Optional[uuid.UUID] = Field(primary_key=True,nullable=False,default_factory=uuid.uuid4)
    name: str = Field()
    
name_functional_index=Index('name_index', User.name.desc())

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

engine = create_engine(sqlite_url, echo=True)

SQLModel.metadata.create_all(engine)

Here you would get a functional index on the user name using a descending value.