SQLAlchemy — Session

FrameworksSeniorormsql

The Question

How does the SQLAlchemy session work?

What a Strong Answer Covers

• unit of work
• "scoped_session = thread-local

Senior-Level Answer

The SQLAlchemy Session is the core of the ORM's unit-of-work pattern. Understanding it prevents the most common bugs: detached instance errors, unexpected autoflush, and transaction leaks.

**Unit of Work**

The Session acts as a transaction-scoped identity map and change tracker. When you add objects to the session or query objects through it, they enter the **identity map** — a dict keyed by primary key. The identity map guarantees that within a session, loading the same row twice gives you the same Python object, not two separate instances.

The session tracks each object's state: - **Transient:** created with `MyClass()` but not yet added to a session - **Pending:** added via `session.add()` but not yet flushed to the DB - **Persistent:** in the session and has a corresponding row in the DB (either just inserted via flush, or loaded via query) - **Detached:** was persistent, then the session was closed or the object was expunged. Database identity exists but no session tracks it. Accessing lazy-loaded relationships raises `DetachedInstanceError`.

**Flush vs Commit**

`session.flush()` writes pending changes to the database as SQL (INSERTs, UPDATEs, DELETEs) within the current transaction, but does not commit. The changes are visible within the same transaction but not to other transactions. SQLAlchemy autoflushes by default before queries to ensure the query sees current state.

`session.commit()` flushes and then commits the transaction. After commit, persistent objects are expired — their attributes will be lazily reloaded on next access.

`session.rollback()` reverts the transaction and expires all objects.

**Scoped Sessions**

In web applications, each request should have its own session. The `scoped_session` factory creates a registry that returns the same session object within the same thread (using thread-local storage by default). In async applications, use `AsyncSession` with `async_scoped_session` keyed on the async task.

The correct pattern with FastAPI: ```python async def get_db(): async with AsyncSessionLocal() as session: yield session # session.close() called automatically ```

**Common pitfalls:** - Accessing lazy-loaded attributes after the session is closed: use `joinedload` or `selectinload` eagerly, or access within the session scope - Long-lived sessions in background jobs accumulate stale state — call `session.expire_all()` or use short-lived sessions - Not committing or rolling back on exception — use `try/except/finally` or a context manager - Autoflush firing at unexpected times — SQL triggered by a query when you expected no DB activity

What Separates a 2/3 from a 3/3

Common Mistakes

• Confusing flush and commit — flush writes SQL, commit makes it durable and visible to others
• Not understanding DetachedInstanceError — trying to access lazy attributes after session close
• Using a single global session in a web app — sessions are not thread-safe without scoped_session
• Not expiring/refreshing long-lived sessions that may have stale data

Follow-Up Questions

• What is the identity map and why does it matter for your application? — Same PK within a session = same object. Prevents duplicate instances, ensures mutations are tracked once. Matters when loading the same object from two code paths.
• What causes DetachedInstanceError and how do you fix it? — Accessing a lazy-loaded relationship after the session is closed. Fix: eager load with joinedload/selectinload, or keep the access within the session scope.
• Why does session.commit() expire all objects? — After commit, another transaction may have modified the rows. Expired objects reload from DB on next access, ensuring you see current state.
• How does scoped_session work in a multithreaded web server? — It uses threading.local() to maintain a registry of sessions keyed by thread. Each request thread gets its own session. Must be removed (session.remove()) at request end.

Related Questions

• FastAPI — Async vs Sync
• Django — Signals
• Django — select_related vs prefetch_related
• Django — Middleware
• FastAPI — Pydantic