Model Inheritance using SQLAlchemy

Object inheritance is a staple in object-oriented-programming, but requires a bit of extra thought when applied with SQLAlchemy.

In this case, I have two distinct models which I want to inherit from a third, shared, parent.

Both: participant and host should inherit the majority of the their properties from the parent user. In my instance, this allowed me to share some standard Flask-Login capabilities, but still decouple distinct logic between the models.

SQLAlchemy requires you to adopt one of three patterns outlined here: https://flask-login.readthedocs.io/en/latest/.

Joined Table Inheritance

The above is implemented as an example of “Joined Table Inheritance”

In this case the database will have three tables to segment “users”:

1. A base users table which will store the columns in the model definition for User including the type column
2. A participant table which will store the participant id (which has a foreign key constraint to the user table) and the specified attributes in the participant model (e.g. email_address)
3. A host table which will store the host id (which has a foreign key constraint to the user table) and the specified attributes in the host model (e.g. website)

For example:

1. Create a new participant with a username=peter and email_address=peter@foo.bar.

• The user table will have a row with two columns: id: 1, and type: participant
• The participant table will have a new row with two columns id: 1 and email_address: peter@foo.bar

(Note: both id columns have the same value because of the foreign key constraint)

Querying

• Querying for all Users will return both Participant and Host objects
• Querying for all Participants will return ony Participant objects (and the same for Host)

Implementation

The User model requires:

1. Identify which column stores the “type” of child model.

...
type = Column(String(128), nullable=False)
...

2. Identify the “polymorphic identity” of this model, and specify the above column

__mapper_args__ = {
    "polymorphic_identity": "user",
    "polymorphic_on": type  # Separate the different sub-models on the "type" column
  }

https://github.com/briangreunke/sqlalchemy-inheritance