pg_trgm migrations with alembic and SQLAlchemy

I'm a Rails engineer working on a Flask app. We use alembic and SQLAlchemy against a Postgres database. Even though I miss ActiveRecord dearly, for the most part what I know has mapped pretty well.

Until today.

I've been trying to create a pg_trgm index on one of our tables, which is easy enough to do if I just drop some raw SQL into a migration. But we can do better–and besides, an in-house code quality tool we use fails to reconcile the model with the state of the database.

The solution lies somewhere between this StackOverflow answer and this gist.

What ended up working for me was:

• Define the index in the model using __table_args__, and then
• Let alembic generate a migration automatically from it.

Genericizing the model to not be insanely domain-specific, it might look something like this:

class Book():
  __tablename__ = "book"
  __table_args__ = (Index(
      "ix_book_author_name_trgm", "author_name",
      postgresql_ops={"author_name": "public.gin_trgm_ops"},
      postgresql_using="gin"
    ),
  )
  book_id = Column(PostgreSQLUUID, primary_key=True, default=uuid_gen)
  author_name = Column(Text, index=True)

The __table_args__ bit is the magic sauce, and the variable must be a tuple or dict (or None...), so the trailing comma is needed here. (Did I mention I miss Ruby sometimes?) In terms of attributes:

• ix_book_author_name_trgm is the name of the index. You can call it whatever you want.
• author_name is the column name.
• postgresql_ops is a Postgres-specific field; author_name is the affected column name, and public.gin_trgm_ops is the schema name (public) and index operation (gin_trgm_ops) to use. Without the schema name, you may run into this error. (Your schema name might not be "public".)
• postgresql_using="gin" tells Postgres this is of the generalized inverted index (GIN) class.

And then, running poetry run alembic --config ./alembic.ini revision --autogenerate set me up with a migration to apply this to the database.

It's very possible there's room for improvement here, but this got things working. The two examples I had come across happened to have columns named title and description, which confused me initially into thinking that "title": "public.gin_trgm_ops" was (for some reason) the key name postgresql_ops looked for, or that "description" was a placeholder for a comment about the migration, leading to me just omitting it. 🤦

Hope my struggles help someone in the future. (Possibly my future self, even...)

Cover photo by Uriel SC on Unsplash