# Delving into the Hybrid Declarative Approach in SQLAlchemy

_The hybrid declarative approach is an incredibly underrated feature, it allows you to work against data structures from your database as if they were any other Declarative Table_

By Yonatan Vega · October 22, 2025

> Source: https://fullstack.rocks/article/delving-into-the-hybrid-declarative-approach-in-sqlalchemy

![A wizard lifting his wand in order to break a pinata while his students are cheering him on.](https://storage.googleapis.com/fullstack-rocks-media/pinata-wizards-960-ac00918c3a3f.webp)

In the article “Objectifying our database with SQLAlchemy ORM” we’ve touched very briefly on the Hybrid Declarative approach, as the primary focus of this article series is on the Declarative Approach, and one of the main use-cases for using the Hybrid Declarative Approach is actually to use the `__table__` property to assign an Imperative model to a Declarative model.

Ill show the same example we’ve used in that article here to refresh your memory

```python
from sqlalchemy import MetaData, create_engine, Table, Column, Integer, String
from sqlalchemy.orm import DeclarativeBase

engine = create_engine('sqlite:///:memory:', echo=True)

metadata_obj = MetaData()

flasks_table = Table(
	"flasks",
  metadata_obj,
  Column('id', Integer, primary_key=True),
  Column('type', String(100)),
  Column('volume', Integer),
)

class Base(DeclarativeBase):
  pass

class Flask(Base):
  __table__ = flasks_table

metadata_obj.create_all(engine)
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/delving-into-the-hybrid-declarative-approach-in-sqlalchemy#code-6ac165385e21826463286963)_

This straightforward example is a great starting point. It might already be satisfactory for many use cases, such as migrating imperative models or just creating wrappers to be used by systems that expect declarative models to be used in specific places.

## But that's not all, folks.

The hybrid declarative approach is actually much more powerful, since it can also accept any `FromClause` as a `__table__` property, which means that technically, you can create declarative models from `select` statements, which provides a way of creating programmatic “views” directly from SQLAlchemy.

Let's take a look at a basic example. We will begin by creating Declarative tables for the subquery that we will be using

```python
from sqlalchemy import ForeignKey
from sqlalchemy.orm import Mapped, mapped_column, Session

class Alchemist(Base):
  __tablename__ = 'alchemists'
  
  id: Mapped[int] = mapped_column(primary_key=True)
  name: Mapped[str] = mapped_column(String(50))
  academy: Mapped[str] = mapped_column(String(50))

class Transmutation(Base):
  __tablename__ = 'transmutations'
  
  id: Mapped[int] = mapped_column(primary_key=True)
  alchemist_id: Mapped[int] = mapped_column(ForeignKey('alchemists.id'), nullable=False)
  technique_name: Mapped[str] = mapped_column(String(50))
  potency_level: Mapped[int]

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

# Add sample data
with Session(engine) as session:
  alchemists = [
      Alchemist(name="Aurelius Flameheart", academy="Chrysopoeia Academy"),
      Alchemist(name="Mercuria Quicksilver", academy="Chrysopoeia Academy"), 
      Alchemist(name="Sulfurion the Wise", academy="Azoth Collegium")
  ]
  session.add_all(alchemists)
  session.flush()
  
  transmutations = [
      Transmutation(alchemist_id=1, technique_name="Solve et Coagula", potency_level=9),
      Transmutation(alchemist_id=1, technique_name="Nigredo Process", potency_level=6), 
      Transmutation(alchemist_id=2, technique_name="Aqua Vitae", potency_level=10),
      Transmutation(alchemist_id=2, technique_name="Sublimation Wave", potency_level=8),
      Transmutation(alchemist_id=3, technique_name="Calcination Burst", potency_level=7)
  ]
  session.add_all(transmutations)
  session.commit()
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/delving-into-the-hybrid-declarative-approach-in-sqlalchemy#code-6ac165385e21826463286964)_

### Moving on to Hybrid Declarative

Now that we have two database tables and some data pushed to our database, we can construct a subquery to be used as a `__table__` property.

```python
from sqlalchemy import select, func
from typing import Optional

academy_mastery_query = select(
  Alchemist.academy.label('academy'),
  func.count(Alchemist.id).label('adept_count'),
  func.group_concat(Alchemist.name).label('master_names')
).group_by(Alchemist.academy).subquery()

class AcademyMastery(Base):
  __table__ = academy_mastery_query
  
  # Type hints
  academy: Mapped[str]
  adept_count: Mapped[int] 
  master_names: Mapped[Optional[str]]
  
  # Specify primary key
  __mapper_args__ = {
      'primary_key': [academy_mastery_query.c.academy]
  }

with Session(engine) as session:
  print("--- Academy Mastery Overview ---")
  for mastery in session.execute(select(AcademyMastery)).scalars().all():
      print(f"{mastery.academy}:")
      print(f"\tMaster Alchemists: {mastery.master_names}\n")
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/delving-into-the-hybrid-declarative-approach-in-sqlalchemy#code-6ac165385e21826463286965)_

Since `__table__` expects either a `Table` or a `FromClause` we need to mark our `SELECT` as a `SUBQUERY` using the `.subquery()` chained function, which will make it possible for the query constructor to construct a `FROM` statement that `SELECT`s from a `SUBQUERY`.

We also include all of the fields from the statement as `Mapped` annotated properties, which is actually all we can do for these column as far as type hinting goes, note that it is not required, and in the case that you do not define these column properties you can still access the fields using the imperative `table_name.c.column_name` syntax.

Since the `__table__` property is used, we cannot assign `mapped_column` to any of our fields, this is a limitation of SQLAlchemy and you will actually get an error if you try to do so.

This limitation is actually the reason we use the `__mapper_args__` property to define our `primary_key` for the table, which is also required by SQLAlchemy, in the case that your tables do not define a `primary_key` for an SQLAlchemy model, you will get an error saying that it’s missing.

> **Note:** 🧙‍♂️ Note that the `primary_key` property is actually a `list`, in the case that you might be one of those DB nerds wondering about support for multi-column primary keys.

## What about relationships?

Since we’re basically using the `__table__` property as the from clause, technically everything else about declarative tables should be working as if we had an actual table behind our model.

This means that we can create things like relationships, as we will see in the following example

```python
from sqlalchemy.orm import relationship, selectinload

alchemist_prowess_query = select(
  Alchemist.id,
  Alchemist.name,
  Alchemist.academy,
  func.count(Transmutation.id).label('total_techniques'),
  func.avg(Transmutation.potency_level).label('average_potency'),
  func.max(Transmutation.potency_level).label('peak_mastery')
).join(Transmutation).group_by(Alchemist.id, Alchemist.name).subquery()

class AlchemistProwess(Base):
  __table__ = alchemist_prowess_query
  
  id: Mapped[int]
  name: Mapped[str]
  academy: Mapped[str] 
  total_techniques: Mapped[int]
  average_potency: Mapped[Optional[float]]
  peak_mastery: Mapped[Optional[int]]
  
  transmutations: Mapped[list[Transmutation]] = relationship()
  
  __mapper_args__ = {
      'primary_key': [alchemist_prowess_query.c.id]
  }

with Session(engine) as session:
  print("--- Alchemist Prowess ---")
  prowess_query = select(AlchemistProwess).options(
      selectinload(AlchemistProwess.transmutations)
  )
  for prowess in session.execute(prowess_query).scalars().all():
      print(f"Alchemist: {prowess.name} (Academy: {prowess.academy})")
      print(f"\tTechniques Mastered: {prowess.total_techniques}")
      print(f"\tPeak Mastery Level: {prowess.peak_mastery}")
      print("\tAll Transmutations:")
      for transmutation in prowess.transmutations:
          print(f"\t\t- {transmutation.technique_name} (Potency: {transmutation.potency_level})")
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/delving-into-the-hybrid-declarative-approach-in-sqlalchemy#code-6ac165385e21826463286967)_

In this example we begin by selecting `Alchemist` rows and joining the `Transmutation` table on them, since our declarative tables have a `ForeignKey` defined on the `Transmutation` object we do not need to define an `on clause` on the join.

When defining the relationship for the `Transmutation` table, since our `primary_key` is the same as for the `Alchemist` table, SQLAlchemy is able to automatically determine the conditions for this relationship.

This is actually how I would actually recommend working with Hybrid Declarative Models, find the root element that you would like to work with, like in our example, the `Alchemist` table, and use its primary key as the `primary_key` in your `__mapper_args__` .

This method can save time when defining relationships, but the more important reason is that it maintains a behavior closer to native for this model. You can continue to use this model and actually use its primary key to define relationships from and to it, as long as your other models maintain a reference in the form of a `FOREIGN KEY` to the `Alchemist` table, creating relationships to this model is a breeze.

> **Note:** 🧙‍♂️ You might remember from previous articles that `selectinload` is better suited for one-to-many relationships since it creates another query for locating the requested relationship objects, which is why I use `selectinload` as opposed to, for example `joinedload`

# Summary

The hybrid declarative approach is a particular feature that is suitable in some situations. The reason it’s so interesting is specifically for cases where your database might have complex queries that you would like to make either reusable or extendable/manipulatable per use case, without having to maintain a query construction function or class to manipulate your queries.

Instead, using SQLAlchemy’s native tools allows us to define and maintain a simple-to-use interface for accessing even the most complex queries. To me, this seems like a perfect example of how powerful SQLAlchemy can be when it comes to database abstraction.

The hybrid declarative approach also partially replaces your need for database views (unmaterialized) if they are solely used in your application layer and not required in the database layer.