← SQLAlchemy and Alembic: reliable database work Progress & review ↗

Typorithm · easy

Session state, autoflush and durability in 2.1

pythonsqlalchemyalembicdatabasesindependent-practice

Saved on this device

Full lesson · 4 learning steps Open any exercise for focused practice

Step 1 of 4 · intro card

Session state, autoflush and durability in 2.1

Concept and contract

Observe an inserted row inside a transaction without confusing flush with a durable commit.

A Session coordinates ORM state and a unit of work; it is not simply a wrapper around a query function. A newly added object is pending until a flush sends its change to the database. Flushing can assign a primary key, but it still runs within a transaction that can be rolled back.

SQLAlchemy 2.1 makes normal execution-triggered autoflush unconditional across ORM and Core statements. Older explanations that a textual statement necessarily avoids autoflush are therefore unsafe for this version. The fixture makes that behavior visible rather than asking you to memorize a release-note sentence.

Mechanism and worked example

The example adds a Role, then executes a textual SELECT count(*) through the same Session.

The example adds a Role, then executes a textual SELECT count(*) through the same Session. Under the pinned 2.1 configuration, execution flushes the pending insert: during becomes one and assigned is True. The program then rolls back rather than committing.

A new Session observes after equal to zero. These three facts belong together: the database saw a row inside the first transaction, the object acquired an ID, and no row survived the rollback. Returning a generated ID alone would not prove that an insert became durable or visible to a later unit of work.

Complete worked example

python

from sqlalchemy import create_engine, select, text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class Role(Base):
    __tablename__ = "roles"
    id: Mapped[int] = mapped_column(primary_key=True)
    score: Mapped[int]

def inspect_example(score):
    engine = create_engine("sqlite+pysqlite://", connect_args={"autocommit": False})
    try:
        Base.metadata.create_all(engine)
        with Session(engine) as session:
            role = Role(score=score)
            session.add(role)
            during = session.scalar(text("SELECT count(*) FROM roles"))
            assigned = role.id is not None
            session.rollback()
        with Session(engine) as check:
            after = check.scalar(text("SELECT count(*) FROM roles"))
        return {"during": during, "assigned": assigned, "after": after}
    finally:
        engine.dispose()

Reasoning: The example adds a Role, then executes a textual SELECT count(*) through the same Session. Under the pinned 2.1 configuration, execution flushes the pending insert: during becomes one and assigned is True. The program then rolls back rather than committing. A new Session observes after equal to zero. These three facts belong together: the database saw a row inside the first transaction, the object acquired an ID, and no row survived the rollback. Returning a generated ID alone would not prove that an insert became durable or visible to a later unit of work.

Download the SQLAlchemy and Alembic computer lab ↗

Focused decision: context is in the complete example

python

session.add(role)
during = session.scalar(text("SELECT count(*) FROM roles"))
session.rollback()

Database decision: In 2.1 the textual Session SELECT flushes pending work by default. Rollback still discards the insert; changing it to commit changes the fresh-session result.

Download the SQLAlchemy and Alembic computer lab ↗

Counterexamples and common mistakes

The controlled mistake uses no_autoflush around the textual SELECT.

The controlled mistake uses no_autoflush around the textual SELECT. The count is then zero and no ID has been assigned at the observed point, contradicting the example’s documented execution contract. Disabling autoflush is sometimes intentional, but it changes when pending changes become observable.

Do not teach flush as a synonym for commit or assume that an ID proves persistence. commit and begin_nested still flush pending work at their transaction boundaries even when ordinary autoflush is disabled. After a failed flush, the Session needs an appropriate rollback before it can be used for another unit of work.

Controlled bug: observe the rejected database contract

python

from sqlalchemy import create_engine, select, text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class Role(Base):
    __tablename__ = "roles"
    id: Mapped[int] = mapped_column(primary_key=True)
    score: Mapped[int]

def inspect_example(score):
    engine = create_engine("sqlite+pysqlite://", connect_args={"autocommit": False})
    try:
        Base.metadata.create_all(engine)
        with Session(engine) as session:
            role = Role(score=score)
            session.add(role)
            with session.no_autoflush:
                during = session.scalar(text("SELECT count(*) FROM roles"))
            assigned = role.id is not None
            session.rollback()
        with Session(engine) as check:
            after = check.scalar(text("SELECT count(*) FROM roles"))
        return {"during": during, "assigned": assigned, "after": after}
    finally:
        engine.dispose()

Reasoning: The controlled mistake uses no_autoflush around the textual SELECT. The count is then zero and no ID has been assigned at the observed point, contradicting the example’s documented execution contract. Disabling autoflush is sometimes intentional, but it changes when pending changes become observable. Do not teach flush as a synonym for commit or assume that an ID proves persistence. commit and begin_nested still flush pending work at their transaction boundaries even when ordinary autoflush is disabled. After a failed flush, the Session needs an appropriate rollback before it can be used for another unit of work.

