Tech stack

SQLAlchemy

The Python SQL toolkit and ORM I use to talk to PostgreSQL, in modern 2.x style, with Alembic for migrations and pytest for tests.

Learningsince Nov 2026
Kind
  • Library
Domain
  • Backend
  • Data
Lifecycle
  • Design
  • Build
  • Test
Learn first
Links

Why I’m learning it

I write Python every day for ML work, but almost always against files, object stores or a feature store, rarely a relational database. To build proper backend services I need a solid way to talk to PostgreSQL from Python, and SQLAlchemy is the standard: it is both a SQL toolkit (Core) and an ORM, and the ORM sits on top of Core rather than hiding it. I used Hibernate in a Java and Spring job, so the ORM ideas (sessions, identity map, lazy loading) are familiar, but I want to learn the modern 2.x API properly rather than copying old session.query() snippets from the internet.

How it fits with the rest of my stack

SQLAlchemy is the layer between Python code and PostgreSQL, using the psycopg 3 driver. Alembic, written by the same author, turns changes to my models into migration scripts. Locally, Postgres runs in Docker and Docker Compose; in CI, the same postgres:18 image can run as a service container so that pytest exercises real Postgres behaviour. Everything I learn on the PostgreSQL roadmap (constraints, indexes, isolation levels) shows up here as model options, query options and session settings.

SQLAlchemy 2.1 is the current release. It needs Python 3.11 or newer, and postgresql:// URLs now default to psycopg 3, though I always write the driver explicitly as postgresql+psycopg://.

How to use this roadmap

Do the stages in order, after at least the first two PostgreSQL stages.

  1. Core basics: two or three evenings. Skipping Core makes the ORM feel like magic.
  2. ORM models and relationships: about a week.
  3. Querying with select: about a week; this is where most real bugs (N+1, accidental lazy loads) live.
  4. Sessions and transactions in an app: a few evenings, paired with the PostgreSQL transactions stage.
  5. Migrations with Alembic: about a week. From this point on, schema changes go through migrations and create_all() is for tests only.
  6. Testing: about a week, finishing with the suite running against a real PostgreSQL service in CI.

The notes share one small example domain (warehouses and items), so each stage builds on the last.

Learning roadmap

6 stages, in order. Each one ends with a check for when it's done, and links the notes that go with it.

  1. Core basics

    Connect to Postgres from Python, run parameterised SQL and control exactly when a transaction commits.

    Learn

    • The engine as a long-lived factory and connection pool, created once per process
    • Database URLs with an explicit driver, postgresql+psycopg://user:pass@host:5432/db
    • text() with bound parameters, and why f-strings in SQL are an injection bug
    • Commit as you go with engine.connect() versus begin once with engine.begin()
    • Result objects - rows, scalar_one(), scalars() and mappings()

    Practise

    • Install sqlalchemy and psycopg[binary] with uv and connect to the Postgres container
    • Insert and read inventory rows with text() and named parameters
    • Turn on echo=True and watch BEGIN, COMMIT and ROLLBACK in the log

    Done when

    • I can say which code path commits and which rolls back without running it
    • No SQL in my code is built with string formatting
  2. ORM models and relationships

    Model Warehouse and Item as typed ORM classes that produce the same schema I would write by hand.

    Learn

    • DeclarativeBase, Mapped[] annotations and mapped_column(), and how nullability follows from Optional types
    • Choosing column types - Numeric for prices with Decimal, DateTime(timezone=True), Identity() keys
    • relationship() with back_populates, and cascade="all, delete-orphan" versus ON DELETE CASCADE
    • Constraints in the model - unique, index, CheckConstraint and ForeignKey
    • Object states (transient, pending, persistent, detached) and the identity map

    Practise

    • Write the Warehouse and Item models and compare the generated DDL with my hand-written SQL
    • Add items through warehouse.items and check what gets flushed and in which order
    • Delete a warehouse and observe what happens to its items with and without delete-orphan

    Done when

    • The ORM-generated DDL matches the schema from the PostgreSQL roadmap
    • I can explain the difference between flush and commit
  3. Querying with select

    Write every query a small inventory app needs with select(), without N+1 problems.

    Learn

    • select(), where(), order_by(), limit() and the Session.scalars and Session.execute entry points
    • Joins along relationships, aggregates with func and group_by, and getting rows rather than objects
    • Lazy loading and the N+1 problem
    • selectinload, joinedload (and why it needs unique() for collections) and raiseload
    • Bulk UPDATE and DELETE statements versus changing objects one by one

    Practise

    • Implement list-items, low-stock and stock-value-per-warehouse queries
    • Log SQL while rendering a list of warehouses with items, then fix the N+1 with selectinload
    • Set lazy="raise" on a relationship and see which code paths break

    Done when

    • Listing warehouses with their items issues a fixed number of queries regardless of row count
    • I never reach for the legacy session.query() API
  4. Sessions and transactions in an app

    Use one session per unit of work in a small inventory app, with commits and rollbacks in exactly one place.

    Learn

    • sessionmaker as the session factory, configured once alongside the engine
    • Session per request, and why sessions must never be shared between threads or requests
    • The begin-once pattern, with Session.begin() committing or rolling back automatically
    • expire_on_commit, DetachedInstanceError, and returning plain data from the service layer
    • Isolation levels, with_for_update(), and retrying serialisation failures

    Practise

    • Write a get_session dependency that yields a session and closes it after the request
    • Implement receive-stock with SELECT ... FOR UPDATE and test it with two concurrent threads
    • Make a request fail halfway through and confirm nothing from it was committed

    Done when

    • There is exactly one place in the app that calls commit
    • Concurrent stock updates never lose an increment
  5. Migrations with Alembic

    Evolve the inventory schema through reviewed, reversible migrations instead of create_all().

    Learn

    • alembic init, env.py, target_metadata and reading the database URL from the environment
    • Autogenerate, and what it cannot detect (renames, anonymous constraints, some type changes)
    • Naming conventions on MetaData so every constraint has a stable name
    • upgrade, downgrade, current, history and alembic check
    • Data migrations with op.execute, and expand-then-contract changes for live systems

    Practise

    • Generate the initial migration for Warehouse and Item and read every line before applying it
    • Rename a column and fix the autogenerated drop-and-add into alter_column
    • Run upgrade head, downgrade -1 and upgrade head again on a database with data in it

    Done when

    • A fresh database reaches the current schema with a single alembic upgrade head
    • alembic check passes in CI, so models and migrations never drift
  6. Testing

    Test database code against a real Postgres, fast and isolated, locally and in CI.

    Learn

    • Why SQLite is not a substitute for Postgres in tests
    • pytest fixtures and scopes - session-scoped engine, function-scoped connection and session
    • The external transaction pattern with join_transaction_mode="create_savepoint"
    • Running Postgres for tests in Docker locally and as a service container in GitHub Actions
    • Testing migrations as well as models

    Practise

    • Write conftest.py fixtures that roll back every test, and prove it with a test that counts rows
    • Test that the CHECK constraint on quantity raises IntegrityError
    • Run the suite in GitHub Actions against a postgres:18 service container

    Done when

    • Tests can run in any order and leave the database empty
    • The CI pipeline runs alembic upgrade head and the full test suite on every push

Resources

Projects using SQLAlchemy

No projects yet.