SqlModel - specify index type (B-Tree, Hash, etc.)

I'm looking at documentation for SQLModel. The python library is written by the author of FastAPI, so I'm eager to leverage the seamless db integration via SqlModel.

In the docs, he illustrates what an index is and how it works by graphically depicting binary search. So I think that it's reasonable to conclude that SQLModel can index on a given variable via B-Tree by default.

B-Trees (and binary search) make sense for range and inequality based queries. But equality or identity queries (name=='John') can be greatly sped up through hash-based indices. But if you could only support one index type, B-Tree obviously makes more sense than hash.

Here's an example

from sqlmodel import Field, Session, SQLModel, create_engine, select


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

But suppose that I want to use a Hash index, can this be done through some SQLModel configuration?

Under the hood, SQLModel is built on top of SQLAlchemy. Perhaps as a backup, one should drop down a level and define the index type through SQLAlchemy. Ideally, building Db-provider specific solutions (ex: Postgres) can be avoided.

Solution

SQLModel allows us to use SQLAlchemy's __table_args__ attribute in our model definitions. So, for

class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    __table_args__ = (
        Index("ix_hero_name", "name", postgresql_using="hash"),
    )

with

connection_url = "postgresql://scott:[email protected]/mydb"
engine = create_engine(connection_url)
SQLModel.metadata.create_all(engine)

SQLAlchemy generates

CREATE TABLE hero (
    id SERIAL NOT NULL, 
    name VARCHAR NOT NULL, 
    secret_name VARCHAR NOT NULL, 
    PRIMARY KEY (id)
)
CREATE INDEX ix_hero_name ON hero USING hash (name)

The postgresql_using= option is ignored by other dialects, so

connection_url = "sqlite:///database.db"
engine = create_engine(connection_url)
SQLModel.metadata.create_all(engine)

produces

CREATE TABLE hero (
    id INTEGER NOT NULL, 
    name VARCHAR NOT NULL, 
    secret_name VARCHAR NOT NULL, 
    PRIMARY KEY (id)
)
CREATE INDEX ix_hero_name ON hero (name)