Alembic Migration when going from SQLAlchemy declarative_base to SQLModel adding indexes

[peterHoburg]

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

# Original SQLAlchamy Schema

import uuid

from sqlalchemy import Column, DateTime
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import declarative_base


Base = declarative_base()


class User(Base):
    __tablename__ = "user"
    id: uuid.UUID = Column(UUID(as_uuid=True), primary_key=True, unique=True, default=uuid.uuid4)
    created_at = Column(DateTime, nullable=False)
    updated_at = Column(DateTime, nullable=False)


# Updated to sqlmodel Schema

import uuid
from datetime import datetime

from sqlmodel import Field, SQLModel


class User(SQLModel, table=True):
    id: uuid.UUID = Field(primary_key=True, default=uuid.uuid4)
    created_at: datetime
    updated_at: datetime


# Resulting Auto Migration

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('user', 'id',
               existing_type=postgresql.UUID(),
               nullable=True)
    op.create_index(op.f('ix_user_created_at'), 'user', ['created_at'], unique=False)
    op.create_index(op.f('ix_user_id'), 'user', ['id'], unique=False)
    op.create_index(op.f('ix_user_updated_at'), 'user', ['updated_at'], unique=False)
    # ### end Alembic commands ###

Description

Using Postgres 13, alembic 1.6.5, and SQLAlchemy 1.4.22. All deps locked with poetry and run inside a docker container.

• Create a simple auto migration with Alembic based on SQLAlchemy declarative_base models alembic revision --autogenerate
• Upgrade DB with migration
• Change schema to use sqlmodel and run automigration again.

The resulting migration includes a bunch of unnecessary indexes.

Operating System

Linux

Operating System Details

Ubuntu in Docker on Linux host

FROM python@sha256:8f642902ba368481c9aca0a100f08daf93793c6fa14d3002253ea3cd210383a7

SQLModel Version

0.0.3

Python Version

3.9.6

Additional Context

No response

[CaselIT]

It seems to add an index by default
https://github.com/tiangolo/sqlmodel/blob/af03df88acda8c5fb3e0e755b0ab0517a2c8aabb/sqlmodel/main.py#L419-L420

I think it's just a typo there

[falkben]

Until this gets fixed, if you don't want to set index=False on every single attribute, you can create a decorator on Field function and have it override the index param setting the default to False. This works as long as you make sure to call Field() for each of your model attributes.

def set_default_index(func):
    """Decorator to set default index for SQLModel
    Can be removed when https://github.com/tiangolo/sqlmodel/pull/11 is merged
    """

    @wraps(func)
    def inner(*args, index=False, **kwargs):
        return func(*args, index=index, **kwargs)

    return inner


# monkey patch field with default index=False
# this works as long as we always call Field()
Field = set_default_index(Field)

To be used like:

class Category(SQLModel, table=True):
    pk: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    description: str = Field()

Creates a table with name column with an index but description does not.

Without calling Field() for description, you'd still end up with an index.

[tiangolo]

Thank you @peterHoburg for bringing it up, thanks @CaselIT for the PR, and thanks @falkben for the workaround! 🚀 🍰

This should be solved in #205, the new docs explaining indexes are here: https://sqlmodel.tiangolo.com/tutorial/indexes/

Indexes are now opt-in and not created by default. You can read more about it all in that PR.

It is now released as part of SQLModel 0.0.6. 🎉

[github-actions]

Assuming the original need was handled, this will be automatically closed now. But feel free to add more comments or create new issues or PRs.