SQLAlchemy is the most widely used database toolkit in the Python ecosystem. It's two libraries packaged together: Core, a SQL expression language that builds queries from Python objects without writing a string, and ORM, an object-relational mapper that sits on top of Core and maps rows to instances of Python classes. This lesson covers the parts of SQLAlchemy that come up in any serious project: setting up an engine, defining models, running queries with the 2.0-style API, working with sessions, modeling relationships, controlling how related objects load, and where Alembic fits in for migrations.

SQLAlchemy is organized as two stacked APIs, and which one fits depends on what's being built.

The Core layer is the SQL Expression Language. Tables and columns are declared as Python objects, then queries are built by combining them with operators and helper functions. The result of a Core query is a row of values, much like the raw driver returns. Core is what most reporting code, migration scripts, and high-performance data pipelines use. It's also what the ORM uses internally to build the SQL it emits.

The ORM layer sits on top of Core. Classes are declared whose attributes correspond to columns, and queries return instances of those classes. The ORM tracks loaded objects, detects attribute changes, manages a session, and translates the whole picture into the same Core expressions a developer could have written by hand. It costs a bit more memory and a bit more complexity, and in exchange it provides objects with named attributes, relationships, and change tracking.

The diagram clears up a common confusion. Core can be used without the ORM, but the ORM cannot be used without Core: every ORM query bottoms out in Core expressions. In practice, most applications use the ORM for everyday CRUD and drop down to Core for reporting queries and bulk operations.

SQLAlchemy 2.0 is the current major version, and the syntax in this lesson assumes 2.0 throughout. Install it with pip install sqlalchemy. For Postgres add psycopg2-binary (or psycopg), for MySQL add pymysql or mysqlclient. SQLite needs no extra driver because it ships with Python.

The connection starts with create_engine. The engine is a pool of database connections plus the dialect (the part that knows how to talk to a specific database).

The URL string is the connection target. The dialect+driver prefix tells SQLAlchemy which dialect to use (sqlite, postgresql, mysql) and which driver to use under it (psycopg2, pymysql). The rest of the URL is standard user:password@host:port/database with optional query parameters.

echo=True is the most useful debugging knob. It prints every SQL statement SQLAlchemy issues, with the bound parameters, to stdout. Turn it on while learning or debugging a slow page; turn it off in production.

Engines are pools, not connections. Creating one is cheap; creating one per request is wasteful. The standard pattern is one engine per process, defined at module level, used across the whole application.

SQLAlchemy 2.0 uses a typed declarative style: subclass DeclarativeBase, annotate columns with Mapped[...], and use mapped_column for column metadata. The result is a class that serves as both the schema definition and the type returned by queries.

Each piece serves a role. __tablename__ sets the SQL table name; without it, SQLAlchemy can't map the class. The Mapped[int] annotation tells SQLAlchemy the Python type, which gets used both for type hints and for inferring the SQL type when possible. mapped_column carries the column metadata: the SQL type, whether the column is the primary key, whether nulls are allowed, defaults, foreign keys.

Base.metadata.create_all(engine) walks every subclass of Base and issues CREATE TABLE IF NOT EXISTS statements. This is fine for development and tests; for production, use Alembic (covered later in this lesson).

The same definition works across SQLite, Postgres, and MySQL because SQLAlchemy translates the column types to each dialect. String becomes VARCHAR on Postgres, TEXT on SQLite. Integer becomes INTEGER on SQLite, INT on Postgres. The model is written once.

A Session is the unit-of-work container. It holds a database connection, tracks new objects you added, detects changes on loaded objects, and emits all the right SQL when you commit.

Inside the with block, add_all told the session "these are new objects, track them." Then commit flushed the pending inserts to the database, assigned the auto-generated primary keys back to the Python objects, and committed the transaction. After the commit, mug.id and notebook.id are populated because SQLAlchemy round-tripped them through the database.

The with statement matters. It calls session.close() at the end, which returns the connection to the pool. Without it, connections leak, which is the kind of bug that surfaces under sustained load. The context manager makes the cleanup automatic.

