As our application grows our database usually grow even more, that is the reality of development, and as our database grows, many times we reach some point where the complexity of our data grows with it.

With experience, many developers, data scientists and DBAs reach a point where they encounter a problem:

1. They have data that they need to store in their database
2. This data is basically identical in structure as another table that holds data from the same source
3. But the new set of data represents different entities.

There are many ways of dealing with this problem.

Some ways to deal with this, off of the top of my head

The naive way, store the data along with the existing entities, query the table “logically” to only get whatever you want.

Why is this naive?

1. Your existing clients will have to implement new queries to limit their existing queries to the table before the additions.
2. Your existing clients will have to implement your query filtering logic in all future queries.

The easiest (yet less scalable) way, create a new table (maybe setup some table inheritance in the DB) and keep the new data

Why is it less scalable?

1. There come a point in database maintenance where the more tables you have, the harder it is to find what you want
2. If your data-source changes it’s structure (removes or adds new fields) you now have to update all of the tables that store data from this data-source (heavily relies on documentation, which lets face it, you probably don’t write…)

The polymorphic way, which is probably the more complex, error prone and heavily relies on database architecture

1. This method is prone to over reliance, you should make absolutely sure that your data truly is polymorphic before you decide to move this way
2. Less experienced database architects may treat these tables as they would “documents” from other document based databases, where the tables become just a bin of data that they push to whenever they do not know where to properly store data
3. Hard to maintain in the long run
4. Heavily relies on application logic

It may seem like the polymorphic approach is the most complex, and it can be, if improperly implemented, but if you plan ahead, it can become an incredibly powerful tool if your database table is mostly used through software, and even if your applications are heavily reliant on database functions, triggers and procedures, SQLAlchemy makes sure that their polymorphism support does not require you to make breaking changes on your database table to support it.

Single Table Inheritance - Example

from typing import Literal
from sqlalchemy import String
from sqlalchemy.orm import Mapped, mapped_column, DeclarativeBase


class Base(DeclarativeBase):
  pass


possible_spell_types = Literal['offensive', 'defensive']


class Spell(Base):
  __tablename__ = 'spells'
  id: Mapped[int] = mapped_column(primary_key=True)
  name: Mapped[str] = mapped_column(String(50))
  effect: Mapped[str] = mapped_column(String(500))
  spell_type: Mapped[possible_spell_types] = mapped_column()

  __mapper_args__ = {
      "polymorphic_identity": "spells",
      "polymorphic_on": spell_type,
  }


class OffensiveSpell(Spell):
  __mapper_args__ = {
      "polymorphic_identity": "offensive",
  }


class DefensiveSpell(Spell):
  __mapper_args__ = {
      "polymorphic_identity": "defensive",
  }

In the above we can see the following

1. We define a regular base class from which our Spell class inherits
2. We define a literal which in this case holds two str values, offensive and defensive
3. The literal is used when defining the spell_type column, meaning that if we would attempt to use any other value than what is defined in the literal we would get an error
4. We introduce a new property on the Spell class which is the __mapper_args__ property, which (amongst other things) provides a place to define our polymorphic table definition
5. The polymorphic_identity is a field in which we define how the ORM differentiates between the related tables, it is used kind of like an “identifier” for this table, when objects are created it will be used along with the polymorphic_on property to decide which object is supposed to be instantiated.
6. The polymorphic_on column is used as kind of the “discriminator” that specifies which object are of what type, for example if the instance of this Spell is actually an OffensiveSpell or a DefensiveSpell

Note that Polymorphic tables are not actually different database tables, this kind of inheritance does not actually effect the database directly but only your application.

The instances of DefensiveSpell and OffensiveSpell actually point to the original spell database table.

If we look at the following piece of code which migrates our table

from sqlalchemy import create_engine

engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)

Even though have three Declarative definitions, we can only one table definition pushed to the database, the rest we can either directly create, or let the ORM take care of deciding which table object needs to be instantiated.

Usage examples

Lets look at an example and it’s output to make more sense of things

We will begin by inserting some data into the database

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)

with Session.begin() as session:
  fire_bolt = Spell(
      id=1,
      name='Fire Bolt',
      effect='Ignites folk',
      spell_type='offensive'
  )
  water_splash = Spell(
      id=2,
      name='Water Splash',
      effect='Unignites folk',
      spell_type='defensive'
  )
  session.add_all([fire_bolt, water_splash])

