WriteOnlyScalar companion to WriteOnlyMapped (which would be Collection?) , as WOM suits an async use case that applies to scalars as well

Describe the use case

Traditional strategies ('select', 'raise', 'subquery') for accessing a lazy-loaded relationship relationship don't work in an async context, as the database call loading the related instance yields to the event loop before a necessarily synchronous attribute access would otherwise return.

The naive solution of forcing asynchronous property access and typing these relationships as Mapped[Awaitable[T]] is also problematic, as even though reads of the attribute would be awaitable, attempts to mutate the value of the foreign key to the the related instance would require assigning an Awaitable and then awaiting this before setting the key value.

Another solution is to force all relationships to be eagerly loaded, but apart from the unnecessary load this would put on the database connection, it's not clear that it would even be possible when circular references are taken into account.

Current approaches

Multiple partial solutions are provided to deal with this issue, however all of them are limited in some way.

WriteOnlyCollection

Note: I actually didn't know about this until yesterday. It was similar to what I was thinking of as a possible solution, but I never put 2 + 2 together as being a solution to my problem

WriteOnlyCollection provides an "virtual collection" instance for the relationship value, which exposes a query via .select() which can be executed (asynchronously, using session.scalars()) in order to read the related objects. It allows updates to the instance via .add(), add_all() and .remove() methods.

This provides type-checking for the columns, but only works when many-to-one and many-to-many relationships, as it treats the result as a collection. It would be possible to treat the WriteOnlyCollection as a collection with a single item and read the result via session.scalar, the mutation methods (add, remove, add_all) are semantically unmeaningful for single value.

AsyncAttrs

This mixin class is intended to provide access to Awaitable values by mixing in an .awaitable_attrs property to the entity instance which proxies entity attributes to provide awaitable access to the proxied attribute by overriding __getattr__.

This approach is simple, straightforward and supports both single or collection-like relationships, but leaves unreadable attributes behind on the parent entity (accessing entity.x will produce an error if the value of x needs to be fetched from the db). In addition, the dynamic nature of awaitable_attrs means relationships accessed via the proxy without available static type information, impeding introspection.

Proposal

Adds a new class WriteOnlyRef[T] as a single-valued companion to WriteOnlyCollection by exposing the follwoing interface. The reference object would have an internal cache, so that repeated resolutions of the reference would return the same instance.

WriteOnlyRef.select() -> Select[T | None]

Similar to WriteOnlyCollection, exposes a query which selects the related object from the target of the relationship. The returned can be executed and the result accessed asynchronously via session.scalar, but the result would miss

WriteOnlyRef.get() -> Awaitable[T | None]

Gets the (possibly cached) value of the reference, loading it from the database if necessary. If the parent entity is not attached to a session, raise appropriately.

WriteOnlyRef.set(value: T | None) -> None

The given value will be bound to the parent instance and the database will be updated in the database when the parent instance is persisted.

Considerations

What should the behaviour of WriteOnlyMapped[T]? This type refers to WriteOnlyCollection, can this type be overriden to support both references and collections, or does a new orm mapping type be added? If not, how to indicate to typecheckers that the attribute exposes the WriteOnlyRef interface. Is it possible for WriteOnlyCollection and WriteOnlyRef to extend Mapped and to deprecate WriteOnlyMapped?

.get returns an Awaitable, which is uncommon in the sqlalchemy API. This utility type is intended specifically to support asynchronous drivers, but it is essentially unusable by synchronous drivers.

Is WriteOnly* a discoverable name? As mentioned in the note above, I didn't discover it until it was mentioned yesterday and I have been thinking of something like this for a long time now.

_note: I cannot engage in further discussion or implementation of this issue. I am proposing it as I believe it provides benefit to consumers the library who prefer asynchronous drivers (including potentially myself in future), but I realised a couple of days ago that I am not mentally stable enough to work alongside other people now or in the future. _

Databases / Backends / Drivers targeted

• asyncpg
• aiodbc
• aiomysql
• asyncmy
• aiosqlite
• oracledb

Example Use

class User(Base):
    __tablename__ = "user"
    id: Mapped[int] = mapped_)column(primary_key=True)
    address: WriteOnlyRef[Address] = relationship()

u = await select(User).where(User.id == 5)
address = await u.address.get()

u.address.set(Address('1 Railroad Pl., Lima Peru'))
session.add(u)
await session.flush()

Additional context

No response

Start by reviewing the existing WriteOnlyCollection and AsyncAttrs interfaces, then compare the proposed WriteOnlyRef operations with relationship and WriteOnlyMapped behavior. Done would require an agreed design for scalar reads, writes, caching, typing, and async-driver support; the issue does not identify implementation files or tests.