Postgres enum values added in early migrations not available in later migrations when running 'upgrade head' on empty database

[stevegoss-komodo]

In a project, we have a number of migrations. The latest one I added creates a function in Postgres using op.execute that references an enum type/value created in a much earlier migration. This works fine to migrate existing database, but when running 'alembic upgrade head' against a fresh database (running CI tests, setting up a new local dev environment, etc) it has an error:

sqlalchemy.exc.OperationalError: (psycopg2.errors.UnsafeNewEnumValueUsage) unsafe use of new value "MY_ENUM_VALUE" of enum type myenumtype
LINE 7:             when a = 'MY_ENUM_VALUE'::myenumtype then...
                             ^
HINT:  New enum values must be committed before they can be used.

There is an early migration that creates the myenumtype Enum type. There is a later migration that adds the "MY_ENUM_VALUE":

op.execute("ALTER TYPE myenumtype ADD VALUE IF NOT EXISTS 'MY_ENUM_VALUE'")

Then the latest migration creates a function that references that value/type. I can create a new database if I run all the migrations before the latest one separately, then run the latest:

alembic upgrade "penultimate"   # creates myenumtype and adds MY_ENUM_VALUE
alembic upgrade head  # adds function that references 'MY_ENUM_VALUE'::myenumtype

Is there a way to structure the migrations to allow for running alembic upgrade head on an empty database, or will I need to update all the CI scripts and local dev setup scripts to run the migration in two commands moving forward?

[zzzeek]

as the hint indicates, that alter type needs to be committed before it can be referenced. the autocommit_block construct was added to support exactly this use case.

[stevegoss-komodo]

Ah, perfect, thank you! I tried to find a solution in the docs, but I missed this.

[CaselIT]

There is also the option of running a transaction per migration file, instead of a single one: https://alembic.sqlalchemy.org/en/latest/api/runtime.html#alembic.runtime.environment.EnvironmentContext.configure.params.transaction_per_migration