SQLAlchemy ORM vs Core: When to Use Each Approach

Jun 15, 2026

Problem

SQLAlchemy has two layers: Core and ORM. This confuses most newcomers. They import sqlalchemy.orm by default, struggle with sessions and identity maps, and never realize Core exists as a simpler alternative.

I’ve seen many developers in the r/Python thread “Are we happy with SQLAlchemy?” complain about complexity — and most of them are fighting the ORM layer when Core would serve them better.

The good news: they share the same engine and you can mix them freely.

The Two Layers

When to Use	Core	ORM
Simple CRUD	Overkill	Perfect
Complex queries	Best choice	Awkward
Bulk operations	Much faster	Slow
Data migration scripts	Good	Overkill
Prototyping	Fine	Faster dev
Legacy database	Better control	Can be tricky

Core works with SQL expression language directly. You execute() statements and get Result objects. No sessions, no identity map, no dirty tracking.

ORM wraps Core with a unit-of-work pattern. You session.add() objects, commit() transactions, and let SQLAlchemy track changes automatically.

Diagram showing SQLAlchemy's layered architecture: Engine at the bottom, Core (SQL Expression Language) in the middle, ORM (Session, Identity Map, Unit of Work) on top.

Core — Direct, SQL-like

from sqlalchemy import create_engine, Table, MetaData, select, func

engine = create_engine("postgresql://user:pass@localhost/db")
metadata = MetaData()
blogs = Table("blogs", metadata, autoload_with=engine)

stmt = (
    select(blogs.c.author_id, func.count(blogs.c.id))
    .where(blogs.c.pub_date >= "2026-01-01")
    .group_by(blogs.c.author_id)
    .having(func.count(blogs.c.id) > 5)
)

with engine.connect() as conn:
    result = conn.execute(stmt)
    for row in result:
        print(f"Author {row.author_id}: {row.count} posts")

This reads almost like SQL. The expression language maps directly to SQL constructs — select, where, group_by, having all behave as you’d expect.

ORM — Object-centric

from sqlalchemy.orm import Session

with Session(engine) as session:
    blog = Blog(title="New Post", pub_date="2026-06-15", author_id=1)
    session.add(blog)
    session.commit()
    # ORM tracks objects automatically
    print(f"Created blog with id {blog.id}")

The ORM shines here. Create objects, add them to a session, commit. The identity map ensures each database row maps to exactly one Python object, and dirty tracking means you only flush what changed.

Hybrid — Best of Both

The real power comes from mixing them.

from sqlalchemy.orm import Session
from sqlalchemy import text

with Session(engine) as session:
    # Use ORM for simple operations
    session.add(Blog(title="Post 1", pub_date="2026-06-15"))

    # Use Core for complex aggregation
    result = session.execute(
        text("""
            SELECT author_id, COUNT(*) as cnt
            FROM blogs
            WHERE pub_date >= :min_date
            GROUP BY author_id
            HAVING COUNT(*) > :min_count
        """),
        {"min_date": "2026-01-01", "min_count": 5}
    )

Use session.connection() or session.execute() with raw SQL or Core expressions to drop down to the Core level within an ORM session. Both share the same transaction.

Common Mistakes

Side-by-side comparison: left side shows overly complex ORM join chains marked as a common mistake, right side shows the equivalent simple Core query marked as the recommended approach.

Using ORM when Core would be simpler. Reporting queries, batch updates, and complex joins are easier with Core.

Trying to force ORM patterns on non-trivial SQL. This leads to complex join() chains that are harder to read than raw SQL.

Not knowing about session.connection(). You can drop to Core within ORM sessions without managing a separate connection.

Over-abstracting with ORM when the team already knows SQL. ORM is for developer productivity, not for hiding SQL from developers.

Summary

In this post, I explained the difference between SQLAlchemy Core and ORM, and showed how to use each approach. The key point is they are complementary, not competing. Use ORM for what it’s good at (object management, CRUD) and Core for what it’s good at (complex queries, bulk operations). They share the same engine, so you can mix them freely. Knowing both layers is the key to SQLAlchemy mastery.

Final Words + More Resources

My intention with this article was to help others share my knowledge and experience. If you want to contact me, you can contact by email: Email me

Here are also the most important links from this article along with some further resources that will help you in this scope:

👨‍💻 r/Python Discussion: Are we happy with SQLAlchemy?
👨‍💻 SQLAlchemy Core Documentation
👨‍💻 SQLAlchemy ORM Documentation

Oh, and if you found these resources useful, don’t forget to support me by starring the repo on GitHub!