So far so good, nothing special, but lets try to query our database using the Spell

with Session() as session:
  all_spells = session.query(Spell).all()
  for spell in all_spells:
      print(f"{spell.name} - Type: {type(spell).__name__}")

  print("Offensive spells only:")
  offensive_spells = session.query(Spell).filter(Spell.spell_type == "offensive").all()
  for spell in offensive_spells:
      print(f"{spell.name} - Type: {type(spell).__name__}")

  print("Defensive spells only:")
  defensive_spells = session.query(Spell).filter(Spell.spell_type == "defensive").all()
  for spell in defensive_spells:
      print(f"{spell.name} - Type: {type(spell).__name__}")

  # You can also query the subclass directly
  print("Direct query of defensive spells:")
  direct_defensive = session.query(DefensiveSpell).all()
  for spell in direct_defensive:
      print(f"{spell.name} - Type: {type(spell).__name__}")

Lets break this down query by query

1. In the first query we select all of our spells from the database
2. 1. By using the type(...).__name__ syntax, we are requesting the name of the objects that we got from the database
   2. We can see that even though we are querying just the Spell class, we are getting two different object types! none of which are Spell (not directly, but technically, due to polymorphism, they actually are a Spell type)
2. In the second and third queries we are again querying using the Spell class, but getting only the object types that correspond to the polymorphic_identity for each, since the spell_type column is the polymorphic discriminator, each kind of spell_type will correspond to a different object type
3. In the last example we are querying directly with the DefensiveSpell class and if we check the generated query from the output, we can see that SQLAlchemy automatically generated a query with a WHERE spells.spell_type IN (?) statement (and the bounded parameter populated with ‘defensive’).

This kind of behavior is great for our applications since we can use this in the case that we don’t actually want to select a specific polymorphic table, or we want to select multiple ones (possibly with a union statement), or even if we want to find all of the Spell rows that correspond to some other conditions.

Pitfall

If you attempt to get the type of a Spell object right after its instantiation you might be surprised to find the object type is actually the Spell object instead of the polymorphic object, Once you make a DB query you should get the polymorphic object type.

More discriminator possibilities

If we have a need for a more complex polymorphic_identity discriminator we could also use the SQL expressions as the polymorphic_on property, the most common SQL expression as a discriminator is the case function, which we saw in an earlier article.

from sqlalchemy import case

class Manuscript(Base):
  __tablename__ = 'manuscripts'
  id: Mapped[int] = mapped_column(primary_key=True)
  title: Mapped[str] = mapped_column(String(50))
  topic: Mapped[str] = mapped_column(String(50))

  __mapper_args__ = {
      "polymorphic_identity": "manuscripts",
      "polymorphic_on": case(
          (topic == "alchemy", "alchemy"),
          (topic == "herbology", "herbology"),
          (topic == "necromancy", "necromancy"),
          else_="manuscripts",
      ),
  }

class AlchemyManuscript(Manuscript):
  __mapper_args__ = {
      "polymorphic_identity": "alchemy"
  }

class HerbologyManuscript(Manuscript):
  __mapper_args__ = {
      "polymorphic_identity": "herbology"
  }

class NecromancyManuscript(Manuscript):
  __mapper_args__ = {
      "polymorphic_identity": "necromancy"
  }

Base.metadata.create_all(engine)

Lets insert some records into our database, using each one of the options our discriminator expects, including the option for the else_ argument in our case function.

with Session.begin() as session:
  # Insert some manuscripts
  spell_book = Manuscript(
      id=1,
      title='A Collection of Spells',
      topic='magic'
  )
  alchemy_book = AlchemyManuscript(
      id=2,
      title='The Art of Alchemy',
      topic='alchemy'
  )
  herbology_book = HerbologyManuscript(
      id=3,
      title='Herbs and Their Uses',
      topic='herbology'
  )
  necromancy_book = NecromancyManuscript(
      id=4,
      title='The Dark Arts of Necromancy',
      topic='necromancy'
  )
  session.add_all([spell_book, alchemy_book, herbology_book, necromancy_book])

Now if we query our database and check the results we can inspect how the polymorphic_identity effects the query constructor when it constructs the query.

from sqlalchemy import select

