Add index to a column that did not have an index when created

[rfan-debug]

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 datetime import datetime
from typing import Dict, Optional

from sqlalchemy import BLOB, Column, JSON
from sqlmodel import Field, SQLModel, create_engine

# Table schema when table was created 
class ExperimentConfig(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    variant_name: str
    owner: str
    description: bytes = Field(default=None, sa_column=Column(BLOB))
    timestamp: datetime

# Updated schema when i found out many queries using timestamp and variant_name column as filters. 
class ExperimentConfig(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    variant_name: str = Field(index=True)
    owner: str
    description: bytes = Field(default=None, sa_column=Column(BLOB))
    timestamp: datetime = Field(index=True)

# Then I tried to run the following code with my local MySQL backend again

db_url = f"mysql+pymysql://user:password@localhost:3306/mydb"
engine = create_engine(db_url, echo=False, future=False)
SQLModel.metadata.create_all(engine)

# However, the index wasn't created at this point. I verified that the index did not exist through the following SQL query. 
# `SHOW INDEX IN experimentconfig`

Description

I have had a table that contains ~10K rows of data. Then i decided to add indexes to two columns in the table by simply updating Field(index=True) on those two columns. However, those indexes cannot be updated even though I rerun the SQLModel.metadata.create_all(engine). Is there any way to operate the DB from SQLModel manually to add indexes to those two columns?

From my perspective, addressing this question could benefit many users because it is very common that we add indexes later after analyzing a database read patterns.

In addition, I am not sure this should be asked here or in SQLalchemy. If this is a wrong place to ask such a question, please let me know!

Operating System

macOS

Operating System Details

It is a macbook pro 2019 with intel x86_64 i7 processor. Thus I don't think my platform could be the issue.

SQLModel Version

0.0.8

Python Version

Python 3.10.9

Additional Context

No response

[ben05allen]

Following this stackoverflow answer, you are able to create a new index with;

from datetime import datetime
from typing import Dict, Optional

from sqlalchemy import BLOB, Column 
from sqlalchemy import Index # new
from sqlmodel import Field, SQLModel, create_engine

# Table schema when table was created 
class ExperimentConfig(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    variant_name: str
    owner: str
    description: bytes = Field(default=None, sa_column=Column(BLOB))
    timestamp: datetime

expcon_var_name_index = Index('expcon_var_name_index', ExperimentConfig.variant_name) # new

db_url = "sqlite:///database.db" # changed
engine = create_engine(db_url, echo=False, future=False)
# SQLModel.metadata.create_all(engine)
expcon_var_name_index.create(bind=engine) # add index

[rfan-debug]

Great. It worked for me. Thanks for the detailed guide!