Full Stack Structure #1

Coherent Python and PostgreSQL Enums Using SQLAlchemy

A singe source of truth for database and runtime enumerations

Brendan Le
Uncountable Engineering
2 min readJun 4, 2021

--

Illustration by Cathy Yang

#Uncountable is hiring! To help us accelerate the future of materials development, check out our Careers page.

InIn Python, enums are a powerful way of restricting a variable to a set of constants, and can help reduce errors over strings. At Uncountable, we deal with a lot of different data types, and we’ll often use an enum to define every possible format that a piece of data can take on. For example, a description of the format for an individual datatype might look like:

TThis “quantity type” information about the format of data will often need to be stored in the database on a per-row basis. In order to ensure that the value stored in the database only takes on the same possible values, we can define the same enum in the database that we did in python. Enums are available in PostgreSQL as a first-class feature, and can help reduce errors between a web service and the database it talks to by requiring that a field in the database only take on a limited set of values. To define the same enum in PostgreSQL that we did in Python, you would write something like this:

Or, equivalently in SQLAlchemy:

SSince data are constantly flowing between the Python layer and the database layer, it becomes very useful to have a single enum definition for both Python and SQLAlchemy, to serve as a single source of truth. We can accomplish this with the following code snippet:

TThis piece of code derives the SQLAlchemy enum definition from Python one, helping to keep our code DRY in the process. Additionally, having this enum defined in Python allows us to use the definition in type hints. When combined with a type checker like MyPy, this will ensure that we only ever insert valid values into the database.

#Uncountable is hiring! To help us accelerate the future of materials development, check out our Careers page.

Brendan Le graduated from Brown University with a degree in Applied Math and Computer Science. He is currently a full stack engineer at Uncountable, where he works on features such as the Machine Learning tools on the platform. In his spare time, he is likely playing video games or watching YouTube.

--

--