skip to content

Why do SQLAlchemy's asyncio docs recommend WriteOnlyMapped collections, and how do you read and add to one through an AsyncSession?

level: middleimportance: nice to knowfreq 20%

answer

  1. a collection that never loads
  2. lazy="write_only" strategy
  3. iteration raises, select() instead
  4. add() queues for the next flush

basics

~10 s

A WriteOnlyMapped collection never loads itself, so it can never cause implicit IO. Read it with await session.scalars(order.status_log.select()); add with order.status_log.add(...), which is queued and inserted at the next flush.

solid answer

~40 s

Annotating a relationship as `WriteOnlyMapped[StatusChange]` gives it the `"write_only"` strategy: the attribute is a `WriteOnlyCollection` that holds pending changes but never loads rows, so no attribute access on it can trigger a lazy load under asyncio. Iterating it raises `TypeError` with a hint to use `select()`. To read, build the statement and await it: `await session.scalars(order.status_log.select().order_by(StatusChange.id))`, adding any filter or limit you need. To write, `add()`, `add_all()` and `remove()` queue changes that the next flush turns into INSERTs or foreign-key updates, without loading anything. `insert()`, `update()` and `delete()` build bulk statements scoped to the parent. The docs call it the predictable choice for collections under asyncio; the cost is that nothing batches loading of such collections across many parents, so that query is yours to write.

code

python · 53 lines
python
import asyncio

from sqlalchemy import ForeignKey, String
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine
from sqlalchemy.orm import (
    DeclarativeBase,
    Mapped,
    WriteOnlyMapped,
    mapped_column,
    relationship,
)


class Base(DeclarativeBase):
    pass


class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    status: Mapped[str] = mapped_column(String(20))
    status_log: WriteOnlyMapped["StatusChange"] = relationship()


class StatusChange(Base):
    __tablename__ = "status_changes"
    id: Mapped[int] = mapped_column(primary_key=True)
    order_id: Mapped[int] = mapped_column(ForeignKey("orders.id"))
    status: 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", status_log=[StatusChange(status="new")]))
        await session.commit()

    async with Session() as session:
        order = await session.get(Order, 1)
        order.status = "paid"
        order.status_log.add(StatusChange(status="paid"))  # queued, no IO
        await session.commit()

        stmt = order.status_log.select().order_by(StatusChange.id)
        changes = (await session.scalars(stmt)).all()
        print([c.status for c in changes])
    await engine.dispose()


asyncio.run(main())

go deeper

for a junior

Recall that a WriteOnlyMapped collection is never loaded: you read it with session.scalars(collection.select()) and add to it with add(), which is saved at the next flush.

for a middle

Explain why it removes implicit IO entirely, what iteration and whole-list assignment do, and how select(), add(), remove(), insert(), update() and delete() map to SQL.

for a senior

Decide per relationship: large or filtered collections become write-only, small always-shown ones stay ordinary with selectinload(), and batch reads of write-only children are written by hand.

for a principal

Judge whether a team standardises write-only for unbounded collections in async services, accepting more explicit query code in exchange for no implicit loading at all.

