Composite Indexes SQLModel

Question

I'm experimenting with SQLModel (https://sqlmodel.tiangolo.com/) and I get to the point that I had to create a composite index between several fields and I can't how to do it using SQLModel library.

Db Model

The only work around I found was to use directly sqlalchemy Index, rather than index=true (from SQLModel documentation when creating indexes for unique fields - )

class Jump(SQLModel, table=True):
    """
    SQL Table abstraction: Jump
    Contains data belonging to a connection between a questionnaire-version
    and another questionnaire-version
    """

    origin_name: str = Field(primary_key=True)
    origin_version: int = Field()
    destination_name: str = Field()

    __table_args__ = (
        Index(
            "compound_index_origin_name_version_destination_name",
            "origin_name",
            "origin_version",
            "destination_name",
        ),
    )

Expressly

Answer 1

This is not a "workaround". This is exactly how it is supposed to be done (as of now). The idea behind SQLModel presumably is to provide a toolkit for constructing table models that is very familiar to people coming from SQLAlchemy, while also providing most of the goodies coming from Pydantic models.

In some cases, SQLModel obviously does things differently and in some regards it tries to simplify existing interfaces. E.g. providing the foreign_key parameter on the Field constructor, so that you don't need to import and instantiate ForeignKey from SQLAlchemy.

But in this case, I really don't see the point in trying to change the existing tools. SQLAlchemy declarative ORM models allow you to set composite indices and other table parameters via the __table_args__ class-attribute. SQLModel's meta class inherits this feature. So why reinvent the wheel? 🙂

Unless you have an idea how to simplify this further. In that case, I am sure Sebastián will be more than happy about a corresponding PR or a suggestion in the issue tracker.

Pasqualepasqueflower