paulserban.eu

Writing Edition

Paul Serban

Writing, snippets & book notes

Vol. 2026

SQLAlchemy Explained: What It Is, How It Works, and Why Python Developers Rely On It

A practical, no-nonsense introduction to Python's most widely used database toolkit - from Core expressions to ORM sessions

Introduction

Almost every non-trivial Python application eventually needs to talk to a relational database, and almost every team that does this eventually reaches for SQLAlchemy. It has been around since 2006, predates most of the web frameworks built on top of it, and has quietly become the default answer to "how do we talk to Postgres from Python" for a huge share of the industry. That longevity is not an accident. SQLAlchemy solved a specific, recurring problem - the mismatch between how relational databases represent data and how object-oriented languages represent data - in a way that gave engineers control instead of taking it away from them.

This article is a grounded introduction for engineers who are new to SQLAlchemy or who have used it superficially through a framework like Flask-SQLAlchemy or FastAPI and want to understand what is actually happening underneath. We will cover what SQLAlchemy is, why it exists, how its two main layers (Core and ORM) work, how to use it in realistic code, where it tends to bite people, and how experienced teams use it well. By the end, you should have a working mental model good enough to read SQLAlchemy documentation confidently and make informed architectural decisions about when and how to use it.

What SQLAlchemy Is and the Problem It Solves

SQLAlchemy is a Python SQL toolkit and Object-Relational Mapper (ORM) that gives developers two complementary ways to interact with relational databases: a lower-level "Core" layer for building and executing SQL expressions programmatically, and a higher-level "ORM" layer for mapping Python classes to database tables and working with rows as objects. It supports the major relational databases - PostgreSQL, MySQL/MariaDB, SQLite, Oracle, and Microsoft SQL Server - through a pluggable dialect system, meaning the same application code can often run against different database backends with minimal changes.

The problem SQLAlchemy addresses is often called the "object-relational impedance mismatch." Relational databases model data as normalized tables connected by foreign keys and manipulated through set-based SQL. Object-oriented languages model data as graphs of objects with references, methods, and inheritance. Neither model maps onto the other cleanly. Writing raw SQL strings scattered through application code works for small scripts, but it quickly becomes unmaintainable: queries are duplicated, injection risks creep in through careless string formatting, and there is no single place to reason about how your data model actually looks. SQLAlchemy gives you a structured, testable, injection-safe way to express queries and object mappings in Python itself, without hiding the SQL that is actually running.

It is worth being explicit about what SQLAlchemy is not. It is not a database. It is not a replacement for understanding SQL - in fact, using it well requires understanding SQL better, not less. And it is not the only ORM in the Python ecosystem; alternatives such as Django's ORM, Peewee, and Tortoise ORM exist and serve different niches. SQLAlchemy's particular strength is flexibility: it does not force a single opinionated way of working, which is why it is comfortable both in small scripts and in large systems with complex data access patterns.

Core vs. ORM: The Two-Layer Architecture

The single most important thing to understand about SQLAlchemy's design is that it is really two libraries in one, arranged in layers. SQLAlchemy Core is the foundation: it provides a Python representation of SQL constructs - tables, columns, expressions, joins, SELECT/INSERT/UPDATE/DELETE statements - along with a connection pool and an engine that manages actual database connectivity. Core code looks and behaves close to SQL, just expressed as composable Python objects instead of strings. You can build a query piece by piece, inspect it, and reuse fragments across different statements.

The ORM is built on top of Core and adds object mapping: it lets you define Python classes whose attributes correspond to table columns, and it manages the process of turning rows into instances of those classes (and back again) through a concept called the "unit of work" pattern. When you fetch objects through the ORM, SQLAlchemy tracks their state, and when you modify an attribute and commit, SQLAlchemy generates the corresponding UPDATE statement automatically. This is powerful because it lets you think in terms of your domain model - a User has Orders, an Order has LineItems - rather than constantly writing joins by hand.

Since SQLAlchemy 1.4 and continuing into the 2.0 series, the Core and ORM APIs were deliberately unified around a single, consistent query-construction style built on the select() construct, replacing the older Query object as the primary interface. This "2.0-style" API means Core statements and ORM statements now look nearly identical, which reduces the learning curve of moving between the two layers and makes it easier to drop down to Core for performance-sensitive queries without switching mental models entirely.

