awaitable_attrs missing in SQLModel

[MarkusDressel]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

copied from sql alchemy documentation
I did not have a working example for sqlmodel without sensitive data

from __future__ import annotations

import asyncio
import datetime
from typing import List

from sqlalchemy import ForeignKey
from sqlalchemy import func
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncAttrs
from sqlalchemy.ext.asyncio import async_sessionmaker
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship
from sqlalchemy.orm import selectinload


class Base(AsyncAttrs, DeclarativeBase):
    pass

class B(Base):
    __tablename__ = "b"
    id: Mapped[int] = mapped_column(primary_key=True)
    a_id: Mapped[int] = mapped_column(ForeignKey("a.id"))
    data: Mapped[str]

class A(Base):
    __tablename__ = "a"
    id: Mapped[int] = mapped_column(primary_key=True)
    data: Mapped[str]
    create_date: Mapped[datetime.datetime] = mapped_column(server_default=func.now())
    bs: Mapped[List[B]] = relationship()

async def insert_objects(async_session: async_sessionmaker[AsyncSession]) -> None:
    async with async_session() as session:
        async with session.begin():
            session.add_all(
                [
                    A(bs=[B(data="b1"), B(data="b2")], data="a1"),
                    A(bs=[], data="a2"),
                    A(bs=[B(data="b3"), B(data="b4")], data="a3"),
                ]
            )


async def select_and_update_objects(
    async_session: async_sessionmaker[AsyncSession],
) -> None:
    async with async_session() as session:
        stmt = select(A).order_by(A.id).options(selectinload(A.bs))
        result = await session.execute(stmt)
        for a in result.scalars():
            print(a, a.data)
            print(f"created at: {a.create_date}")
            for b in a.bs:
                print(b, b.data)
        result = await session.execute(select(A).order_by(A.id).limit(1))
        a1 = result.scalars().one()
        a1.data = "new data"
        await session.commit()
        # access attribute subsequent to commit; this is what
        # expire_on_commit=False allows
        print(a1.data)
        # alternatively, AsyncAttrs may be used to access any attribute
        # as an awaitable (new in 2.0.13)
        for b1 in await a1.awaitable_attrs.bs:
            print(b1, b1.data)


async def async_main() -> None:
    engine = create_async_engine("sqlite+aiosqlite://", echo=True)
    # async_sessionmaker: a factory for new AsyncSession objects.
    # expire_on_commit - don't expire objects after transaction commit
    async_session = async_sessionmaker(engine, expire_on_commit=False)
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)
    await insert_objects(async_session)
    await select_and_update_objects(async_session)
    # for AsyncEngine created in function scope, close and
    # clean-up pooled connections
    await engine.dispose()


asyncio.run(async_main())

Description

With sqlalchemy 2.0, adding the AsyncAttrs mixin in your model or Base class makes it easy to lazy-load collections async via the awaitable_attrs namespace

I could not find that namespace within SQLModel. What is the best practices to lazy load in sqlmodel

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.16

Python Version

3.11

Additional Context

No response

[s4v4g3]

As an FYI, I haven't been able to get the AsyncAttrs mixin to work either, but the lazy load options can be set to "selectin" using the sa_relationship_kwargs param as shown here:
https://github.com/tiangolo/sqlmodel/blob/main/tests/test_field_sa_relationship.py#L42
Agreed that it would be nice to have support for AsyncAttrs to avoid having to eager-load all the time.

(edited to add additional details)
I am able to add the AsyncAttrs mixin to a base class like so:

class BaseModel(AsyncAttrs, SQLModel):
   ...

And this does give the awaitable_attrs attribute on the instance, but when attempting to access a property through it, I'm getting the error referenced here: https://sqlalche.me/e/20/bhk3 (Parent instance not bound to a session). If I set the sa_relationship_kwargs as described above to enable the selectin loader, the awaitable_attrs attribute works just fine.

[s4v4g3]

I take it back....adding the AsyncAttrs mixin seems to work perfectly; I was making a silly mistake earlier.

[tom-cook-veea]

I'm seeing inconsistent behaviour around this. Doing this fails:

class ModelClass(SQLModel, table=True):
    id: int = Field(primary_key=True)
    children: list["Child"] = Relationship(sa_relationship_kwargs={"lazy": "selectin"})

class Child(SQLModel, table=True):
    id: int = Field(primary_key=True)
    parent_id: int = Field(foreign_key="modelclass.id")

my_model = ModelClass()
session.add(my_model)
await session.flush()
print(len(my_model.children))

But this works:

my_model = ModelClass()
my_model.children.append(Child())
session.add(my_model)
await session.flush()
print(len(my_model.children))