python-patterns/sources/sqlalchemy.md

SQLAlchemy source notes

Repo: sqlalchemy/sqlalchemy Local checkout: /home/ubuntu/repos/rodin-sources/sqlalchemy

Why this repo is useful

• SQLAlchemy is a strong source for persistence-boundary patterns: explicit session lifetime, transaction visibility, and parallel sync/async APIs.
• It is especially useful because the examples make lifecycle boundaries visible in ordinary calling code rather than hiding them in framework glue.

Sync and async persistence APIs are parallel but distinct

Repeated evidence

• examples/asyncio/async_orm.py:15-18 imports async-specific engine and session primitives.
• examples/asyncio/async_orm.py:51-67 builds an async engine and async_sessionmaker(...), then enters an async session and transaction block explicitly.
• examples/inheritance/joined.py:7-17 imports sync engine/session primitives separately.
• examples/inheritance/joined.py:90-120 uses Session(engine) with explicit add/commit calls in the synchronous path.

Why it matters

Repeated signal: SQLAlchemy does not pretend sync and async persistence are the same execution model. The APIs are conceptually parallel, but the entrypoints stay distinct.

Caveat / counterexample

The useful pattern is not "keep two unrelated APIs." examples/asyncio/async_orm.py:78-79 explicitly notes that async execution uses the same 2.0-style ORM execution concepts as the sync API. The separation is at runtime model and lifecycle, not at overall mental model.

Session and transaction lifetime are made visible in calling code

Repeated evidence

• examples/asyncio/async_orm.py:56-59 uses engine.begin() blocks for schema setup.
• examples/asyncio/async_orm.py:65-74 nests session.begin() inside async_session() so write scope is easy to see.
• examples/inheritance/joined.py:93-120 uses with Session(engine) as session: and makes the write boundary explicit with session.add(...) followed by session.commit().
• examples/inheritance/joined.py:133-135 shows a later mutation followed by another explicit session.commit() rather than hidden autoflush-as-commit semantics.

Why it matters

Repeated signal: SQLAlchemy favors visible unit-of-work boundaries. You can usually point to the exact lines where a session starts, a transaction begins, and persistence becomes durable.

Async examples surface async-specific caveats instead of hiding them

Repeated evidence

• examples/asyncio/async_orm.py:61-63 calls out expire_on_commit=False and explains the post-commit attribute-expiration consequence directly.
• examples/asyncio/async_orm.py:75-80 notes that eager loading should be applied for relationship loading in the async example.
• examples/asyncio/async_orm.py:106-107 shows the explicit AsyncAttrs path for lazy-loaded relationships via awaitable_attrs.

Why it matters

Repeated signal: the async API is not just a renamed sync API. SQLAlchemy documents where async changes loading and object-lifetime behavior, which is exactly the kind of caveat future synthesis should preserve.

Pattern candidates supported by this repo

• keep sync and async persistence entrypoints distinct but conceptually parallel
• make session and transaction scope visible in user code
• use explicit commit boundaries for writes
• preserve async-specific loading/lifecycle caveats rather than smoothing them over