FastAPI Users route fails with relationships due to async SQLAlchemy

103 Views Asked by At

I started using the library FastAPI Users for FastAPI backend. The library includes some routes to register and fetch users but these don't work as soon as I put relationships on my User model.

I have a User which should have many Post's. If I define my UserRead Pydantic model/schema to include posts, FastAPI will try to load posts for the response. This fails since FastAPI Users uses async SQLAlchemy which cannot lazy load (no implicit IO). Specifically I am calling the /auth/register as when following the docs.

These are my SQLAlchemy models:

class Mixin(MappedAsDataclass, SQLAlchemyBaseUserTable[int]):
    pass

class User_DB(BaseModel_DB, Mixin):
    __tablename__ = "user_table"
    id: Mapped[int] = mapped_column(primary_key=True, init=False)  # type: ignore . It's fine
    firstname: Mapped[str] = mapped_column()
    posts: Mapped[list["Post_DB"]] = relationship(init=False)
class Post_DB(BaseModel_DB):
    __tablename__ = "post_table"

    id: Mapped[int] = mapped_column(primary_key=True, init=False)
    name: Mapped[str] = mapped_column()
    user_id: Mapped[int] = mapped_column(ForeignKey("user_table.id"))

and UserRead schema:

class UserRead(fastapi_users_schemas.BaseUser[int], BaseSchema):
    firstname: str
    email: str
    posts: list[PostRead]  # uncommenting this makes the route work

The following is raised upon calling POST /auth/register with data. This error is from an async SQLAlchemy load which then tries to lazily access an attribute which is not eagerly loaded. Thus excluding posts from UserRead, the framework won't try loading it.

Error extracting attribute: MissingGreenlet: greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place?

So one possibility might be to specify eager load for every relationship on User_DB but why wouldn't the docs of FastAPI Users say this if it was the only way to make relationships work? It also forces eager loading on every relationship attribute we would wanna return.

Q: How are relationships meant to be used with FastAPI Users?

The included routes don't even work with a simple related model. Reproduce with this minimal repo I made.

0

There are 0 best solutions below