with Session() as session:
  manuscripts = session.execute(select(Manuscript)).scalars().all()
  for manuscript in manuscripts:
      print(f"{manuscript.title} - Topic: {manuscript.topic} - Type: {type(manuscript).__name__}")

In this query the constructor actually uses the sql CASE expression as part of the query, while injecting our cases into the query, keeping the results in a _sa_polymorphic_on column, which it will use to differentiate between the returned objects.

Hopefully you can imagine the range of possibilities when creating expression based discriminators, this opens us up to many more possibilities.

Complex discriminators

As we can see the polymorphic_identity argument is actually effecting the WHERE clause when the queries are being constructed, we can leverage this knowledge to our benefit and in very complex scenarios use this to create some complex polymorphic identities like so

from sqlalchemy import ForeignKey, func, exists
from sqlalchemy.orm import relationship

class Alchemist(Base):
  __tablename__ = 'alchemists'
  id: Mapped[int] = mapped_column(primary_key=True)
  name: Mapped[str] = mapped_column(String(50))
  guild_id: Mapped[int] = mapped_column(ForeignKey('guilds.id'))
  guild: Mapped["Guild"] = relationship(back_populates='members')
  

class Guild(Base):
  __tablename__ = 'guilds'
  id: Mapped[int] = mapped_column(primary_key=True)
  name: Mapped[str] = mapped_column(String(100))
  members: Mapped[list[Alchemist]] = relationship(lazy='joined')
  __mapper_args__ = {
      "polymorphic_identity": "guild",
      "polymorphic_on": case(
          (
              select(1)
              .where(Alchemist.guild_id == id)
              .group_by(Alchemist.guild_id)
              .having(func.count(Alchemist.id) < 2)
              .exists(),
              'small_guild'
          ),
          (
              select(1)
              .where(Alchemist.guild_id == id)
              .group_by(Alchemist.guild_id)
              .having(
                  func.count(Alchemist.id) >= 2,
                  func.count(Alchemist.id) <= 4
              )
              .exists(),
              'medium_guild'
          ),
          else_='large_guild'
      ),
  }

class SmallGuild(Guild):
  __mapper_args__ = {
      "polymorphic_identity": "small_guild",
  }

class MediumGuild(Guild):
  __mapper_args__ = {
      "polymorphic_identity": "medium_guild",
  }

class LargeGuild(Guild):
  __mapper_args__ = {
      "polymorphic_identity": "large_guild",
  }

Base.metadata.create_all(engine)

In this example we are creating our Alchemist class our polymorphic classes based off of the Guild table, in this case we are leveraging the fact that the WHERE clause is dictating the polymorphic identity of each one of the inheriting classes.

Since the EXISTS expression is used in the WHERE clause, we can construct a query that references the Alchemist class and checks the count of the Alchemists in each one of the guilds

1. If the guild has under 2 members we consider it a small guild
2. If the guild has between 2 and 4 members we consider it a medium guild
3. Anything above that (else_) the guild is a large guild

Usage

Lets begin by inserting some data into our database tables

from sqlalchemy import insert

with Session.begin() as session:
  session.execute(
      insert(Guild).values([
          {'id': 1, 'name': 'The Alchemist Guild'},
          {'id': 2, 'name': 'The Herbology Guild'},
          {'id': 3, 'name': 'The Necromancy Guild'}
      ])
  )
  session.execute(
      insert(Alchemist).values([
          {'id': 1, 'name': 'Elara Vance', 'guild_id': 1},
          {'id': 2, 'name': 'Master Borin', 'guild_id': 1},
          {'id': 3, 'name': 'Silas Croft', 'guild_id': 1},
          {'id': 4, 'name': 'Lysandra', 'guild_id': 1},
          {'id': 5, 'name': 'Zaltar the Mysterious', 'guild_id': 1},
          {'id': 6, 'name': 'Boogi The Mischievous', 'guild_id': 2},
          {'id': 7, 'name': 'Thaddeus Quicksilver', 'guild_id': 2},
          {'id': 8, 'name': 'Zephyr Goldleaf', 'guild_id': 2},
          {'id': 9, 'name': 'Lyra Starwhisper', 'guild_id': 2},
          {'id': 10, 'name': 'Octavius Cindermere', 'guild_id': 3}
      ])
  )

Querying and Automatic instantiation of the correct objects

