Under SQLAlchemy's asyncio extension, how do AsyncAttrs.awaitable_attrs and AsyncSession.refresh() with attribute names load an attribute on demand, and how do they differ?
answer
- make the load awaitable
- mixin on the declarative Base
- getattr inside greenlet_spawn
- refresh needs the relationship named
basics
~20 sawait obj.awaitable_attrs.items runs the attribute's normal loader inside an awaitable, so it loads only what is missing. await session.refresh(obj, ["items"]) expires the named attributes and re-selects them, loading a lazy relationship only when it is named.
solid answer
~40 sAdding the `AsyncAttrs` mixin to the declarative `Base` gives every instance an `awaitable_attrs` namespace; `await order.awaitable_attrs.items` wraps the ordinary `getattr` in `greenlet_spawn()`, so whatever loader the attribute has (lazy relationship, deferred column, expired column) runs and can await its IO. If the value is already loaded, no SQL is sent. It respects the mapping, so a relationship declared `lazy="raise"` still raises. `await session.refresh(order, ["items"])` instead expires the named attributes and selects them again; a lazy relationship loads only when its name is in `attribute_names`, and naming it works even with `lazy="raise"`. A bare `refresh(order)` re-reads columns but leaves lazy relationships unloaded, even ones loaded earlier. Both work one object at a time, so a loop over many objects becomes one query per object.
code
python · 53 linesimport asyncio
from sqlalchemy import ForeignKey, String
from sqlalchemy.ext.asyncio import AsyncAttrs, async_sessionmaker, create_async_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
class Base(AsyncAttrs, DeclarativeBase):
pass
class Order(Base):
__tablename__ = "orders"
id: Mapped[int] = mapped_column(primary_key=True)
status: Mapped[str] = mapped_column(String(20))
notes: Mapped[str] = mapped_column(String(500), deferred=True)
items: Mapped[list["OrderItem"]] = relationship()
class OrderItem(Base):
__tablename__ = "order_items"
id: Mapped[int] = mapped_column(primary_key=True)
order_id: Mapped[int] = mapped_column(ForeignKey("orders.id"))
sku: Mapped[str] = mapped_column(String(20))
async def main() -> None:
engine = create_async_engine("sqlite+aiosqlite://")
async with engine.begin() as conn:
await conn.run_sync(Base.metadata.create_all)
Session = async_sessionmaker(engine, expire_on_commit=False)
async with Session() as session:
session.add(Order(status="new", notes="leave at door", items=[OrderItem(sku="A1")]))
await session.commit()
async with Session() as session:
order = await session.get(Order, 1)
# AsyncAttrs: run the normal loader for one attribute, awaited
items = await order.awaitable_attrs.items
notes = await order.awaitable_attrs.notes
print([i.sku for i in items], notes)
# refresh() with no names re-reads columns but leaves the
# lazily loaded collection expired again
await session.refresh(order)
# naming the relationship makes refresh() load it
await session.refresh(order, ["items"])
print([i.sku for i in order.items])
await engine.dispose()
asyncio.run(main())go deeper
Recall the two explicit ways to load one attribute under asyncio: await obj.awaitable_attrs.name after adding AsyncAttrs to the Base, and await session.refresh(obj, ["name"]).
Explain that awaitable_attrs runs the attribute's own loader inside greenlet_spawn and skips SQL when loaded, while refresh always re-selects and loads a lazy relationship only when named.
Spot the misuse: awaitable_attrs inside a loop creating N+1 queries, a bare refresh() leaving a collection unloaded, refresh overwriting in-memory values, and cascade all expiring related objects.
Decide where these escape hatches are allowed: as rare per-object reads, never as a substitute for loader options on the paths a service always runs.
## Why an explicit loader is needed at all Under `AsyncSession`, any attribute read that would run SQL raises `MissingGreenlet`, because a plain read cannot `await` and SQLAlchemy refuses to block the event loop. Eager loader options in the query are the first answer. For the cases where the query could not know what will be needed, SQLAlchemy offers two explicit, awaitable ways to load one attribute on one object: the **`AsyncAttrs`** mixin and **`AsyncSession.refresh()`** with attribute names. ## AsyncAttrs.awaitable_attrs Add the mixin to the declarative base, `class Base(AsyncAttrs, DeclarativeBase): pass`, and every mapped class gains a property `awaitable_attrs`. Reading `order.awaitable_attrs.items` returns an awaitable that performs `getattr(order, "items")` inside `greenlet_spawn()`, the same bridge the awaitable `AsyncSession` methods use. The documentation describes it as approximately equivalent to `await session.run_sync(lambda sess: order.items)`. What that means in practice: - **It runs the attribute's own loader.** A lazy relationship runs its lazy SELECT, a deferred column its column SELECT, an expired attribute its refresh. Nothing about the strategy changes; it simply becomes awaitable. - **It loads only what is missing.** If the attribute is already in the object's state, the await returns it without SQL. - **It respects the mapping.** On a relationship declared with `lazy="raise"`, `await order.awaitable_attrs.items` raises `InvalidRequestError` ("'Order.items' is not available due to lazy='raise'"), exactly as a plain read would. - **It works for any attribute name**, columns included, which makes it the general escape hatch for "this one value might not be loaded". ## AsyncSession.refresh() with attribute_names `await session.refresh(order, ["items"])` proxies `Session.refresh()`: it first **expires** the named attributes, then issues a SELECT to load them again. Since 2.0.4, a relationship that would normally load lazily is loaded too, but **only if it is named** in `attribute_names`; it is then loaded with the immediate strategy. - **It always goes to the database** for the named attributes, even if they were loaded, so it also serves as "re-read this from the current transaction". - **It overrides the relationship's strategy.** Naming a relationship declared `lazy="raise"` still loads it, which makes refresh the sanctioned way to load such a relationship on one object. - **Without names it is not a loader for relationships.** `await session.refresh(order)` re-reads the column attributes; lazy relationships, even ones loaded earlier by a lazy load, are expired and stay unloaded, so the next plain read raises again. Relationships loaded by an eager strategy are reloaded with that strategy. ## Side by side | | `await obj.awaitable_attrs.x` | `await session.refresh(obj, ["x"])` | |---|---|---| | needs | `AsyncAttrs` on the class | an `AsyncSession` in scope | | SQL when already loaded | none | always re-selects | | on `lazy="raise"` | raises `InvalidRequestError` | loads it | | works on columns | yes | yes, re-read | | typical use | an optional attribute, read once | fresh values, or a guarded relationship | ## Where both go wrong 1. **Loops.** Awaiting `awaitable_attrs.items` for each of 100 orders sends 100 SELECTs: an N+1 made explicit. Load collections for a list with `selectinload()` in the query instead. 2. **Discarded state.** Refresh expires the named attributes before reloading them, so in-memory values on those attributes are replaced by what the database holds in the current transaction. 3. **Cascades.** A relationship with `cascade="all"` includes `refresh-expire`, so refreshing the parent can expire related objects without reloading them; SQLAlchemy's asyncio guidance advises listing cascades explicitly. ## Choosing between them 1. **The value may or may not be loaded, and any loaded copy is fine**: `awaitable_attrs`. It costs nothing when the query already loaded the attribute. 2. **The value must reflect the database now**, for example a counter another transaction may have changed: `refresh()` with the attribute named, ideally early in the transaction. 3. **The relationship is guarded by `lazy="raise"`** and one code path legitimately needs it on one object: `refresh()` with its name, which is explicit and greppable. 4. **The code path always needs it, for many objects**: neither. Put `selectinload()` or `joinedload()` in the query. Neither tool should be the first choice for data a code path always needs: that belongs in the query's loader options. They are for the occasional, per-object read the query could not predict.
- What does await order.awaitable_attrs.items return when items is already loaded?The collection itself, with no SQL. `awaitable_attrs` only wraps `getattr` in an awaitable; when the attribute is present in the object's state, the getattr finds it and nothing is loaded. That makes it safe to use defensively on a value that may or may not have been eagerly loaded by the query.
- How do you reload several relationships on many objects at once, rather than per object?Re-run a query for those objects with the loader options you need and the `populate_existing` execution option, for example `select(Order).where(Order.id.in_(ids)).options(selectinload(Order.items)).execution_options(populate_existing=True)`. `Session.refresh()` documents this as the tool for open-ended refreshes across many objects with explicit loader control.
- awaitable_attrs bypasses lazy='raise' because the load is awaited.
- await session.refresh(order) with no names reloads every relationship on the object.
- awaitable_attrs batches the loads for every object of the same class in the session.
- AsyncAttrs is a session option, passed to async_sessionmaker.
- refresh() keeps unflushed in-memory values on the attributes it reloads.