SQLAlchemy Async ORM: The MissingGreenlet error after commit

pre: This is not a concern for sqlalchemy core module user.

TLDR:

If you use the sqlalchemy async orm module and you try to access object attributes after committing a session, you will have to call await session.refresh(your_object) for each object you want to access. if you forget, you will run into the greenlet error. setting expire_on_commit=False is one way to avoid that.

the basics

You need to first create an engine that will manage the connection to the relational database of your choice, the next step is to create a highlever interface to the database called a session. you bind this session to the engine you created, and you use this session to interact with your database.

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine("sqlite:///mydatabase.db")
Session = sessionmaker(bind=engine)
session = Session()

The next step to this is creating your database tables which is done by creating a Base or Model class that will hold the metadata for your tables(name of this class is up to you, those used are the most commonly used), and then you create your tables as classes that inherit from it. after you create your tables, you can use the session to interact with your database. Let's extend above code example to include the creation of a user table.

...
class Model(DeclarativeBase):
    pass

# creating a user table

class User(Model):
    __tablename__ = "users"

    uid = Column(Integer, primary_key=True)
    name = Column(String)

Usually, you will need to go a little bit further and configure the metadata for you tables in this Model class by setting the naming_conventions for your tables metadata so you can control the names of the constraints and indexes that will be generated for your tables, but lets not go into that in this article.

async orm

That was for the most basic common sqlalchemy orm usage, but what if we want to use it async? what if we want to interact with our database in a non blocking way? well, sqlalchemy orm module has an async version that you can use to interact with your database in a non blocking way. Though, we will need to modify our code a bit, we will need an async database driver, Many database have them examples are asyncpg for postgres, aiomysql for mysql and aiosqlite for sqlite SQLAlchemy's ORM is internally synchronous, and greenlet is what lets it cooperate with asyncio. We will need to have it installed as well. You can install sqlalchemy with its greenlet dependency by running:

pip install sqlalchemy[asyncio]

Let's modify our code example to use create_async_engine, and async_sessionmaker.

from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

engine = create_async_engine("sqlite+aiosqlite:///mydatabase.db")
Session = async_sessionmaker(bind=engine)  # expire_on_commit=True by default

session = Session()

class Model(DeclarativeBase):
    pass


class User(Model):
    __tablename__ = "users"

    uid: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]


async def add_user(name: str) -> User:
    user = User(name=name)
    session.add(user)
    await session.commit()
    return user


async def create_tables():
    async with engine.begin() as conn:
        await conn.run_sync(Model.metadata.create_all)

async def main():
    await create_tables()
    user = await add_user(name="Foo")
    print(user.uid)


if __name__ == "__main__":
    asyncio.run(main())

Running the code:

...
raise exc.MissingGreenlet(
    ...<2 lines>...
    )
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)

In our code above we tried to add a user and commit it to the database and return the user object, right after we commit, the session expires all the data in it by default. but, we try to access the uid of the user object that we just added and since the session has expired, it will try to refresh the data by querying the database again, and this refresh attempt happens outside the greenlet context (the async context ended at commit), it raises the greenlet error. To fix this, we can set the expire_on_commit parameter to False when we create our session, this will prevent the session from expiring the data after we commit it, and we will be able to access the uid of the user object without any issues.

Session = async_sessionmaker(bind=engine, expire_on_commit=False)

outuput of the code above will be:

1

We could have just called await session.refresh(user) right after we committed the session to refresh the data in the session, but we will have to do that for every object we want to access its attributes, I think that is very easy to forget to do compared to setting expire_on_commit to False.