Skip to content
Tutorials Center
My learning

Topics

Lesson 3 of 37 ยท Getting Started

Core vs ORM: How SQLAlchemy Is Organized

Understand SQLAlchemy's layers, the difference between Core and the ORM, how the same query looks in each, and how to choose or mix them in a real project.

Newcomers often ask whether they should learn "SQLAlchemy" or "the SQLAlchemy ORM". The answer is that they are the same package, organized in layers. Understanding the layers early saves a lot of confusion later, because the documentation, error messages and Stack Overflow answers jump between them.

In short: Core works with tables, columns and SQL statements. The ORM works with classes, objects and a unit of work. The ORM is implemented on top of Core, and every ORM query becomes a Core statement before it is run.

flowchart TB
    subgraph ORM["ORM layer"]
      M["Mapped classes"] --> S["Session (unit of work)"]
    end
    subgraph CORE["Core layer"]
      T["Table / Column / MetaData"] --> X["select / insert / update / delete"]
      X --> E["Engine, Connection, Pool"]
    end
    S --> X
    M -. "maps to" .-> T
    E --> D["DBAPI driver"] --> DB[("Database")]

A mapped class points to a Table. A Session builds Core statements and runs them on an Engine.

What Core gives you

  • Engine, Connection and the connection pool.
  • MetaData and Table to describe a schema, and DDL such as create_all().
  • SQL expressions: select(), insert(), update(), delete(), func, case(), joins, CTEs.
  • Results as Row objects (named tuples).

Core is the right fit for data pipelines, reports, bulk loads, scripts and any code where you think in tables and rows, not in objects.

What the ORM adds

  • Mapped classes with typed attributes (Mapped[int]).
  • Relationships between classes (user.addresses).
  • A Session that tracks which objects are new, changed or deleted and flushes them as INSERT, UPDATE and DELETE statements.
  • An identity map: asking twice for the same row in one session gives the same object.
  • Lazy and eager loading strategies.

The ORM is the right fit for applications with a domain model: web backends, services, anything where "a user has many orders" is the natural way to think.

The same query in both layers

The next example defines one users table, then fetches the same data through Core and through the ORM. Compare what you get back.

Python One query, two result shapes

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The class User and the table object share the same columns. Core returned rows of values; the ORM returned User instances. After you change an attribute and commit, the ORM wrote the UPDATE for you.

Statements are the shared language

The key idea of 2.0 is that select() is the one query construct for both layers. In Core you select columns; in the ORM you select entities (classes) or a mix. You execute it with connection.execute() or session.execute()/session.scalars(). That is why this course teaches Core expressions first: they carry over to the ORM unchanged.

Python The same select, run by a Connection and by a Session

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Choosing between them

Question Lean towards
Do I have a domain model with relationships? ORM
Am I loading millions of rows or running reports? Core (or ORM with bulk helpers)
Do I want change tracking and a unit of work? ORM
Do I only need to run a few SQL statements? Core with text()
Is the schema owned by another team or tool? Core with reflection (chapter 3)

You do not have to choose once. Most large projects use the ORM for everyday code and drop to Core for heavy queries, in the same transaction.

Check your understanding

Quick check ยท 3 questions

of 3 answered You scored / 3 ยท perfect!

  1. 1. How are Core and the ORM related?

    Every ORM operation produces Core statements that are executed through the Engine.
  2. 2. What does session.scalars(select(User)) return?

    scalars() takes the first column of each row, which for an entity select is the mapped object.
  3. 3. Which of these is only provided by the ORM?

    The Session with its identity map and unit of work belongs to the ORM. Pooling and Table objects are Core.

Summary

  • Core handles engines, tables and SQL expressions; the ORM adds mapped classes, relationships and the Session.
  • In 2.0, select() is shared: run it on a connection for rows, or on a session for objects.
  • Mix layers freely; choose by how much object modelling the problem needs.

Next: A Quick Tour of the ORM