Inheritance is a core concept when we are developing with an object oriented approach. Developing models/entities with SQL Alchemy or any mapper is no different. We can have all the benefits of OOP -inheritance, composition etc.- and transition/translate them to database design. In this post we will go over a brief example on how to utilize inheritance in SQL Alchemy.
The main reason we want to do this is to avoid repeated functionality in our models. Where functionality can be either model methods (a custom get or create, create or update etc) or model attributes.
Let us use a very simple example for this demo. Assume that we have to model a school. The main entities we have in this scenario are teacher, student, and class. We will be using Postgres as our database for this particular example -but overall one of the points of using an ORM is that they are database agnostic- and a schema called orm_inheritance.
Having those in mind the initial code to create those tables would look something like this:
from sqlalchemy import Column, String, create_engine, event, DDL, Integer, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
conn_string = "postgres://user:password@localhost:5432/code_examples"
db = create_engine(conn_string, echo=True)
Base = declarative_base()
event.listen(
Base.metadata, "before_create", DDL("CREATE SCHEMA IF NOT EXISTS orm_inheritance")
)
class Class(Base):
__tablename__ = "class"
__table_args__ = {"schema": "orm_inheritance"}
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String)
description = Column(String)
class Teacher(Base):
__tablename__ = "teacher"
__table_args__ = {"schema": "orm_inheritance"}
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String)
surname = Column(String)
hired_at = Column(DateTime)
class Student(Base):
__tablename__ = "student"
__table_args__ = {"schema": "orm_inheritance"}
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String)
surname = Column(String)
birth_date = Column(DateTime)
if __name__ == "__main__":
Session = sessionmaker(db)
session = Session()
Base.metadata.create_all(db)If we run the code above it will generate the tables in the database as shown below.
As we can see there is some repeated code. Generation of ids and declaration the schema where the table should be created. In order to have those only in one place, we could create a BaseModel class which will inherit the Base class and will hold the common functionality. Then the BaseModel will be inherited from the three core models of our program.
For our example we will add the id in the BaseModel and also created_at and updated_at columns. In addition we will declare the table schema and we will implement a custom save method.
from sqlalchemy import Column, String, create_engine, event, DDL, Integer, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime, timezone
conn_string = "postgres://user:password@localhost:5432/code_examples"
db = create_engine(conn_string, echo=True)
Base = declarative_base()
event.listen(
Base.metadata, "before_create", DDL("CREATE SCHEMA IF NOT EXISTS orm_inheritance")
)
class BaseModel(Base):
__abstract__ = True # declared to not be created by create_all
__table_args__ = {"schema": "orm_inheritance"}
id = Column(Integer, primary_key=True, autoincrement=True)
created_at = Column(
DateTime(timezone=True), default=lambda: datetime.now(timezone.utc)
)
updated_at = Column(DateTime(timezone=True), nullable=True)
def save(self, session, commit=True):
session.add(self)
if commit:
try:
session.commit()
except Exception as e:
session.rollback()
finally:
session.close()
class Class(BaseModel):
__tablename__ = "class"
name = Column(String)
description = Column(String)
class Teacher(BaseModel):
__tablename__ = "teacher"
name = Column(String)
surname = Column(String)
hired_at = Column(DateTime)
class Student(BaseModel):
__tablename__ = "student"
name = Column(String)
surname = Column(String)
birth_date = Column(DateTime)
if __name__ == "__main__":
Session = sessionmaker(db)
session = Session()
Base.metadata.create_all(db)
As we can see now, each model holds only the very minimal info which is only related to it. Although always make sure that you add abstract=True to those models to make the ORM understand that it should not try and create them in the database (line 16). The tables that we have created are like this:
We could keep adding functionality in the BaseModel and like this we will pass it to all of our models. Some of the most common methods to add here for example are the get_or_create() and the upsert(). Any functionality/behavior which you will add in the parent model it will pass directly to the children. Nevertheless this is not mandatory of course. Assuming you have a large collection of models you may need some of them to inherit simply from the Base class or from an other parent class.
Applying OOP concepts when working with database models is really powerful and efficient. It allows us to write readable and easier to maintain and extend code in less lines. Keep in mind that you can have more that one Base (or parent) class and each of your models could inherit from the one that is appropriate.