SQLAlchemy Scoped Sessions: The Footgun Nobody Warns You About

2026-09-23

BudgetFlow uses SQLAlchemy's scoped_session to make a session available during each Flask request. The convenience depends on cleaning up that session at the right time.

Here is the relevant setup from BudgetFlow:

from sqlalchemy.orm import scoped_session, sessionmaker

db_session: scoped_session[Session] = scoped_session(
	sessionmaker(
		autocommit=False,
		autoflush=False,
		expire_on_commit=True,
	)
)

The settings matter, but the first thing to understand is what scoped_session gives you when code accesses db_session.

What scoped_session Actually Is

scoped_session is a registry. With the default thread-local scope, it gives code in the same thread the same Session until that session is removed.

A later db_session.query(User) in that thread reuses the session and its transaction state. This is handy within a request, but only if the request teardown calls remove().

In BudgetFlow, the intended scope is one request. Thread-local storage by itself does not enforce that boundary; Flask's teardown handler does.

The Identity Map Can Keep Old Values

A session keeps an identity map of objects it has loaded. If another session changes a row, querying the same object again in the original session can return the existing Python object with its old attribute values.

# Request A
user = db_session.execute(
	select(User).where(User.id == 1)
).scalar_one()
print(user.name)  # "Alice"

# Meanwhile, Request B commits: UPDATE users SET name = 'Bob' WHERE id = 1

# Still in Request A
user = db_session.execute(
	select(User).where(User.id == 1)
).scalar_one()
print(user.name)  # Still "Alice", from the identity map

To refresh it, expire the object or end the transaction before reading again. Whether a new read can see another transaction's change also depends on the database's isolation rules.

Remove the Session at Teardown

A worker thread can handle another request later. Without cleanup, that request may inherit the previous session and its pending work. BudgetFlow registers this teardown function with Flask:

def shutdown_session(_: BaseException | None) -> None:
	db_session.remove()

app.teardown_appcontext(shutdown_session)

remove() closes the current session and clears it from the registry. The next request on that thread gets a new session.

What autoflush=False Changes

SQLAlchemy normally flushes pending changes before a query. BudgetFlow sets autoflush=False, so a query will not see a newly added object until the session flushes or commits.

For example:

new_budget = Budget(name="Holidays", user_id=1, interval=FlowInterval.monthly)
db_session.add(new_budget)

# With autoflush=False, this query does NOT see the new budget
budgets = db_session.execute(
	select(Budget).where(Budget.user_id == 1)
).scalars().all()

# new_budget is NOT in the list. It's pending, not flushed.

With autoflush enabled, the query would flush the pending insert first. With it disabled, code that needs the new row in a query must call flush() explicitly.

SQLite Transactions Need to Be Short

SQLite allows one writer at a time, including in WAL mode. BudgetFlow configures SQLAlchemy to start transactions with BEGIN IMMEDIATE, which acquires the write reservation at the start of a transaction.

If a request opens a transaction and then spends time doing unrelated work, other writers can wait on it. That is a reason to keep the transaction as short as the operation allows.

The database module describes the current behaviour this way:

As soon as it is used in any way, it will lead to a write lock on the database, which will persist from then until the end of the request, or until .remove(), .commit(), .rollback(), or .close() is called on the session.

In particular, avoid leaving a transaction open during a slow external request or while rendering a response.

Rules We Follow

The useful rules come from the session lifecycle:

Call db_session.remove() at request teardown.
Choose where an operation commits, and make that boundary visible to callers.
Remember that expire_on_commit=True expires loaded objects after a commit; later attribute access may run another query.
Keep SQLite transactions short, especially when other requests may write.

Other Execution Models

A request lifecycle gives scoped_session a clear cleanup point. Other execution models need their own scope and cleanup rules.

In particular:

For background jobs, create a session for each job and close it when the job ends.
For CLI scripts, make the session lifetime explicit, especially in long-running scripts.
For asyncio, do not share a thread-local session across concurrent tasks. Use an async session with a task-aware lifecycle.

The registry saves us from passing a session through every Flask function. Its value depends on the teardown handler and on developers knowing when a transaction starts and ends.

If you use the same pattern, check where remove() runs and which code can hold a transaction open. Those two details explain most of the surprises we encountered.