Alembic Migration when going from SQLAlchemy declarative_base to SQLModel adding indexes

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

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

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.

@peterHoburg

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. 🎉

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.

Merge pull request fastapi#9 from NEXT-JP/patch-1

Navigation Menu

Repository navigation

Alembic Migration when going from SQLAlchemy declarative_base to SQLModel adding indexes #9

First Check

Commit to Help

Example Code

Description

Operating System

Operating System Details

SQLModel Version

Python Version

Additional Context

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Alembic Migration when going from SQLAlchemy declarative_base to SQLModel adding indexes #9

Description

First Check

Commit to Help

Example Code

Description

Operating System

Operating System Details

SQLModel Version

Python Version

Additional Context

Activity

CaselIT commented on Aug 25, 2021

falkben commented on Nov 20, 2021

tiangolo commented on Dec 28, 2021

github-actions commented on Jan 8, 2022

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions