Alembic autogenerate wants to remove foreign key indexes

[anentropic]

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

class User(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    username: str = Field(unique=True)
    is_admin: bool = False

    games: List["GameSession"] = Relationship(back_populates="user")

class GameSession(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    user_id: int = Field(foreign_key="user.id")
    user: User = Relationship(back_populates="games")

Description

I initially created my db using SQLModel.metadata.create_all(engine)

This added a foreign key index on the above table gamesession_user_id as you would expect

Now I am adding Alembic migrations to my project

In my alembic env.py I have imported my models and given the SQLModel metadata:

from sqlmodel import SQLModel
from myapp.repository import GameSession, User
target_metadata = SQLModel.metadata

when I run alembic check or alembic revision --autogenerate I get:

INFO  [alembic.autogenerate.compare] Detected removed index 'gamesession_user_id' on 'gamesession'

and for the latter the generated revision drops the index

So it seems like Alembic doesn't understand the foreign keys on SQLModel models properly

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

Python 3.11.1

Additional Context

No response

[anentropic]

the same problem occurs with field that has a unique index:

class User(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    username: str = Field(unique=True)

alembic check shows:

INFO  [alembic.autogenerate.compare] Detected removed index 'user_username' on 'user'
ERROR [alembic.util.messaging] New upgrade operations detected:
[('remove_index', Index('user_username', Column('username', VARCHAR(), table=<user>, nullable=False)))]

i.e. SQLModel.metadata.create_all(engine) created the index for the unique field as expected, but Alembic thinks the model does not specify an index so wants to remove it from the db

[anentropic]

the db is SQLite in case that matters

[anentropic]

Ok, found the reason for this:

• I originally created the tables with another ORM and then migrated to SQL model
• previous ORM automatically created an index on the FK fields, but SQLModel does not (?) ... that's fine now I understand (although https://www.sqlite.org/foreignkeys.html#fk_indexes recommends that FK fields should be indexed, makes sense)
  • perhaps the docs might suggest this? https://sqlmodel.tiangolo.com/tutorial/relationship-attributes/define-relationships-attributes/#declare-relationship-attributes
• similarly, my username: str = Field(unique=True) field has two unique indexes in the db, one created by Peewee and one by SQLModel, with different names... so Alembic just wants to remove one of them

so the fix for me was to add index=True to my foreign key fields, and then let Alembic do the changes it wanted to do