Creating a new enum type in sqlalchemy with the type decorator

I wasn’t satisfied with the built-in EnumType that SQLAlchemy offers for column definitions because it’s limited to using the native sql enum type or a large varchar. The native sql enum isn’t a bad choice if you’re using MySQL, but I’ve found the postgres (which is what I’m using) implementation to be too rigid since you can’t easily alter the list of possible values. The varchar is overkill and takes up too much storage space.

I settled on storage using a small integer due to its small footprint while still allowing a large bucket of values. I was initially planning on using the integers in the code and trying to remember what each integer represented but my feeble mind wasn’t up for the challenge. So I found the SQLAlchemy TypeDecorator class which makes it dead simple to create a custom column type:

Using the new type is very similar to the generic EnumType:

#in your table declaration
animal_table = sa.Table("title", meta,
    sa.Column("name", sa.types.Unicode(31), nullable=False),
    sa.Column("type", EnumIntType(("dog", "cat", "narwhal")), nullable=False),
    )

#in a query
session.query(Animal).filter(Animal.type == "cat").all()