## The problem a write-only collection removes Under `AsyncSession`, an ordinary collection relationship is a hazard: its first read runs a lazy SELECT, which a plain attribute access cannot await, so it raises `MissingGreenlet` unless an eager loader option filled it first. The asyncio documentation lists several ways around that, and the most structural is to make the collection one that **never loads at all**. SQLAlchemy 2.0 introduced exactly that as the **write-only** relationship, and its asyncio guidance says it is fully compatible with asyncio and preferred over the older `"dynamic"` loader, which does not work under asyncio by default. ## Declaring one With annotated declarative mapping, the annotation selects the strategy: ```python class Order(Base): __tablename__ = "orders" id: Mapped[int] = mapped_column(primary_key=True) status_log: WriteOnlyMapped["StatusChange"] = relationship() ``` `WriteOnlyMapped[...]` is equivalent to `relationship(lazy="write_only")`. On an instance, `order.status_log` is a `WriteOnlyCollection`: an object that records pending additions and removals and can build SQL, but holds no loaded rows. ## Reading it - **Iteration is refused.** `for c in order.status_log:` raises `TypeError`: "WriteOnly collections don't support iteration in-place; to query for collection items, use the select() method to produce a SQL statement and execute it with session.scalars()." No IO is attempted, so the failure is immediate and deterministic. - **`select()` builds the query.** `order.status_log.select()` returns a `Select` of the target class with the parent's criteria already applied (`WHERE :param_1 = status_changes.order_id`). Add ordering, filters or `limit()` as usual, then run it: `(await session.scalars(stmt)).all()`. - **Awaiting the attribute does not load it.** `await order.awaitable_attrs.status_log` just returns the `WriteOnlyCollection` itself; there is nothing to load. ## Writing to it 1. **`add()` and `add_all()`** queue new objects; the next flush INSERTs them with the parent's foreign key set. Nothing is read first, so this works on a parent with millions of children. 2. **`remove()`** queues the removal of an object from the collection, applied at the next flush. 3. **`insert()`, `update()` and `delete()`** produce Core-style statements scoped to the parent, such as `await session.execute(order.status_log.insert(), [{"status": "shipped"}])`. `insert()` is supported only for one-to-many; for many-to-many, the documented route is a separate bulk insert followed by `add_all()`. Assigning a whole new list to the attribute of an object that is already persistent raises `InvalidRequestError` ("collection replacement operations can't be used"), because replacement would need to load the old contents. Passing a list to the constructor of a new object is fine. ## Pending changes and autoflush Objects queued with `add()` are not visible on the attribute, because there is nothing to iterate. They become visible to queries once flushed. With the session's default `autoflush=True`, running `await session.scalars(order.status_log.select())` flushes pending changes first, so the query does return the row just added; with `no_autoflush` or `autoflush=False` it would not until an explicit `await session.flush()`. ## Common mistakes - Iterating the attribute in a template or serializer, expecting a list. - Replacing the collection wholesale on a loaded object instead of using `add()` and `remove()`. - Running one `select()` per parent in a loop over a page of parents, recreating N+1 by hand. ## When to choose it | | Ordinary collection plus loader options | `WriteOnlyMapped` | |---|---|---| | implicit IO risk | yes, whenever an option is missed | none | | reading many parents' children | `selectinload()` batches it | manual query per parent, or one hand-written query | | very large collections | loaded whole | queried in pages | | code at the call site | `order.items` | `await session.scalars(order.items.select())` | - **Choose it** for collections that are large, append-mostly, or always read with a filter: logs, history, events per order. - **Keep an ordinary collection** for small collections that are always shown with the parent, where `selectinload()` is simpler and batches across parents. The documentation states the trade-off directly: behaviour is simple and predictable, but there is no built-in way to load many of these collections at once, so that has to be done by hand.

  • How would you load the status logs for a page of 50 orders without 50 queries?
    Write-only collections have no batch loader, so query the child table once yourself: `select(StatusChange).where(StatusChange.order_id.in_(order_ids)).order_by(StatusChange.order_id, StatusChange.id)`, then group the rows by `order_id` in Python. If that page always needs the children, an ordinary relationship loaded with `selectinload()` may be the better mapping for it.
  • Why does assigning a new list to a persistent object's write-only collection fail?
    Replacing a collection means working out which old members to remove, which requires loading the current contents. A write-only collection never loads, so SQLAlchemy raises `InvalidRequestError` saying collection replacement operations can't be used. Use `remove()` and `add()`, or a `delete()` statement from the collection followed by `add_all()`.

  • Iterating a WriteOnlyMapped collection under AsyncSession raises MissingGreenlet.
  • order.status_log.add() immediately sends an INSERT, so it must be awaited.
  • selectinload() can batch-load write-only collections for many parents.
  • Write-only collections are the old dynamic loader renamed, with .all() and .filter().
  • await order.awaitable_attrs.status_log returns the list of rows.