with Session() as session:
  # Query all guilds
  guilds = session.query(Guild).all()
  for guild in guilds:
      print(f"{guild.name} - Type: {type(guild).__name__}")
      for member in guild.members:
          print(f"  Member: {member.name}")

Looking at the types of each guild we can see that using the polymorphic_identity on both the Guild class and the inheriting polymorphic classes, SQLAlchemy correctly deduces the type of the class that needs to be instantiated for each one of our cases.

The Joined Table Inheritance approach

Up to this point we’ve only examined cases where our polymorphic sub-classes exist only in-code, which I believe might be a more important case to tackle, as we do not always dictate the structure of our database, we might be working in an environment where a DB admin decides the structure of our databases, or where adding additional database tables to a database is not straightforward (legacy software).

In any other case, polymorphic tables actually do support setting a __tablename__ attribute on the sub-classes, in these cases an actual database table is expected to exist, and we still maintain all of the possibilities of polymorphic tables with the additional benefit of being able to define additional columns that have an actual database representation behind them.

An example of the Joined Table Inheritance approach

class Potion(Base):
  __tablename__ = 'potions'
  id: Mapped[int] = mapped_column(primary_key=True)
  name: Mapped[str] = mapped_column(String(50))
  effect: Mapped[str] = mapped_column(String(500))
  potion_type: Mapped[str] = mapped_column(String(50))
  __mapper_args__ = {
      "polymorphic_identity": "potion",
      "polymorphic_on": potion_type,
  }

class IllusionPotion(Potion):
  __tablename__ = 'illusion_potions'
  id: Mapped[int] = mapped_column(ForeignKey('potions.id'), primary_key=True)
  duration: Mapped[int] = mapped_column()
  __mapper_args__ = {
      "polymorphic_identity": "illusion_potion",
  }

class OffensivePotion(Potion):
  __tablename__ = 'offensive_potions'
  id: Mapped[int] = mapped_column(ForeignKey('potions.id'), primary_key=True)
  damage: Mapped[int] = mapped_column()
  __mapper_args__ = {
      "polymorphic_identity": "offensive_potion",
  }

Base.metadata.create_all(engine)

In this example we actually do specify the __tablename__ attribute on each of the classes, meaning that each one of these tables will be created as part of our migration (or expected to exist in the database).

Since these tables are “polymorphic” the id column on each of the sub-tables is set with both the primary_key argument set to True and a ForeignKey pointing to the base class, this approach is actually taken directly from the documentation page.

Populating Joined Table Inheritance polymorphic tables

When writing polymorphic tables that defined using the Joined Table Inheritance approach we should actually write to the sub-tables directly instead of to the base class, since these Declarative definitions represent an actual database table.

with Session.begin() as session:
  # Create some potions
  potion1 = IllusionPotion(
      id=1,
      name='Potion of Invisibility',
      effect='Grants invisibility for a short duration',
      duration=30
  )
  potion2 = OffensivePotion(
      id=2,
      name='Fireball Elixir',
      effect='Deals fire damage to enemies',
      damage=50
  )
  session.add_all([potion1, potion2])

We can omit the “potion_type” though, that gets automatically populated for us.

Selecting from our base class

with Session() as session:
  # Query all potions
  potions = session.query(Potion).all()
  for potion in potions:
      print(f"{potion.name} - Type: {type(potion).__name__} - Effect: {potion.effect}")
      if isinstance(potion, IllusionPotion):
          print(f"  Duration: {potion.duration} seconds")
      elif isinstance(potion, OffensivePotion):
          print(f"  Damage: {potion.damage}")

If we sift through the output from our engine we can tell that while our query selects rows from the potions table (which is separate from the others) we are getting the correct object instance type per potion row, the type function shows us that we are actually getting an IllustionPotion and an OffensivePotion class instances.

This is due to how SQLAlchemy resolve the object instance based off of the polymorphic_identity argument in the __mapper_args__ dictionary.

Another thing that we are seeing is that both of the objects are actually being lazy loaded, we can tell that they are getting lazy loaded by examining all of the subsequent SELECT queries that are being emitted on each attribute access.

In this case we are accessing the duration and damage attributes of our sub-classes which are specific to these sub-classes, by default SQLAlchemy will lazy load polymorphic sub-classes and their attributes, but that behavior can be replaced either explicitly or by specific arguments on the __mapper_args__ dictionary as we will soon see in the next section.

