Topolearn

Learn SQLAlchemy

Python's SQL toolkit and ORM: Core expression language plus mapped classes

22 lessons · 4 hours · 4 levels · covers SQLAlchemy 2.1

SQLAlchemy is the standard Python library for working with relational databases. Its Core layer builds SQL from Python expressions across PostgreSQL, MySQL, SQLite and more, and its ORM maps classes to tables with sessions, relationships and a unit of work. It sits under FastAPI, Flask and countless data pipelines, so knowing it well pays off in almost any Python back end.

Sign in free to track progressOfficial docs ↗

Before you start

SQLAlchemy builds on these; the tree links to the exact lessons you need.

PythonPractical levelSQLEssentials level

How deep do you want to go?

1. EssentialsRead and write simple code, do the everyday basics7 lessons · 1 hour in total
2. PracticalUse it productively at work, with the usual idioms14 lessons · 3 hours in total
3. AdvancedThe less common features, performance and trade-offs19 lessons · 4 hours in total
4. ExpertInternals and the edge cases experts know22 lessons · 4 hours in total

The SQLAlchemy tree

SQLAlchemyEngines, connections and text()Engines, connectionsand text()Core: MetaData and TableCore: MetaData andTableORM models with Mapped and mapped_columnORM models withMapped and mapp…The Session: add, commit and getThe Session: add,commit and getQuerying with select() and scalars()Querying withselect() and scalars()Filtering, ordering and limitsFiltering, orderingand limitsUpdating and deletingUpdating anddeletingrelationship() and one-to-manyrelationship() andone-to-manyJoins in select()Joins in select()Aggregates, group_by and labelsAggregates,group_by and labelsMany-to-many and association objectsMany-to-many andassociation objectsTransactions, flush and session lifecycleTransactions, flushand session lifecycleLoading strategies and the N+1 problemLoading strategiesand the N+1 problemSchema migrations with AlembicSchema migrationswith AlembicSELECT: reading dataSELECT: reading dataContext managers and withContext managersand withCreating and altering tablesCreating and alteringtablesType hints basicsType hints basicsForeign keys and referential integrityForeign keys andreferential integrityInner joinsInner joinsWindow functionsWindow functionsasync and await with asyncioasync and await withasyncio
Essentials and Practical shown (14 of 22 lessons); the full list is below. Click a lesson to read it.

All 22 lessons, in order

Engines and Core

  1. Engines, connections and text() · Essentials · 10 min

    Create an Engine, open a Connection, run raw SQL with text() and commit explicitly.

  2. Core: MetaData and Table · Essentials · 10 min

    Describe tables in Python with MetaData, Table and Column, create them, and build insert and select statements.

ORM models

  1. ORM models with Mapped and mapped_column · Essentials · 12 min

    Map Python classes to tables with DeclarativeBase, Mapped[] annotations and mapped_column().

  2. The Session: add, commit and get · Essentials · 10 min

    Use a Session to add objects, commit them to the database and load them back by primary key.

  3. Hybrid properties and computed columns · Advanced · 12 min

    Define attributes that work both on instances and in queries with hybrid_property, and use column_property for SQL-computed values.

Querying

  1. Querying with select() and scalars() · Essentials · 12 min

    Build ORM queries with select(Model), run them with session.scalars() or execute(), and pick the right result method.

  2. Filtering, ordering and limits · Essentials · 10 min

    Combine where() conditions with and_/or_, in_, like and is_(None), then sort and page with order_by, limit and offset.

  3. Updating and deleting · Essentials · 10 min

    Change objects by setting attributes, delete them with session.delete(), or run bulk update() and delete() statements.

  4. Joins in select() · Practical · 10 min

    Join related tables in a select() with join() along relationships or explicit ON clauses, including outer joins.

  5. Aggregates, group_by and labels · Practical · 10 min

    Count, sum and group rows with func, group_by and having, and name result columns with label().

  6. Subqueries, CTEs and window functions · Advanced · 14 min

    Compose queries with .subquery(), .cte(), scalar_subquery() and over() for window functions.

Relationships and loading

  1. relationship() and one-to-many · Practical · 12 min

    Link models with a ForeignKey column plus relationship() on both sides using back_populates, and control cascades.

  2. Many-to-many and association objects · Practical · 10 min

    Model many-to-many links with a secondary association table, or an association object when the link has its own data.

  3. Loading strategies and the N+1 problem · Practical · 12 min

    Avoid N+1 queries by choosing selectinload, joinedload or raiseload for relationships instead of default lazy loading.

Sessions and transactions

  1. Transactions, flush and session lifecycle · Practical · 12 min

    Understand flush versus commit, use session.begin() blocks, handle rollback, and scope sessions correctly in applications.

  2. Async SQLAlchemy · Advanced · 14 min

    Use create_async_engine and AsyncSession with an async driver, and avoid implicit IO from lazy loading.

Migrations and production

  1. Schema migrations with Alembic · Practical · 12 min

    Version your schema with Alembic: init, autogenerate revisions from your models, review them and upgrade or downgrade.

  2. Connection pooling and engine tuning · Advanced · 12 min

    Configure the engine's QueuePool (pool_size, max_overflow, pool_pre_ping, pool_recycle) and handle forks and serverless setups.

  3. Bulk inserts and upserts · Advanced · 12 min

    Load many rows fast with insert() and lists of dicts, use RETURNING, and upsert with on_conflict_do_update.

Internals and extension

  1. Unit of work and the identity map · Expert · 15 min

    How the Session tracks object state, orders flushes by dependency, and guarantees one object per row through the identity map.

  2. SQL compilation, dialects and caching · Expert · 14 min

    How statements compile to dialect SQL, how the statement cache works, and how to inspect compiled SQL and parameters.

  3. Custom types and events · Expert · 14 min

    Extend SQLAlchemy with TypeDecorator for custom column types and event listeners for engine and ORM hooks.