This is only a proof of concept - using the template repo for easy of sharing across teams, no intention to merge this.
Changes
Modified how lookup values work making them derived directly from enums, but stored in the DB as text-foreign keys to handle validation at the DB layer.
Note that the implementation is very hacky, and poorly organized.
Context for reviewers
I'll start by saying that this idea might be more than necessary, but for DBs that we really want ease-of-use in-code, as well as validation of lookup values in the DB, it would cover that with minimal complexity.
This is an idea that joins together various different solutions to get something that I hope is somewhat intuitive. First, a bit of context:
Many systems require the use of lookup values, IDs that represent a limited set of potential values. For example, the US states (CA, NJ, MI, etc.), different claim statuses (open, closed, pending), race, ethnicity, address type, and many many more.
The biggest challenge of managing these values is:
avoiding duplication of defining the values (eg. needing to define a list in an API, and the DB)
managing updates or changes to them
making it clear what the values actually represent - or not having IDs specified as a series of integers that require cross-referencing other tables or documents to understand what they are
While we have tried various solutions for this in the past, the tools we use with SQLAlchemy, alembic and Postgres make it difficult to do so. While Postgres has an enum type and/or constraints you can use, you cannot change an existing enum/constraint. The only way to do so is to drop the constraint and remake it. Enums can be added to, but removal also requires dropping and remaking it, which in a live system could cause problems.
In a prior project, we solved this with lookup tables, which were essentially data tables we populated when the application started up (or more generally, just synced differences to). For any lookup values, we would specify foreign keys to these lookup value records. While this works, the usage in-code was clunky, and the amount of code necessary to make it work was a lot as several caches had to be maintained to avoid constantly fetching the lookup value records from the DB.
So, what did I do here? I tried to combine the best components of each solution. In-code, only Python enums are used. The LookupEnum type decorator created handles converting the enum to/from a string which makes using it as a developer uneventful. When creating or updating columns, you just keep working with enums, and the DB itself is irrelevant.
As for the DB itself, we still maintain that foreign-key lookup validation approach. We never need to reference the records from that table, and the foreign keys basically serve as a mutable constraint.
Additional ideas, beyond just cleaning this up:
The migration to populate the values is done arbitrarily when the app starts up - probably could find a way to make it work with the migrations themselves
Removal of a lookup value would still be difficult, would need to first validate nothing references the key, and then add logic to delete it.
Modification of a lookup value with this approach wouldn't be possible as for readability I made the enum value the foreign key
I have no idea if the performance of this would be impacted, we did something similar before, so I don't imagine that's a major issue
The "description" of the lookup table could use a bit of work, just added as an idea.
Testing
Adding a new enum value and restarting the app makes it an allowed value in both the API, as well possible to store in the DB.
This is only a proof of concept - using the template repo for easy of sharing across teams, no intention to merge this.
Changes
Modified how lookup values work making them derived directly from enums, but stored in the DB as text-foreign keys to handle validation at the DB layer.
Note that the implementation is very hacky, and poorly organized.
Context for reviewers
I'll start by saying that this idea might be more than necessary, but for DBs that we really want ease-of-use in-code, as well as validation of lookup values in the DB, it would cover that with minimal complexity.
This is an idea that joins together various different solutions to get something that I hope is somewhat intuitive. First, a bit of context:
Many systems require the use of lookup values, IDs that represent a limited set of potential values. For example, the US states (CA, NJ, MI, etc.), different claim statuses (open, closed, pending), race, ethnicity, address type, and many many more.
The biggest challenge of managing these values is:
While we have tried various solutions for this in the past, the tools we use with SQLAlchemy, alembic and Postgres make it difficult to do so. While Postgres has an enum type and/or constraints you can use, you cannot change an existing enum/constraint. The only way to do so is to drop the constraint and remake it. Enums can be added to, but removal also requires dropping and remaking it, which in a live system could cause problems.
In a prior project, we solved this with lookup tables, which were essentially data tables we populated when the application started up (or more generally, just synced differences to). For any lookup values, we would specify foreign keys to these lookup value records. While this works, the usage in-code was clunky, and the amount of code necessary to make it work was a lot as several caches had to be maintained to avoid constantly fetching the lookup value records from the DB.
So, what did I do here? I tried to combine the best components of each solution. In-code, only Python enums are used. The
LookupEnumtype decorator created handles converting the enum to/from a string which makes using it as a developer uneventful. When creating or updating columns, you just keep working with enums, and the DB itself is irrelevant.As for the DB itself, we still maintain that foreign-key lookup validation approach. We never need to reference the records from that table, and the foreign keys basically serve as a mutable constraint.
Additional ideas, beyond just cleaning this up:
Testing
Adding a new enum value and restarting the app makes it an allowed value in both the API, as well possible to store in the DB.