Flush versus commit is the next piece of vocabulary. A flush sends pending SQL to the database but doesn't end the transaction; a commit flushes and ends the transaction (so other sessions can see the changes). The session auto-flushes before queries that might depend on the pending changes, so explicit flush calls are rare. The two times they're needed are when a generated primary key is required before commit (session.flush() populates mug.id without committing) and during debugging.

The diagram captures the timing. New objects sit in a pending bucket until flush. Flush sends the SQL but doesn't end the transaction. Commit finishes the unit of work. Queries trigger an auto-flush, so reads stay consistent with pending writes.

SQLAlchemy 1.x and 2.0 support two query styles. The 1.x style (session.query(Model)) still works but is in maintenance mode. The 2.0 style (select(Model) with session.scalars or session.execute) is the recommended form for new code, and it's the style used here.

Each piece has a role. select(Product) starts a statement that returns Product instances. .where(Product.price < 20) adds a filter; the Product.price < 20 expression is a SQL expression, not a Python comparison, because Product.price is a column descriptor. .order_by(Product.price) adds an ORDER BY. session.scalars(stmt) runs the statement and returns the first column of each row (the Product instance); .all() collects them into a list.

The 2.0 style separates "build a statement" from "run it." That separation is useful because statements can be composed, passed around, and extended with filters dynamically without running them prematurely. A common pattern is a function that returns a statement, which the caller then runs or further composes.

The function returned a statement; the caller added .limit(2) before running it. Statements are immutable in 2.0, so each chained call returns a new statement. Statements can be built up step by step without earlier callers seeing later modifications.

The query language covers the same ground as SQL, expressed as method chains.

Multiple .where() calls combine with AND. For OR, use sqlalchemy.or_. For inequalities, comparison operators on column objects produce the corresponding SQL: Product.price > 5 becomes products.price > 5.

Aggregations use func, which is a namespace that proxies to whatever the database supports.

The call switches from session.scalars to session.execute. When the SELECT returns multiple columns (instead of one entity), execute returns rows with named attributes corresponding to the labels. func.count(Product.id).label("count") becomes count(products.id) AS count in SQL, and the row exposes row.count.

GROUP BY follows the same pattern:

The same chain pattern covers the rest of the SQL surface: .join() for joins, .limit() and .offset() for paging, .distinct() for deduplication, .with_only_columns() for narrowing the projection. Every method returns a new statement, so chains are safe to refactor and reuse.

Most real schemas have multiple tables. A Customer has many Orders. An Order has many OrderItems. SQLAlchemy models these connections with foreign keys at the column level and relationship at the class level.

Three pieces tie the relationship together. The ForeignKey("customers.id") on Order.customer_id is the database-level link. The relationship on both sides is the Python-level link: Customer.orders is a list of Order instances, and Order.customer is the Customer who placed it. back_populates tells SQLAlchemy these two relationships are the two ends of the same connection, so assigning to one updates the other automatically.

The example does something useful at commit time. The code never set customer_id on the orders, but the commit succeeded with the right foreign key. The session noticed that the orders were attached to a customer, that the customer is new, that the customer's ID isn't known yet, so it inserted the customer first, captured the generated ID, and then inserted the orders with that ID as the foreign key. The cascading is what makes relationships convenient to work with.

The same flow works the other way. Loading an order, accessing order.customer, mutating it, and committing updates the customer row. Setting order.customer = some_other_customer reassigns the foreign key. The Python attribute is the SQL relationship.

A many-to-many relationship needs a third table, the "association table," whose rows are pairs of foreign keys. SQLAlchemy expresses this with a Table plus relationship(secondary=...).

The product_tags table doesn't need a model class because it's a join table with no extra columns. The two relationship declarations point at it via secondary=product_tags, and SQLAlchemy handles the join logic. Adding a tag to a product's tags list inserts a row in product_tags; removing it deletes a row.

When the association table has extra columns (a created_at timestamp on the tag-product link, for example), a model class is required, and the relationship is configured slightly differently. The SQLAlchemy docs cover that pattern under "association object."

Relationships have a loading strategy that decides when the related rows arrive. The default is "lazy," which means the related rows aren't fetched until the attribute is accessed. That's convenient for one-off loads and expensive in loops, because each access in a loop issues another query (the N+1 problem).

SQLAlchemy provides three main strategies, each with a use case.