Underneath both layers sits the Engine, which manages a connection pool, and the Dialect system, which translates the SQLAlchemy-generated SQL into database-specific syntax. This separation is what allows the same mapped models and Core expressions to run against SQLite in local tests and PostgreSQL in production with only a connection string change, assuming you avoid database-specific SQL features.

Implementation: Practical Examples

Let's ground this in code. The example below defines a small schema using SQLAlchemy's modern declarative style (SQLAlchemy 2.0), sets up an engine, and creates the tables. This is the kind of setup you would find at the top of a real application module.

from datetime import datetime
from typing import List, Optional

from sqlalchemy import create_engine, ForeignKey, String
from sqlalchemy.orm import (
    DeclarativeBase, Mapped, mapped_column, relationship, Session
)


class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String(255), unique=True)
    created_at: Mapped[datetime] = mapped_column(default=datetime.utcnow)

    orders: Mapped[List["Order"]] = relationship(back_populates="user")


class Order(Base):
    __tablename__ = "orders"

    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    total_cents: Mapped[int]
    status: Mapped[str] = mapped_column(String(20), default="pending")

    user: Mapped["User"] = relationship(back_populates="orders")


engine = create_engine("postgresql+psycopg2://app:app@localhost/appdb", echo=False)
Base.metadata.create_all(engine)

Notice the use of Mapped[...] type annotations - this is the SQLAlchemy 2.0 style, which integrates with static type checkers like mypy and makes the mapped attributes self-documenting. The relationship() calls describe how User and Order relate at the Python object level; SQLAlchemy infers the join condition from the foreign key unless you tell it otherwise.

Once the schema exists, everyday work happens through a Session, which represents a transactional, in-memory workspace for your objects. The following example creates a user with an order and then queries for it using the 2.0-style select() construct rather than the legacy Query API.

with Session(engine) as session:
    new_user = User(email="jane@example.com")
    new_user.orders.append(Order(total_cents=4599, status="paid"))
    session.add(new_user)
    session.commit()

from sqlalchemy import select

with Session(engine) as session:
    stmt = (
        select(User)
        .where(User.email == "jane@example.com")
    )
    user = session.scalar(stmt)
    print(user.orders[0].total_cents)  # lazy-loads orders on access

This last line - accessing user.orders after the original query already returned - illustrates "lazy loading," one of the ORM's most consequential defaults: related objects are only fetched from the database the moment you access them, inside an open session. That convenience is also the source of one of SQLAlchemy's most notorious pitfalls, which the next section covers directly.

For read-heavy reporting queries where you don't need full object mapping, dropping to Core directly is often faster and clearer, since it avoids the overhead of identity mapping and change tracking entirely.

from sqlalchemy import func

with engine.connect() as conn:
    result = conn.execute(
        select(User.email, func.count(Order.id).label("order_count"))
        .join(Order, Order.user_id == User.id)
        .group_by(User.email)
    )
    for row in result:
        print(row.email, row.order_count)

Trade-offs and Common Pitfalls

The most commonly cited SQLAlchemy pitfall is the N+1 query problem, and it exists precisely because of the lazy-loading convenience shown above. If you fetch a list of a hundred users and then loop over them accessing user.orders, the ORM will issue one query for the users and then, by default, a separate query per user to fetch their orders - 101 queries where one or two would have sufficed. This is not a bug; it is a direct consequence of an ergonomic default that trades explicitness for convenience. The fix is to be deliberate about loading strategy using constructs like selectinload() or joinedload(), which tell SQLAlchemy to fetch related rows eagerly, in a controlled number of queries, at the point you issue the original query.

A second recurring issue is session and object lifecycle confusion, particularly in web frameworks. Objects fetched through a Session are only safely "live" - meaning their lazy-loaded attributes will work - while that session is open. A common bug is passing an ORM object out of a request-scoped session (for example, into a background task or a serializer called after the session closes) and hitting a DetachedInstanceError when an unloaded attribute is accessed. Understanding session scope, and being deliberate about eager-loading what you need before the session closes, avoids most of this class of bug.