Download the SQLAlchemy and Alembic computer lab ↗

Independent practice

First type the add, SELECT and rollback sequence, predicting each reported field before running the lab.

First type the add, SELECT and rollback sequence, predicting each reported field before running the lab. The independent task changes the durability requirement: complete the successful transaction so that the fresh Session sees one stored row. Keep the during count and generated-ID observation unchanged.

Find the transaction-completion operation rather than adding a second insert or changing the reported number. Try a zero score and a negative score too: neither changes the number of rows in this particular contract. The task is about lifecycle, not filtering score values or inventing a new validation rule.

Step 1: Commit the successful unit of work so a new session can see the inserted row. Step 2: Keep the count and ID observations before transaction completion.

Practice starter: solve before reading the solution

python

from sqlalchemy import create_engine, select, text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class Role(Base):
    __tablename__ = "roles"
    id: Mapped[int] = mapped_column(primary_key=True)
    score: Mapped[int]

def inspect_example(score):
    engine = create_engine("sqlite+pysqlite://", connect_args={"autocommit": False})
    try:
        Base.metadata.create_all(engine)
        with Session(engine) as session:
            role = Role(score=score)
            session.add(role)
            during = session.scalar(text("SELECT count(*) FROM roles"))
            assigned = role.id is not None
            session.rollback()
        with Session(engine) as check:
            after = check.scalar(text("SELECT count(*) FROM roles"))
        return {"during": during, "assigned": assigned, "after": after}
    finally:
        engine.dispose()

# Task: Commit the successful unit of work so a new session can see the inserted row.

Reasoning: Commit the successful unit of work so a new session can see the inserted row.

Download the SQLAlchemy and Alembic computer lab ↗

Hint 1: Change the final operation on the first Session.
Hint 2: Prove durability with the second Session rather than a literal return value.

Solution, tests and trade-offs

The solution replaces rollback with commit.

The solution replaces rollback with commit. The same execution-triggered autoflush still happens, but the later Session now observes after equal to one. The accepted cases compare the full report for several score values, while the unfinished starter continues to fail the new durability requirement.

The code returns primitive report values, not an attached ORM object whose later attribute access could trigger more work. This makes the observation independent of a live Session. A long-lived application should put commit ownership at a clear service boundary rather than allowing unrelated helper functions to commit one another’s pending objects.

Practice solution and acceptance checks

python

from sqlalchemy import create_engine, select, text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class Role(Base):
    __tablename__ = "roles"
    id: Mapped[int] = mapped_column(primary_key=True)
    score: Mapped[int]

def inspect_example(score):
    engine = create_engine("sqlite+pysqlite://", connect_args={"autocommit": False})
    try:
        Base.metadata.create_all(engine)
        with Session(engine) as session:
            role = Role(score=score)
            session.add(role)
            during = session.scalar(text("SELECT count(*) FROM roles"))
            assigned = role.id is not None
            session.commit()
        with Session(engine) as check:
            after = check.scalar(text("SELECT count(*) FROM roles"))
        return {"during": during, "assigned": assigned, "after": after}
    finally:
        engine.dispose()

Reasoning: The solution replaces rollback with commit. The same execution-triggered autoflush still happens, but the later Session now observes after equal to one. The accepted cases compare the full report for several score values, while the unfinished starter continues to fail the new durability requirement. The code returns primitive report values, not an attached ORM object whose later attribute access could trigger more work. This makes the observation independent of a live Session. A long-lived application should put commit ownership at a clear service boundary rather than allowing unrelated helper functions to commit one another’s pending objects.

Download the SQLAlchemy and Alembic computer lab ↗

Interview reasoning and next step

Explain the sequence pending, flushed and committed using the actual fixture reports.

Explain the sequence pending, flushed and committed using the actual fixture reports. State why a rollback removes the row despite the already assigned ID, and why a second Session is useful evidence for the final durable state. Distinguish that evidence from a cached Python attribute.

When reviewing a 2.0-to-2.1 migration, search for code that depended on Core or textual Session execution avoiding autoflush. Recheck exception timing and pending-object assumptions instead of simply turning autoflush off everywhere. Next, examine how SQL rows, scalar columns and ORM entities shape a query result.

Optional stretch — after the core task works: Add a case where a failed flush is rolled back before a new transaction begins.

Computer practice: https://applydjinn.com/typorithm/sqlalchemy-lab-20261107.zip. Install the pinned requirements in an isolated Python 3.13 environment and run verify.py. Edit the working exercise baseline, then run --practice sa-session. The lab uses disposable SQLite databases and actual SQLAlchemy/Alembic calls; it does not connect to application accounts, production databases or paid services. The app includes prepared results and explained questions without an editor or Python runtime.

Observe an inserted row inside a transaction without confusing flush with a durable commit.