Core vs ORM

SQLAlchemy is two libraries stacked: Core (SQL expression language) and ORM (object/row mapper). They share the engine, dialect, and pool. Core is closer to typed SQL; ORM adds identity, change tracking, and lazy loading.

The two layers

┌─────────────────────────────────┐
│ ORM: Session, mapped classes,   │   ← session.add(user); session.commit()
│   Unit of Work, Identity Map    │
├─────────────────────────────────┤
│ Core: Table, Column, select(),  │   ← conn.execute(select(user_table).where(...))
│   Connection, Engine            │
├─────────────────────────────────┤
│ Dialect: postgresql, mysql,     │   ← drives parameter style, type handling
│   sqlite, oracle, ...           │
├─────────────────────────────────┤
│ DBAPI: psycopg, asyncpg,        │   ← actual database driver
│   pymysql, sqlite3, ...         │
└─────────────────────────────────┘

Core in 5 lines

from sqlalchemy import Table, Column, Integer, String, MetaData, select, create_engine

metadata = MetaData()
users = Table(
    "users", metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String(50)),
)

engine = create_engine("postgresql+psycopg://...")
with engine.connect() as conn:
    result = conn.execute(select(users).where(users.c.name == "alice"))
    for row in result:
        print(row.id, row.name)

users.c.name is the column reference (via the .c accessor — “columns”). select() returns a SQL statement; conn.execute() runs it.

Core gives you Row objects (tuple-like with column access). No instances of mapped classes; no automatic relationship loading.

ORM in 5 lines

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]

with Session(engine) as session:
    user = User(name="alice")
    session.add(user)
    session.commit()
    # user.id is now populated from the DB

The ORM wraps the same Table underneath (User.__table__ is the Core Table). Calling session.add() registers the instance; session.commit() issues the INSERT and refreshes the auto-generated primary key.

Side by side

When to use which

Use ORM for:

• CRUD endpoints (REST APIs, admin panels).
• Code that needs the “domain object” feel (user.email = "..."; user.posts.append(p); commit()).
• Anything where object identity matters across a request.

Use Core for:

• Bulk inserts/updates (10k+ rows).
• Heavy reporting queries (analytics, aggregations).
• ETL pipelines where you don’t need objects.
• Performance-critical paths where ORM overhead dominates.

Most apps are 90% ORM with a few Core escape hatches for bulk operations. Don’t over-rotate either way.

Mixing them

You can drop down to Core inside an ORM Session — both share the same connection and transaction:

with Session(engine) as session:
    # ORM
    user = session.get(User, 42)

    # Core, same transaction
    result = session.execute(
        select(User.id, User.name).where(User.is_active == True)
    )
    rows = result.all()      # list of Row

    session.commit()

session.execute() accepts Core statements directly. For 2.0-style queries, session.execute(select(User)) returns Row objects (because select(User) is a Core construct that knows about ORM entities); session.scalars(select(User)) unwraps to a stream of User instances.

scalars() is the common case for “give me model instances.”

What ORM gives you that Core doesn’t

1. Identity Map — session.get(User, 42) twice returns the same Python object. See 07_unit_of_work_identity_map.md.
2. Unit of Work — set attributes on mapped objects; commit() figures out the right INSERT/UPDATE/DELETE.
3. Relationships — user.posts loads from DB on first access.
4. Cascades — session.delete(user) can auto-delete related rows.
5. Validation hooks — @validates on a column, event listeners on save.

What it costs:

• Per-object overhead (every loaded row instantiates a Python object).
• Hidden N+1 if relationships aren’t configured for eager loading.
• The “stale” / “detached” object lifecycle to learn.

When ORM is the wrong tool

• 10k+ row inserts — ORM session.add(); commit() per row is slow. Use session.execute(insert(User), list_of_dicts) (Core executemany) or bulk_insert_mappings.
• Streaming results from huge tables — session.execute(select(User)) materializes everything by default. Use yield_per() or stream_results=True on the connection.
• Complex window functions / analytic queries — express in Core (or raw SQL) and pull the rows you need; don’t try to shoehorn into ORM relationships.

Common interview confusions

• “ORM is just a different syntax for Core.” — close, but ORM adds identity, change tracking, lazy loading. Core has none of that.
• “You have to pick one.” — they coexist in the same session. ORM for CRUD; Core for bulk and reports.
• “Core is always faster.” — for bulk ops yes. For typical single-row CRUD, ORM overhead is small.
• “session.query() is the modern API.” — it’s 1.x style and still works. 2.0-style select() is the modern recommendation.

Interview angle

• “What’s the difference between SQLAlchemy Core and ORM?” — Core is a typed SQL expression builder returning Row objects; ORM maps Python classes to rows with identity map, unit-of-work, lazy loading, and relationships. ORM uses Core under the hood.
• “When would you use Core directly?” — bulk inserts, complex analytical queries, ETL pipelines, performance-critical paths where ORM overhead matters.
• “What does session.scalars(select(User)) give you that session.execute(select(User)) doesn’t?” — scalars() unwraps each Row to give you User instances directly; execute() returns Row objects each containing one User.
• “What features does ORM add over Core?” — identity map (same object per row per session), unit of work (batched INSERT/UPDATE/DELETE on commit), relationship loading (lazy/eager), cascades.
• “Why is session.query(User).all() considered legacy?” — 2.0-style unified the API around select(); both styles work but new code should use session.execute(select(User)).scalars().all() or session.scalars(select(User)).all().