# Polymorphic Tables in SQLAlchemy

_Polymorphism is possibly one of the first concepts that developers encounter whenever they learn about programming for the first time, and yet is a topic that eludes many when it comes to Database design._

By Yonatan Vega · October 15, 2025

> Source: https://fullstack.rocks/article/polymorphic-tables-in-sqlalchemy

![A wizard looking into a mirror and seeing a reflection of himself, or is he?.](https://storage.googleapis.com/fullstack-rocks-media/wizard-in-mirror-960-3894b1893d43.webp)

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

> **Note:** 🧙‍♂️ Cautionary tale about polymorphic tables 
>
> Let me tell you a short story about a successful company that implemented polymorphic tables in the worst way possible, this story was told to me by one of my teachers years ago.
>
> There exists a a very successful company (that I will not name) that started with a small team of junior developers, none of them really knew their way around a relational database, and so an idea came to them to create a single database table with a JSON column, and a column that describes this JSON data.
>
> This table "evolved" into a table that stores all of their application's data.
>
> Well.. as you can imagine, as their user base grew, their database (and subsequently their application) became incredibly slow and incredibly expensive to maintain.
>
> They ended up having to slowly rewrite most parts of their application and database.

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

```python
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",
  }
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/polymorphic-tables-in-sqlalchemy#code-6ac165385e21826463286944)_

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.

> **Note:** 🧙‍♂️ As the title suggests, this approach is considered the **Single Table Inheritance** approach, where a single table exists for each sub-class, not individually, but as a single table on the base class.

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

```python
from sqlalchemy import create_engine

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

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/polymorphic-tables-in-sqlalchemy#code-6ac165385e21826463286946)_

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

```python
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])
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/polymorphic-tables-in-sqlalchemy#code-6ac165385e21826463286947)_

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

```python
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__}")
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/polymorphic-tables-in-sqlalchemy#code-6ac165385e21826463286948)_

Lets break this down query by query

1. In the first query we select all of our spells from the database
    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.

```python
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)
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/polymorphic-tables-in-sqlalchemy#code-6ac165385e21826463286949)_

> **Note:** 🧙‍♂️ Note that in this case we are not using a `Literal` as the discriminator, instead just using a regular `str` 
>
> While this makes our code more error prone, this makes it clear that you actually do not have to using a collection of literals as a discriminator.
>
> In my experience it is good practice to limit the collection of discriminators though, even if it adds a little bit of boilerplate code that you have to maintain as your application grows.

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.

```python
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])
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/polymorphic-tables-in-sqlalchemy#code-6ac165385e2182646328694b)_

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.

```python
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__}")
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/polymorphic-tables-in-sqlalchemy#code-6ac165385e2182646328694c)_

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

```python
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)
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/polymorphic-tables-in-sqlalchemy#code-6ac165385e2182646328694d)_

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

```python
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}
      ])
  )
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/polymorphic-tables-in-sqlalchemy#code-6ac165385e2182646328694e)_

> **Note:** 🧙‍♂️ Note we are using the `begin` and `insert` functions to auto-commit and insert rows in bulk

### Querying and Automatic instantiation of the correct objects

```python
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}")
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/polymorphic-tables-in-sqlalchemy#code-6ac165385e21826463286950)_

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.

> **Note:** 🧙‍♂️ While this example might be written or designed differently for performance benefits, it illustrates the possible uses of the `polymorphic_on` argument and its strengths.

# 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.

> **Note:** 🧙‍♂️ The official SQLAlchemy documentation actually tackles the Joined Table Inheritance case first when introducing polymorphic tables.

## An example of the Joined Table Inheritance approach

```python
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)
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/polymorphic-tables-in-sqlalchemy#code-6ac165385e21826463286953)_

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.

> **Note:** 🧙‍♂️ You will probably not get to see such a primary key configuration in the real world, unless you work on a project with heavy SQLAlchemy usage, in reality a database that has multiple consumers would probably not make this design choice.
>
> You could simply declare a `potion_id` column with a foreign key constraint on each of your sub-tables and SQLAlchemy will do the rest, which you are much more likely to actually encounter in actual databases.

### 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.

```python
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])
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/polymorphic-tables-in-sqlalchemy#code-6ac165385e21826463286955)_

We can omit the “potion\_type” though, that gets automatically populated for us.

### Selecting from our base class

```python
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}")
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/polymorphic-tables-in-sqlalchemy#code-6ac165385e21826463286956)_

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.

```python
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)
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/polymorphic-tables-in-sqlalchemy#code-6ac165385e21826463286957)_

> **Note:** 🧙‍♂️ If we take a look at the SQL Output of the `alchemical_records` table, we can see that even though the `library_id` , `scribe_id` and `workshop_id` are defined on it’s sub-classes they still actually exist in the `CREATE TABLE` definition.
>
> In the case that you are constructing your database manually or using SQL scripts, you should make sure to reflect this behavior.

### Let continue by populating the database

```python
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])
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/polymorphic-tables-in-sqlalchemy#code-6ac165385e21826463286959)_

## 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

```python
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'}")
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/polymorphic-tables-in-sqlalchemy#code-6ac165385e2182646328695a)_

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 

> **Note:** 🧙‍♂️ Remember that a `joined_load` is used to constructs `JOIN` statements into our queries, which means that for every relationship we load, columns are being added to each row, and if our relationship is a `x-to-many` relationship, that means that every `x` would be duplicated to the amount of rows on the other side of the relationship.
>
> Whenever working with large datasets it might be wise to move to the `selectin_load` technique, which would increase the amount of round-trips to the database, but reduce the size of each response and the time it takes to construct 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.

> **Note:** 🧙‍♂️ Lazy loading is especially beneficial in cases of long living programs that traverse a database based off of user interaction.

### 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:

```python
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'}")
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/polymorphic-tables-in-sqlalchemy#code-6ac165385e2182646328695d)_

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

```python
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__}")
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/polymorphic-tables-in-sqlalchemy#code-6ac165385e2182646328695e)_

> **Note:** 🧙‍♂️ The `Potion` class is the base class of both the `IllusionPotion` and `OffensivePotion` classes, as a reminder these polymorphic classes are using the Joined Table Inheritance approach, which means that each are pointing to actual underlying tables in the database.

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

```python
__mapper_args__ = {
  "polymorphic_load": "selectin"
}
```

## `with_polymorphic` alternative

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

```python
__mapper_args__ = {
  "with_polymorphic": "*"
}
```

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

```python
__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.