Polymorphic sub-attributes Eager Loading

The advantages of polymorphic tables do not end with our possible omission of the WHERE clause, since the polymorphic tables are represented by their own classes we can extend them in any way that we would like, things like adding specific relationships, columns and anything else we would do with a Declarative Table we can do with these polymorphic classes

Lets create a few Declarative table definitions and polymorphic tables using the Single Table Inheritance approach.

import datetime

class Library(Base):
  __tablename__ = 'libraries'
  id: Mapped[int] = mapped_column(primary_key=True)
  name: Mapped[str] = mapped_column(String(100), nullable=False)
  location: Mapped[str | None] = mapped_column(String(200), nullable=True, default=None)
  # Relationship back to BookRecord
  books: Mapped[list["BookRecord"]] = relationship(back_populates="library")

class Scribe(Base):
  __tablename__ = 'scribes'
  id: Mapped[int] = mapped_column(primary_key=True)
  name: Mapped[str] = mapped_column(String(100), nullable=False)
  specialty: Mapped[str | None] = mapped_column(String(50), nullable=True, default=None)
  # Relationship back to ScrollRecord
  scrolls: Mapped[list["ScrollRecord"]] = relationship(back_populates="scribe")

class Workshop(Base):
  __tablename__ = 'workshops'
  id: Mapped[int] = mapped_column(primary_key=True)
  name: Mapped[str] = mapped_column(String(100), nullable=False)
  focus: Mapped[str | None] = mapped_column(String(50), nullable=True, default=None)
  # Relationship back to TabletRecord
  tablets: Mapped[list["TabletRecord"]] = relationship(back_populates="workshop")

# --- Polymorphic Base Table ---

class AlchemicalRecord(Base):
  __tablename__ = 'alchemical_records'
  id: Mapped[int] = mapped_column(primary_key=True)
  title: Mapped[str] = mapped_column(String(200), nullable=False)
  discovered_date: Mapped[datetime.date | None]
  is_decoded: Mapped[bool] = mapped_column(default=False)
  # Discriminator column for joined-table inheritance
  record_type: Mapped[str] = mapped_column(String(50))

  __mapper_args__ = {
      "polymorphic_identity": "record",
      "polymorphic_on": record_type
  }

# --- Polymorphic Subclass Tables with Specific Relationships ---

class BookRecord(AlchemicalRecord):
  library_id: Mapped[int | None] = mapped_column(ForeignKey("libraries.id"), default=None)
  library: Mapped[Library | None] = relationship(
      back_populates="books",
      lazy='joined'
  )

  __mapper_args__ = {
      "polymorphic_identity": "book", # Identity for this subclass
  }

class ScrollRecord(AlchemicalRecord):
  scribe_id: Mapped[int | None] = mapped_column(ForeignKey("scribes.id"), default=None)
  scribe: Mapped[Scribe | None] = relationship(
      back_populates="scrolls",
      lazy='joined'
  )

  __mapper_args__ = {
      "polymorphic_identity": "scroll", # Identity for this subclass
  }

class TabletRecord(AlchemicalRecord):
  workshop_id: Mapped[int | None] = mapped_column(ForeignKey("workshops.id"), default=None)
  workshop: Mapped[Workshop | None] = relationship(
      back_populates="tablets",
      lazy='joined'
  )

  __mapper_args__ = {
      "polymorphic_identity": "tablet", # Identity for this subclass
  }

Base.metadata.create_all(engine)

Let continue by populating the database

