I recently ran into the following error when adding some indices to a PostgreSQL database from within an Alembic migration:

CREATE INDEX CONCURRENTLY cannot run inside a transaction block

Which makes sense, particularly if you're creating the table with the index in the same migration (transaction block) -- how can `CREATE INDEX` know anything about a table that doesn't exist yet?

There are a variety of SQL schema transforms (a.k.a. DDL statements) that work this way. Alembic migrations are always run inside a transaction, and breaking out a given statement is not immediately obvious. Luckily, Alembic provides a way for us to end the transaction early and execute statements on their own ("autocommit"):

def upgrade():
    with op.get_context().autocommit_block():
        op.execute("ALTER TYPE mood ADD VALUE 'soso'")

It's important to note that any statements before the autocommit block will be committed! So it is probably best to do this last, at the end of your migration, in order to keep things simple.

And while this blog post speaks of PostgreSQL, this should apply to any database that SQLAlchemy supports!

More info: Alembic Documentation
H/T Stack Overflow