How do you establish a self-referencing foreign key in SQLAlchemy 2.0?

Question

Here is my model:

class MyModel(Base):
    __tablename__ = 'model'

    id          : Mapped[int]       = mapped_column(primary_key=True)
    given_id    : Mapped[str]       = mapped_column(String(50), unique=True, nullable=True)
    cancel_id   : Mapped[int]       = mapped_column(ForeignKey('model.given_id'), nullable=True)
    return_model: Mapped['MyModel'] = relationship(remote_side=[given_id])

With the structure like above when calling return_model I'm getting the following error:

sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place? (Background on this error at: https://sqlalche.me/e/20/xd2s)

But if I replace given_id with id then everything works fine and MyModel is able to reference itself:

class MyModel(Base):
    __tablename__ = 'model'

    id          : Mapped[int]       = mapped_column(primary_key=True)
    given_id    : Mapped[str]       = mapped_column(String(50), unique=True, nullable=True)
    cancel_id   : Mapped[int]       = mapped_column(ForeignKey('model.id'), nullable=True)
    return_model: Mapped['MyModel'] = relationship(remote_side=[id])

What is the right way of self-referencing in SQLAlchemy 2.0? How to achieve a one-to-one relationship? What am I doing wrong?

Note: I'm using async engine.

This looks like an error caused by unexpected IO in async code -does [this](https://stackoverflow.com/a/74256068/5320906) answer your question? — snakecharmerb, Aug 17 '23 at 13:57

score 1 · Accepted Answer · answered Aug 17 '23 at 15:36

Ok, so I've figured it out. When accessing related object/s the lazy loading will take place. According to the documentation:

in order to succeed will usually emit IO to the database, which will fail under asyncio as no implicit IO is allowed.

The suggested solution is for the Base model to inherit from AsyncAttrs:

class Base(AsyncAttrs, DeclarativeBase):
    pass

This mixin adds a single new attribute AsyncAttrs.awaitable_attrs to all classes, which will yield the value of any attribute as an awaitable. This allows attributes which may be subject to lazy loading or deferred / unexpiry loading to be accessed such that IO can still be emitted

and now I can access my related object:

result = await instance.awaitable_attrs.return_model

How do you establish a self-referencing foreign key in SQLAlchemy 2.0?

1 Answers1