Opt into the eager strategies per query with options(selectinload(Customer.orders)) or options(joinedload(Customer.orders)):

The selectinload strategy issues two queries: one for the customers, one for the orders with WHERE customer_id IN (1, 2, 3, ...). Two queries to load 100 customers and all their orders. Without it, the same loop would issue 101 queries.

joinedload is the alternative when the relationship is small and one query is preferable to two:

The trade-off: a join produces a Cartesian product, so loading 100 customers with 10 orders each yields 1,000 rows of which 900 are duplicates of the customer side. SQLAlchemy deduplicates in Python, but the rows still travel across the network. For small relationships (one-to-one, or short lists), this is fine. For long lists, selectinload is usually faster.

The third strategy worth knowing is "raise on access": setting lazy="raise" on the relationship turns accidental lazy loads into errors, which is a useful way to catch N+1 bugs in tests.

N+1 isn't a SQLAlchemy quirk, it's the default behavior across ORMs. The fix is the same everywhere: ask the ORM for the related rows up front. SQLAlchemy's selectinload is the standard default for one-to-many relationships unless there's a specific reason to use joinedload.

Single inserts and updates go through the unit of work: build, add or modify, commit. For larger batches, three options exist, in order of decreasing ORM involvement.

The first option is add_all plus commit. Same as add, but takes an iterable. The session tracks each object, runs INSERTs at commit time, and populates primary keys back.

The session issues 100 INSERT statements (or one per batch, depending on the driver). For 100 rows that's fine. For 100,000 rows, the bookkeeping cost adds up.

The second option is session.execute with a Core insert statement and a list of dicts. This skips the ORM's per-object tracking and issues a single bulk INSERT.

A thousand rows in one statement. The trade-off is that the objects aren't in the session afterward; they would need to be queried back to use them as ORM instances. That's a fine trade for "load this CSV into the database."

The third option is the Core layer directly, bypassing the session entirely:

engine.begin() opens a transaction at the connection level. There's no session, no identity map, no change tracking. For pure data-loading tools that don't care about objects, this is the lightest-weight option.

Updates have the same trio. loaded.attribute = value; session.commit() for one row. session.execute(update(Product).where(...).values(...)) for a bulk update inside a session. engine.begin() plus a Core update for the lightest path.

Schemas change over the life of a project, and editing models without migrating the database leaves the two out of sync. Alembic is SQLAlchemy's official migration tool. It generates Python migration files from the diff between the models and the live schema, and applies them in order.

The skeleton of an Alembic workflow:

A generated migration looks like:

The upgrade function applies the change; downgrade reverses it. The version hashes form a chain (each migration knows which migration precedes it), so Alembic can move forward and backward through the history. Production deploys run alembic upgrade head as part of the rollout, after the code that depends on the new schema is in place but before it's serving requests.

The workflow above is enough to use Alembic on a project. The official Alembic docs cover the nuances: data migrations (not just schema), branches, offline mode for environments without database access.

SQLAlchemy fits when the application has multiple related tables, when schema portability matters, when complex queries appear in the codebase, or when the team wants the safety of typed model classes. It scales from a 50-line script up to a multi-million-line monolith. Codebases at Meta, Yelp, Reddit, and many others use SQLAlchemy as the data layer.

SQLAlchemy is overkill for a one-off script that touches a single table and runs ten lines of SQL. The investment in setting up a Base, defining a model, and learning sessions exceeds the savings. Use sqlite3 directly, or use SQLAlchemy Core without the ORM if the dialect and the connection pool are needed but not the object mapping.

The middle ground, "CRUD on a few tables without learning Sessions deeply," is well served by Peewee, SQLModel, or Tortoise. They're easier to learn, and the code is shorter. SQLAlchemy rewards investment; the lighter ORMs reward simplicity. Pick based on the project's size and complexity.

A realistic shape of an endpoint that lists a customer's recent orders, drawing on everything in this lesson:

The function takes a Session instead of building its own, which makes it easier to test and reuse. The query filters, orders, limits, and eager-loads the customer in one statement, so the print loop doesn't trigger any extra queries. The same pattern scales to dozens of endpoints with minor changes to the filters and the load options.