There is also a genuine trade-off in choosing how much of the ORM to use at all. The full ORM, with relationships, cascades, and identity mapping, is excellent for transactional, object-shaped work - the classic "create an order, attach line items, update inventory" style of code. But for reporting, analytics, bulk exports, or anything read-heavy at scale, many teams intentionally use Core directly, or even raw SQL via SQLAlchemy's text() construct, because the overhead of object identity tracking and change detection isn't buying you anything when you're not mutating objects. Treating "should this query go through the ORM or Core" as a real design decision, rather than defaulting to whichever is more familiar, tends to produce healthier codebases.

Best Practices for Using SQLAlchemy Well

Teams that get long-term value out of SQLAlchemy tend to converge on a similar set of habits. First, they keep session lifecycle management centralized and explicit - typically one session per request or per unit of work, created and closed by a context manager or framework hook, rather than a global session shared across the application. This avoids most of the detached-object and cross-request state bugs mentioned earlier, and it keeps transaction boundaries obvious when reading the code.

Second, disciplined teams are explicit about loading strategy on any relationship that will be traversed in a loop or serialized into an API response, rather than relying on lazy loading's default behavior. Specifying selectinload() for one-to-many collections and joinedload() for many-to-one references, right in the query that fetches the parent objects, keeps query counts predictable and makes performance characteristics visible in the code itself rather than hidden behind attribute access.

Third, migrations are treated as a first-class part of the schema, almost always managed through Alembic, SQLAlchemy's companion migration tool. Letting Base.metadata.create_all() manage schema in anything beyond local development or tests is a common early mistake; production schema changes need versioned, reviewable migration scripts, and Alembic autogenerates a useful starting point for those scripts by diffing your models against the live database.

Fourth, mature codebases separate query construction from business logic, typically through a repository or data-access layer, so that the same well-tested query isn't copy-pasted across a dozen call sites with subtle variations. This also makes it easier to swap loading strategies or add caching later without touching business logic.

Analogies and Mental Models

A useful mental model for SQLAlchemy Core is that it is SQL "in Python's clothing" - every select(), join(), and where() call maps almost one-to-one onto a clause you would write by hand in SQL, just expressed as composable objects instead of a string. If you can already write the SQL, Core is mostly a matter of learning the vocabulary, not the concepts.

The ORM's Session is best thought of as a staging area, much like git's working directory and index. Objects you load or create sit in this staging area, changes accumulate against them, and nothing is actually written to the database until you commit() - analogous to a commit finalizing staged changes into the permanent history. Understanding the session this way makes lazy loading, dirty tracking, and rollback behavior far more predictable, because you start reasoning about "what state is currently staged" instead of assuming every attribute access magically talks to the database.

The 80/20 of SQLAlchemy

If you only internalize a handful of ideas from SQLAlchemy, make them these: understand the difference between Core and ORM and consciously choose between them per use case; treat the Session as a transactional scope with a clear beginning and end rather than a global object; always decide loading strategy explicitly for any relationship you will traverse more than once; and use Alembic for schema changes from day one, even on side projects, because retrofitting migrations onto an existing production schema is far more painful than starting with them.

These four ideas resolve the overwhelming majority of real-world SQLAlchemy problems teams encounter - N+1 queries, detached instance errors, schema drift, and unpredictable query performance. Everything else in the library, from custom types to hybrid properties to event listeners, is valuable but secondary; you can build reliable production systems knowing only the fundamentals above, and pick up the rest as specific needs arise.

Key Takeaways

Conclusion

SQLAlchemy earns its place in the Python ecosystem not by hiding SQL from developers but by giving them a structured, composable, and type-friendly way to express it, alongside an object mapper that handles the tedious parts of translating rows into domain objects. The library rewards engineers who take the time to understand what is happening underneath - session lifecycle, loading strategy, the Core/ORM split - and it can quietly cause pain for those who treat it as a black box that "just works."

The good news is that the fundamentals are learnable in an afternoon and the pitfalls are well documented and predictable once you know what to look for. Start with the 2.0-style select() API, keep your sessions scoped tightly, be deliberate about what gets loaded and when, and bring in Alembic before you need it rather than after. From there, SQLAlchemy tends to scale gracefully from a weekend script to a large, multi-service production system, which is precisely why it has remained the default choice for relational database access in Python for close to two decades.

References