Questions on how to best add a complex index in the context of auto-generating migrations

[ekeric13]

So I had a complex index that I didn't know how to model in the sqlAlchemy ORM and I am wondering how to best handle it.
I have 2 questions on how to best handle it but first some context.

So in alembic i created a migration and did:

    op.execute(
        """
        CREATE UNIQUE INDEX unique_started_or_finished_per_user
        ON "widget" (user_id)
        WHERE status IN ('started', 'finished');
        """
    )

This worked fine until I auto-generated a new migration in which alembic auto-generated the migration to drop the index I created.

So I guess

1. How do I get auto-generation to ignore my own custom migration?

After doing some googling it seemed like one solution is to simply define the index in the models.
Both SO and chatGPT lead me to something like this:
https://stackoverflow.com/a/10061143

    __table_args__ = (
        Index(
            'unique_started_or_finished_per_user',
            'user_id',
            unique=True,
            postgresql_where=(status.in_(['started', 'finished'])),
        ),
    )

But checking the 2.0 version of the docs it seems it wants me to do something like this:
https://docs.sqlalchemy.org/en/20/core/constraints.html#indexes

Index(
    'unique_started_or_finished_per_user',
    'user_id',
    postgresql_where=(status.in_(['started', 'finished'])),
    unique=True
)

so my second question is
2. How do I declaratively write this in my models if that is the best way to go about it?

[zzzeek]

So I had a complex index that I didn't know how to model in the sqlAlchemy ORM and I am wondering how to best handle it. I have 2 questions on how to best handle it but first some context.

So in alembic i created a migration and did:

    op.execute(
        """
        CREATE UNIQUE INDEX unique_started_or_finished_per_user
        ON "widget" (user_id)
        WHERE status IN ('started', 'finished');
        """
    )

This worked fine until I auto-generated a new migration in which alembic auto-generated the migration to drop the index I created.

So I guess

1. How do I get auto-generation to ignore my own custom migration?

use the include_object hook: https://alembic.sqlalchemy.org/en/latest/api/runtime.html#alembic.runtime.environment.EnvironmentContext.configure.params.include_object

After doing some googling it seemed like one solution is to simply define the index in the models. Both SO and chatGPT lead me to something like this: https://stackoverflow.com/a/10061143

    __table_args__ = (
        Index(
            'unique_started_or_finished_per_user',
            'user_id',
            unique=True,
            postgresql_where=(status.in_(['started', 'finished'])),
        ),
    )

you could do that also

But checking the 2.0 version of the docs it seems it wants me to do something like this: https://docs.sqlalchemy.org/en/20/core/constraints.html#indexes

Index(
    'unique_started_or_finished_per_user',
    'user_id', 'status',
    postgresql_where=(status.in_(['started', 'finished'])),
    unique=True
)

what's different, the "status" column? I would omit that since it is not in your index definition

[ekeric13]

Thanks for the reply.

Yeah the status column being part of the index is a typo.. It is not a compound index. Just unique user_id where "status" in ('started', 'finished'). edited my original quesiton.

Is the first or second method preferrable? or doesn't matter?

include_object hook seems confusing so i will go with just declare it in the code.

edit: To be more clear does should Index() function be nested under __table_args__ or outside of the class?

[zzzeek]

Thanks for the reply.

Yeah the status column being part of the index is a typo.. It is not a compound index. Just unique user_id where "status" in ('started', 'finished'). edited my original quesiton.

Is the first or second method preferrable? or doesn't matter?

it doesn't matter except that autogenerate support for correctly more elaborate indexes like that is a newer feature. if it compares correctly, then it's fine :)

include_object hook seems confusing so i will go with just declare it in the code.

edit: To be more clear does should Index() function be nested under __table_args__ or outside of the class?

that part doesn't matter, if the Index is outside, it is easier if your Index has references to column-bound tables, so it has some way to associate with the table, that is, Index("index_name", Myclass.column, ...). I'd personally probably prefer putting it in __table_args__() but historically that wasn't required.