with Session.begin() as session:
  library_dicts = [
      {'id': 1, 'name': 'Grand Archives', 'location': 'Capital City'},
      {'id': 2, 'name': 'Lost Library of Azmar'}
  ]
  libraries = [Library(**library) for library in library_dicts]

  scribe_dicts = [
      {'id': 1, 'name': 'Elara the Swift', 'specialty': 'Enchantments'},
      {'id': 2, 'name': 'Brother Thomas', 'specialty': 'Histories'}
  ]
  scribes = [Scribe(**scribe) for scribe in scribe_dicts]

  workshop_dicts = [
      {'id': 1, 'name': 'Stoneheart Carvers', 'focus': 'Runecarving'},
      {'id': 2, 'name': 'Aetherium Forge', 'focus': 'Artifacts'}
  ]
  workshops = [Workshop(**workshop) for workshop in workshop_dicts]

  books_dicts = [
      {'id': 1, 'title': 'Treatise on Transmutation Vol. 1', 'discovered_date': datetime.date(1500, 5, 20), 'is_decoded': True, 'library_id': 1, 'record_type': 'book'},
      {'id': 2, 'title': 'Compendium of Flora', 'discovered_date': datetime.date(1450, 1, 10), 'is_decoded': False, 'library_id': 1, 'record_type': 'book'},
  ]
  scrolls_dicts = [
      {'id': 3, 'title': 'Recipe for Liquid Luck', 'discovered_date': datetime.date(1200, 1, 1), 'is_decoded': False, 'scribe_id': 1, 'record_type': 'scroll'},
      {'id': 4, 'title': 'Map to the Sunken City', 'discovered_date': datetime.date(950, 6, 15), 'is_decoded': True, 'scribe_id': 2, 'record_type': 'scroll'},
  ]
  tablets_records = [
      {'id': 5, 'title': 'Talisman of Warding Schema', 'discovered_date': datetime.date(800, 8, 15), 'is_decoded': True, 'workshop_id': 1, 'record_type': 'tablet'},
      {'id': 6, 'title': 'Aetherium Condenser Blueprint', 'discovered_date': datetime.date(1650, 11, 5), 'is_decoded': False, 'workshop_id': 2, 'record_type': 'tablet'}
  ]
  alchemical_records = [
      *[BookRecord(**book) for book in books_dicts],
      *[ScrollRecord(**scroll) for scroll in scrolls_dicts],
      *[TabletRecord(**tablet) for tablet in tablets_records]
  ]
  # Add all records to the session
  session.add_all([*alchemical_records, *libraries, *scribes, *workshops])

Querying Polymorphic sub-classes Eagerly

One thing to make sure your understand when you query the base class of your polymorphic tables is that even though SQLAlchemy automatically knows which objects to instantiate it populates the inheriting classes with the results of the base class.

Meaning that any sub-class specific properties will be initiated in the expired state, that means that SQLAlchemy will automatically query the database the first time that these columns are accesses, this behavior as you might know is the lazy loading method.

If you look at our table definitions you can see that our polymorphic sub-classes have relationship definitions with the lazy='joined' argument, this means that are explicitly asking the relationship to be eagerly loaded using the joinedload approach, in reality this is not enough.

In the case that we are looking for these sub-classes to be loaded fully when we query the Base class there is are a few ways we can achieve this.

The selectin_polymorphic approach

The selectin_polymorphic function can be used as one of the ways to achieve eager loading, SQLAlchemy will issue separate SELECT queries to the database per sub-class that we would like to load “eagerly” and populate these instances of the sub-classes, this is similar to the selectin_load relationship loader.

Lets look at an example

from sqlalchemy.orm import selectin_polymorphic

with Session() as session:
  # Query all records
  polymorphic_loads_with_eager = selectin_polymorphic(
      AlchemicalRecord,
      [BookRecord, ScrollRecord, TabletRecord]
  )
  records = session.execute(
      select(AlchemicalRecord).options(polymorphic_loads_with_eager)
  ).scalars().all()
  for record in records:
      print(f"{record.title} - Type: {type(record).__name__} - Discovered: {record.discovered_date} - Decoded: {record.is_decoded}")
      if isinstance(record, BookRecord):
          print(f"  Library: {record.library.name if record.library else 'None'}")
      elif isinstance(record, ScrollRecord):
          print(f"  Scribe: {record.scribe.name if record.scribe else 'None'}")
      elif isinstance(record, TabletRecord):
          print(f"  Workshop: {record.workshop.name if record.workshop else 'None'}")

In this example we are introducing a basic usage of the selectin_polymorphic function.

The code snippet goes like this:

1. We use the selectin_polymorphic function to define which base class and which sub-classes we would like to SELECT from
2. The select function queries the base class of our polymorphic classes
3. We add the selectin_polymorphic result to the options on our select statement, this will make sure that the polymorphic sub-classes related to the AlchemicalRecord are loaded eagerly using the selectin relationship loading technique.

If we take a look at the output from the engine we can see that we have four queries emitted to the database.

