halfORM vs SQLAlchemy Core vs Django ORM¶
Part 5 of six in Relation-Oriented Programming. Series index
Let me declare the bias first: I wrote halfORM. Everything below is a structural comparison chosen to illustrate a design claim, not a neutral evaluation of three libraries. There's no benchmark here and no user study. I've included the case where halfORM comes off worst, because a comparison that only shows wins isn't worth reading.
The two reference points are the dominant styles of Python data access. SQLAlchemy Core is a SQL expression builder and the closest structural peer here, since both defer execution and compose expressions. Django ORM is the full code-first ORM. Peewee, PonyORM and Tortoise are all code-first with model class declarations and condition-level composition, so they'd sit next to Django in every row below.
All examples use a four-table blog schema: author, post, comment,
comment_type, whose DDL is in the
series index. In the SQLAlchemy listings, post / author / comment are
Table objects declared with autoload_with=engine.
The underlying difference is what the composable unit is:
- SQLAlchemy Core composes column expressions (
post.c.published == True) andSelectstatements. - Django ORM composes
Qobjects and__path expressions. - halfORM composes relation predicates: an object carrying its table, its constraints, and its join structure.
That distinction sounds academic until you try to reuse something.
Query 1: set difference¶
Authors who have never posted.
halfORM:
SQLAlchemy Core, with no set-difference operator on Select objects, needs a
subquery:
select(author).where(
~author.c.id.in_(
select(post.c.author_id).where(post.c.author_id.isnot(None))
)
)
Django ORM has no set-difference operator either; exclude with a subquery:
All three work. The difference is that one of them is the relational operation
you actually meant, written once, with a -.
Query 2: predicate reuse¶
This is the one that matters.
In halfORM a Relation is a value. Name it, store it, pass it around, compose
it into different things; no query runs until a terminal method does:
published = Post(published=True) # a value
alice_posts = Author(name='Alice').rfk_posts() # a value
published & alice_posts # published posts by Alice
published.rfk_comments().fk_author() # commenters on published posts
published is reused in two structurally different queries. It carries its own
join structure, so nothing has to be restated.
SQLAlchemy Core: published_cond is a column expression. It carries no join
structure. Each reuse restates the joins:
published_cond = post.c.published == True
# Reuse 1: join restated
select(post).join(author, post.c.author_id == author.c.id) \
.where(and_(published_cond, author.c.name == 'Alice'))
# Reuse 2: two joins restated
select(author).join(comment, comment.c.author_id == author.c.id) \
.join(post, comment.c.post_id == post.c.id) \
.where(published_cond)
Django Q objects are composable and their __ paths do resolve to joins, but
the join structure comes from the QuerySet context, not from the Q object. A
Q written for one starting relation can't cross over to another:
published = Q(published=True)
alice_q = Q(author__name='Alice')
Post.objects.filter(published & alice_q) # context 1: fine
Author.objects.filter(comment__post__published=True) # context 2: `published`
# cannot be reused here
The halfORM version of that second query is
published.rfk_comments().fk_author(): the same published object, in a
different composition.
Query 3: multi-hop navigation¶
Authors who commented on published posts.
halfORM, with no join conditions at all, because they're in the catalog:
SQLAlchemy Core, with every join condition explicit:
select(author) \
.join(comment, comment.c.author_id == author.c.id) \
.join(post, comment.c.post_id == post.c.id) \
.where(post.c.published == True)
Django is concise, and the filter itself generates a single JOIN query:
Credit where due: that's a good line. The problem is downstream, when you traverse the results:
for author in authors:
for comment in author.comment_set.all(): # N queries
print(comment.post.title) # N more queries
# Fix: explicit opt-in, restating the full traversal path
Author.objects.filter(comment__post__published=True) \
.prefetch_related('comment_set__post')
Both Django ORM and SQLAlchemy's ORM layer return model instances rather than dictionaries, which brings back the identity, session, and lazy-loading questions from part 1 of this series.
Summary¶
What the developer must state explicitly:
| Set difference | Predicate reuse | Multi-hop | Schema declaration | Result type | N+1 by default | |
|---|---|---|---|---|---|---|
| halfORM | - operator |
compose once | 0 join conditions | none | dicts | not expressible |
| SQLAlchemy Core | subquery | restate joins | explicit joins | Table per relation |
dicts | no |
| Django ORM | filter/exclude |
restate path | __ traversal |
model class | model instances | yes, unless prefetch |
The "schema declaration" column is the one that compounds. SQLAlchemy Core with
autoload_with populates column metadata from the live database automatically,
but you still need one Table variable per relation and you still write join
conditions by hand. automap_base() goes further and generates mapped classes
from a live schema, but it targets the ORM layer, so what you get back carries
the identity map, the session, and the lazy loading that this whole design is
trying to avoid. halfORM's database-first stance isn't unique; the combination
of introspection-only, relation-level predicates, and no session management
doesn't have a direct equivalent in the Python ecosystem that I know of.
Where halfORM loses: scalar aggregation¶
Two things travel under the name "aggregation", and halfORM handles exactly one of them.
Collecting related rows it does natively. ho_select(json_agg=...), from
part 3, compiles to a LEFT JOIN plus json_agg
plus a GROUP BY on the primary key, and returns each author with their posts
nested inside the row. It follows FK chains, and takes a distinct flag for
when an intermediate join multiplies rows. No view required.
Reducing them to one number per group it does not. Annotate every author with their post count. Django:
One expression. Django infers the LEFT OUTER JOIN and the GROUP BY from the
declared foreign key. It's exactly right, and it's the kind of thing you write
constantly.
halfORM has no equivalent. There is no COUNT, SUM or AVG to project per
group, no HAVING to filter on the result, and no window functions;
correlated subqueries are outside what relation-oriented composition expresses
too. (ho_count() counts the extension of one predicate, which is a different
question.)
You can get close by counting the aggregated list in Python:
authors = Author()
authors.rfk_posts.set()
for row in authors.ho_select(json_agg={'rfk_posts': ['id']}):
post_count = len(row['rfk_posts'])
That works, and it is the wrong tool for the job. It ships every post id across the wire to compute a number the database could have returned by itself, and the count exists only once the rows arrive, so it cannot narrow the query: there is no way to ask for authors with more than ten posts and have the database do the filtering. The recommended answer is a view:
CREATE VIEW blog.author_post_count AS
SELECT a.*, count(p.id) AS post_count
FROM author a
LEFT JOIN post p ON p.author_id = a.id
GROUP BY a.id;
and then use it like any other relation, because halfORM doesn't distinguish tables from views:
Is that better or worse? Honestly: it depends on who you are.
The cost is real: you write and maintain a view, you need DDL rights or a DBA, and a one-liner became a two-step. If you're a solo developer on a greenfield project, Django's version is plainly more convenient.
The benefit is also real: the view is available to every client of that database, not just your Python app; it's version-controlled alongside the schema; it's readable by anyone who knows SQL; and the query planner sees it as SQL rather than as generated ORM output. If three services query this database and two of them aren't in Python, the view is the only version of this logic that all three can share.
So it's a position, not an oversight: complex query logic belongs in the database, expressed in the language designed for it. But you should know you're choosing it.
The same escape hatches exist for everything else: model.execute_function()
and model.call_procedure() for stored routines, model.execute_query() for
arbitrary parameterised SQL returning dicts.
When not to use halfORM¶
Since this is the comparison post, the fit question belongs here.
Don't use it if you need to support more than one database engine. halfORM is PostgreSQL-only, deliberately and irreversibly: views, native table inheritance and advanced types are all what makes the rest work.
Don't use it if your project is code-first and you like it that way. If your team's model is "the Python classes are the schema", halfORM inverts your entire workflow and you will fight it every day.
Don't use it if you're in Django. Django's ORM is woven into forms, the admin, auth, and the migration system. Swapping it out means giving up the parts of Django people use Django for.
Do consider it if your team is comfortable with SQL, you treat PostgreSQL as a first-class component rather than a generic storage backend, and you work against schemas that exist independently of your application: because someone else owns them, because several applications share them, or because they predate your code.
That last case is the one halfORM was built for, and it's more common in enterprise settings than the greenfield-project literature suggests.
For a hands-on comparison of your own, Learn halfORM in half an hour.