SQLAlchemy / Alembic raw SQL for adding index

Question

I'd like to use the following raw SQL to create an index in PostgreSQL:

CREATE INDEX ix_action_date ON events_map ((action ->> 'action'), date, map_id);

I tried to put this line into the model class's __table_args__ part, but I couldn't. Then I simply solved it by using raw SQL in Alembic migration.

conn = op.get_bind()
conn.execute(text("CREATE INDEX ..."))

and just using a dummy index in __table_args__ like:

Index('ix_action_date')

My only problem is that Alembic doesn't accept the dummy index with the same name, and every time I run a revision --autogenerate, it tells me the following:

SAWarning: Skipped unsupported reflection of expression-based index ix_action_date
  % idx_name)

and then it adds the autogenerated index to the migration file:

op.create_index('ix_action_date', 'events_map', [], unique=False)

My question is:

1. How can I write raw SQL into a __table_args__ Index?

2. How can I really make my dummy index concept work ? I mean an index which is only compared by name?

Answer 1

How can I write raw SQL into a __table_args__ Index?

To specify formula indexes, you have to provide a text element for the expression

example:

class EventsMap(Base):
    __tablename__ = 'events_map'
    __table_args__ = (Index('ix_action_date', text("(action->>'action'), date, map_id")),)
    map_id = Column(Integer, primary_key=True)
    date = Column(DateTime)
    action = Column(JSONB)

How can I really make my dummy index concept work ? I mean an index which is only compared by name?

It seems unnecessary to make your dummy index concept work. Either specify the full index expression in the __table_args__ as I've shown above, or omit it completely from the model & delegate index creation as a database migration handled by sqlalchemy.