1. The first one queries all of the AlchemicalRecord objects, which are the actual base objects
2. The second, third and fourth ones are the queries that are made because of the selectin_polymorphic loader, each one is called to populate the polymorphic instances of the base class along with any class specific columns.

It may seem wasteful to load all of these records one by one, and that might be true, but it can be helpful in cases when network latency is not as critical as memory usage of your program or compute time.

The selectin_load technique is especially helpful to reduce network usage by making sure that the result set we are getting from the database is actually smaller than if we would make a joined_load and expect SQLAlchemy to separate all of the columns into our desired objects

Remember that each one of these objects may have many objects with eager loading, and their relationships might have them too!

In these cases it might be beneficial to load all of the base records of the polymorphic identity that match some base criteria, evaluate them and “lazy” load or operate on them as required.

In cases where your program absolutely needs all of the records along with their relationships and sub-relationships, this method is a good way to do so.

Eager loading using with_polymorphic

There is another method of getting our polymorphic objects to load, this method more closely resembles the joined_load technique in combination with an aliased construct.

Instead of issuing additional SELECT statements to the database per sub-class it instead creates LEFT OUTER JOIN statements on the tables of the underlying sub-classes and joins them to the original query.

In the case that all of our tables are polymorphic without a underlying table that represents them, the query constructor will skip the aliased joins and instead load all of the attributes of the sub-classes directly within one query.

If for example we would use the our polymorphic tables that do not have an underlying tables of their own:

from sqlalchemy.orm import with_polymorphic

with Session() as session:
  # Query all records
  alchemical_record_polymorphic = with_polymorphic(
      AlchemicalRecord,
      [BookRecord, ScrollRecord, TabletRecord]
  )
  records = session.execute(
      select(alchemical_record_polymorphic)
  ).scalars().all()
  for record in records:
      print(f"{record.title} - Type: {type(record).__name__} - Discovered: {record.discovered_date} - Decoded: {record.is_decoded}")
      if isinstance(record, BookRecord):
          print(f"  Library: {record.library.name if record.library else 'None'}")
      elif isinstance(record, ScrollRecord):
          print(f"  Scribe: {record.scribe.name if record.scribe else 'None'}")
      elif isinstance(record, TabletRecord):
          print(f"  Workshop: {record.workshop.name if record.workshop else 'None'}")

We can see that we are in-fact constructing a single query that would be sent to the database and all of our polymorphic sub-classes would be populated in place.

As we access the attributes and relationships on our objects, we can see that no more SQL queries are being emitted to the database, meaning we have all of this information in-memory.

Joined Table Eager loading using with_polymorphic

When using the Joined Table Inheritance approach, SQLAlchemy takes a different approach to constructing the underlying SQL Queries, we can take a look at the following example

with Session() as session:
  potions_polymorphic = with_polymorphic(
      Potion,
      [IllusionPotion, OffensivePotion]
  )
  potions = session.execute(
      select(potions_polymorphic)
  ).scalars().all()
  for potion in potions:
      print(f"{potion.name} - Type: {type(potion).__name__}")

Here we can actually see a different behavior, instead of just querying the Potions table, the query actually uses LEFT OUTER JOIN statements in the query to load all of the polymorphic types of the Potion class, this way it actually loads all of the possible sub-classes in a single query.

Automatic selectin_polymorphic and with_polymorphic using __mapper_args__

Using selectin_polymorphic relationship loaded or the with_polymorphic function are great methods if you plan on having using relationship loaders selectively depending on the situation in your application logic.

But what if your polymorphic table are used in such a way that you always need your polymorpic tables fully loaded?

For this you can actually use additional __mapper_args__ arguments.

selectin_polymorphic alternative

Instead of using the selectin_polymorphic relationship loader you may use the following syntax

__mapper_args__ = {
  "polymorphic_load": "selectin"
}

with_polymorphic alternative

Instead of using the with_polymorphic function you may use the following syntax

__mapper_args__ = {
  "with_polymorphic": "*"
}

Or in-order to load only a few of the relationships

__mapper_args__ = {
  "with_polymorphic": [ClassOne, ClassTwo]
}

Summary

Polymorphic tables are a special case of database design, are really should not be forced upon you application, although when you encounter a situation that justifies it, SQLAlchemy’s support for it can become very useful.

In the next article we will delve into Hybrid Declarative Tables, which is another advanced form of constructing “Declarative” tables.