Autogenerate with a gin index on jsonb field with postgresql

[JabberWocky-22]

I have a table as shown in the following code

import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import JSONB

metadata = sa.MetaData()
table = sa.Table(
    "account",
    metadata,
    sa.Column("id", sa.Integer, primary_key=True, autoincrement=True),
    sa.Column("extra", JSONB()),
)
table.append_constraint(
    sa.Index(
        "ix_account_nickname",
        table.c.extra["user_info"]["nickname"].astext.label("nickname"),
        postgresql_using="gin",
        postgresql_ops={"nickname": "gin_trgm_ops"},
    )
)

If I try to create a revision with autogenreate, the upgrade ops would like follows:

# ### commands auto generated by Alembic - please adjust! ###
    op.create_table('account',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('extra', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index('ix_account_nickname', 'account', [sa.text("(((extra -> 'user_info')) ->> 'nickname')")], unique=False, postgresql_using='gin', postgresql_ops={'nickname': 'gin_trgm_ops'})

The create index statement is not correct since the text expresion doesn't have a label, so the oeprator class will take no effect.(Actually not matters much since I only need to adjust once)

Besides, if I try to create a revision after this migration, the index will always be dropped and created.

def upgrade()
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index('ix_account_nickname', table_name='account', postgresql_using='gin')
    op.create_index('ix_account_nickname', 'account', [sa.text("(((extra -> 'user_info')) ->> 'nickname')")], unique=False, postgresql_using='gin', postgresql_ops={'nickname': 'gin_trgm_ops'})
    # ### end Alembic commands ###

def downgrade()
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index('ix_account_nickname', table_name='account', postgresql_using='gin', postgresql_ops={'nickname': 'gin_trgm_ops'})
    op.create_index('ix_account_nickname', 'account', [sa.text("((extra -> 'user_info'::text) ->> 'nickname'::text)")], unique=False, postgresql_using='gin')
    # ### end Alembic commands ###

I can use include_name to filter this index, but it seems a bug with index expression comparing.

[JabberWocky-22]

code to repoduce

import sqlalchemy as sa
from alembic.autogenerate.api import produce_migrations
from alembic.autogenerate.api import render_python_code
from alembic.migration import MigrationContext
from sqlalchemy.dialects.postgresql import JSONB

metadata = sa.MetaData()
table = sa.Table(
    "account",
    metadata,
    sa.Column("id", sa.Integer, primary_key=True, autoincrement=True),
    sa.Column("extra", JSONB()),
)
table.append_constraint(
    sa.Index(
        "ix_account_nickname",
        table.c.extra["user_info"]["nickname"].astext.label("nickname"),
        postgresql_using="gin",
        postgresql_ops={"nickname": "gin_trgm_ops"},
    )
)
engine = sa.create_engine("postgresql://user:pass@host:1234/db")
conn = engine.connect()
with conn.begin():
    metadata.drop_all(conn)
    # metadata.create_all(conn)
mc = MigrationContext.configure(connection=conn)
migration_script = produce_migrations(mc, metadata)
upgrade_code = render_python_code(migration_script.upgrade_ops, migration_context=mc)
print(upgrade_code)
downgrade_code = render_python_code(
    migration_script.downgrade_ops, migration_context=mc
)
print(downgrade_code)

[CaselIT]

Hi,

thanks for reporting. what should be the correct rendering of the index in the migration?

Regarding the index wrong detection this was already reported here #1390

[JabberWocky-22]

This one works but I think it's hard to render.

def upgrade():
    table = op.create_table("account", ...)
    nickname = table.c.extra["user_info"]["nickname"].astext.label("nickname")
    op.create_index(
        index_name="ix_account_nickname",
        table_name="account",
        columns=[nickname],
        postgresql_using="gin",
        postgresql_ops={"nickname": "gin_trgm_ops"},
    )

https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#operator-classes
According to the doc, maybe there is some way to give a label to text?

[CaselIT]

You can give text a label while rendering it but I'm not sure how easy it would be.

@zzzeek do you think this case is worth supporting?

[JabberWocky-22]

If I'm not mistaken, giving text a label would raise a NotImplementedError :(

[CaselIT]

indeed it does. Literal column is likely an option here instead of text

[JabberWocky-22]

Literal column works fine, but will similarly render to text in autogenerate.
Anyway, It's a simpler a way to create index.
Thanks