Alembic is an awesome library to setup migrations for your database schema which works really great. There is one small thing which it isn’t capable of though. When you upgrade an enum value in your code, it may get pretty confused.

This tutorial will show you how to overcome this weakness, so you don’t get stuck with upgrading the enum types yourself on the PostgreSQL server, but rather create the migration file which will do that for you.

Basic model

Let’s jump straight into the code. Let’s say we have the following enum and SQLAlchemy’s ORM model:

Then, generating Alembic’s new revision will result in the migration file containing our status field details with available values imprinted:

Updating the enum

What will happen if we want to add a new status to the enum definition? Let’s try and add one to our Statusenum.

Now, after creating next revision, migration file is going to be empty i.e. it will look like this:

So, how can we make sure the REJECTED value is added so the database knows that it should be a valid one? Fortunately for us in PostgreSQL 9.1 an ALTER TYPEdirective was added. With it, we can easily upgrade our enum type:

And what about downgrade to roll back the changes? In this case it would be a bit more work, but nothing that couldn’t be done:

This is still pretty easy, isn’t it?

Autocommit block

As you’ve probably noticed in the examples above I’ve executed all of the commands inside the autocommit block. Why is that?

If ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum type) is executed inside a transaction block, the new value cannot be used until after the transaction has been committed.

https://www.postgresql.org/docs/13/sql-altertype.html

As you can see from the documentation excerpt it’s necessary to run those commands within the autocommit block. Otherwise if our next migrations will try to use the new value (e.g. to insert new values to the database inside the migration) it will raise an exception as our updated type won’t be available until all migration files are properly executed.

Furthermore if you are using an older version of PostgreSQL (<12) you will have to add an autocommit block every time when using ALTER TYPE … ADD VALUE command, otherwise the migration will fail. The restriction is needed because using this directive may lead to possible index issues in the enum column.

Conclusion

In this tutorial we’ve successfully generated the migration file which can automatically upgrade enum types on PostgreSQL server. If you want to read more about this topic or Alembic migrations in general, take a look at an official Alembic documentation.

Thanks to Kamil, Marcin Struś, and Iwo Herka.