skip to content

In SQLAlchemy 2.1's asyncio extension, what does greenlet do, and what do AsyncConnection.run_sync() and AsyncSession.run_sync() expose of it?

level: middleimportance: should knowfreq 35%

answer

  1. a proxy, not a rewrite
  2. sync code paused at the driver
  3. an extra install target since 2.1
  4. a sync Connection or Session as first argument

basics

~20 s

AsyncSession and AsyncConnection proxy the synchronous Session and Connection; greenlet lets that sync code run on the event loop and pause wherever the driver awaits. run_sync() gives your own sync function the same treatment, as with MetaData.create_all.

solid answer

~40 s

The asyncio extension is a thin proxy: `AsyncSession` wraps a real `Session`, and `AsyncConnection` wraps a `Connection`. Each awaitable method calls the synchronous code through `greenlet_spawn()`, which runs it in a greenlet; when the driver adapter reaches real IO it calls `await_()`, which switches back to the coroutine, awaits the driver there, and switches the result back in. That needs the greenlet package, which SQLAlchemy 2.1 installs only through the `sqlalchemy[asyncio]` extra. `run_sync(fn, *args)` exposes the same bridge: it passes `fn` a synchronous `Connection` (from `AsyncConnection`) or `Session` (from `AsyncSession`), so sync-only APIs such as `MetaData.create_all` or `inspect()` work: `await conn.run_sync(Base.metadata.create_all)`. The function runs inline on the event loop, so blocking IO outside SQLAlchemy stalls the loop.

code

python · 32 lines
python
import asyncio

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


class Base(DeclarativeBase):
    pass


class Event(Base):
    __tablename__ = "event"
    id: Mapped[int] = mapped_column(primary_key=True)
    source: Mapped[str]


def table_names(conn: Connection) -> list[str]:
    # plain synchronous code: inspect() has no async version
    return inspect(conn).get_table_names()


async def main() -> None:
    engine = create_async_engine("sqlite+aiosqlite:///events.db")
    async with engine.begin() as conn:
        # create_all() expects a sync Connection; run_sync() supplies one
        await conn.run_sync(Base.metadata.create_all)
        print(await conn.run_sync(table_names))
    await engine.dispose()


asyncio.run(main())

go deeper

for a junior

Remember two facts: SQLAlchemy 2.1 needs pip install sqlalchemy[asyncio] for async use, and create_all runs through await conn.run_sync(...).

for a middle

Explain the proxy design and how greenlet_spawn and await_ hand control between sync code and the event loop, and what run_sync passes to your function.

for a senior

Diagnose the import error, the MissingGreenlet from calling a sync engine in a coroutine, and a run_sync block that stalls the loop with outside IO.

for a principal

Decide where run_sync is allowed in the codebase, for DDL, reflection or a porting phase, and how the greenlet dependency is pinned and built for every target platform.

## A proxy, not a second ORM SQLAlchemy did not write an asynchronous copy of its ORM. `AsyncSession` is documented as a **proxy** for a traditional `Session`, and `AsyncEngine` and `AsyncConnection` proxy an `Engine` and a `Connection`. Flushes, identity-map bookkeeping, SQL compilation and result processing are the same synchronous code that a non-async application runs. What changes is how that code waits for the database. The proxied objects stay reachable, which matters whenever you need the synchronous API directly: - `AsyncEngine.sync_engine` is the `Engine` underneath; - `AsyncConnection.sync_connection` is the `Connection` in use; - `AsyncSession.sync_session` is the `Session` that does the ORM work. These attributes are where event listeners are registered. Calling their IO methods directly from a coroutine, outside the bridge described next, does not work. ## How greenlet_spawn() and await_() cooperate A **greenlet** is a lightweight execution context that can pause and hand control to another greenlet without threads. SQLAlchemy uses two internal helpers: 1. An awaitable method such as `AsyncSession.execute()` calls `greenlet_spawn(fn, ...)`, which starts the synchronous `Session.execute()` inside a new greenlet. 2. The sync code runs until the driver adapter needs real IO. The adapter calls `await_(coroutine)`, which **switches** to the parent greenlet with the driver's coroutine. 3. The parent, still running as an ordinary coroutine on the event loop, `await`s that coroutine, so other tasks run while the database answers. 4. When the result arrives, the parent switches back into the greenlet and the sync code continues as though the call had blocked. 5. When the sync function returns, `greenlet_spawn()` returns its value to your `await`. If synchronous code tries to reach the driver **outside** a `greenlet_spawn()` context, `await_()` has no parent to switch to and raises `MissingGreenlet: greenlet_spawn has not been called; can't call await_() here. Was IO attempted in an unexpected place?` ## Installing it in SQLAlchemy 2.1 SQLAlchemy 1.4 and 2.0 installed greenlet automatically on platforms with a pre-built wheel. **2.1 keeps greenlet entirely inside the `asyncio` extra**, so plain `pip install sqlalchemy` no longer brings it. Without it, importing `sqlalchemy.ext.asyncio` fails at once: - the error is an `ImportError` whose message names the fix, `pip install sqlalchemy[asyncio]`; - it fires at import time, before any engine is created; - where greenlet has no pre-built wheel, it is built from source, which needs Python's development libraries. ## run_sync(): the bridge made public `run_sync(fn, *args, **kwargs)` runs **your** synchronous function inside the same instrumented greenlet and returns whatever it returns. | Method | First argument passed to `fn` | Typical use | |---|---|---| | `AsyncConnection.run_sync()` | a sync `Connection` | `MetaData.create_all`, `drop_all`, `inspect(conn)` | | `AsyncSession.run_sync()` | a sync `Session` | existing sync ORM code, legacy `session.query()` | Typical uses: - **DDL helpers**: `await conn.run_sync(Base.metadata.create_all)`. `create_all()` wants a sync connection and has no awaitable form. - **Reflection**: there is no asyncio `Inspector`, so `await conn.run_sync(lambda c: inspect(c).get_table_names())`. - **Porting**: a block of sync ORM code, attribute loads included, can run unchanged under `AsyncSession.run_sync()` while the rest of the program moves to `await`. ## What fails without the bridge - `Base.metadata.create_all(async_engine)` raises `AttributeError: 'AsyncEngine' object has no attribute '_run_ddl_visitor'`, because `create_all()` expects a synchronous engine or connection. - `Base.metadata.create_all(async_engine.sync_engine)` from a coroutine raises `MissingGreenlet`: the sync engine reaches the async driver with no greenlet to switch through. - `run_sync()` is the supported route for both. - The reverse mistake exists too: an engine whose driver never awaits makes `greenlet_spawn()` raise `AwaitRequired` ("The current operation required an async execution but none was detected"), which usually means the dialect is not an asyncio one. ## Reading the example The first code example opens one `AsyncConnection` with `engine.begin()` and calls `run_sync()` twice. `create_all` receives the proxied `Connection`, emits its `CREATE TABLE` inside the transaction that `begin()` commits, and returns nothing. The second call runs a small helper that builds an `Inspector` with `inspect(conn)` and returns the table names; `run_sync()` hands that list back to the coroutine as the awaited value. ## Limits worth stating - `run_sync()` runs `fn` **inline on the event loop**; there is no thread executor. SQLAlchemy calls are adapted, but a blocking HTTP call or file read inside `fn` stalls every other task. - The documentation calls the pattern "controversial", because it lets IO happen on lines without `await`. Keep run_sync blocks small and deliberate. - Events are not exposed as async: listeners stay synchronous functions registered on `AsyncEngine.sync_engine` or `AsyncSession.sync_session`, and they run inside the same greenlet.

  • Why does Base.metadata.create_all(async_engine) fail, and what is the fix?
    `create_all()` expects a synchronous `Engine` or `Connection`. Given an `AsyncEngine` it fails with `AttributeError: 'AsyncEngine' object has no attribute '_run_ddl_visitor'`, and given `async_engine.sync_engine` from a coroutine it raises `MissingGreenlet` because no greenlet bridge is running. Open a connection and run it through the bridge: `async with engine.begin() as conn: await conn.run_sync(Base.metadata.create_all)`.
  • How do you attach an engine event listener when the application uses an AsyncEngine?
    The asyncio extension has no async events. `event.listen(async_engine, ...)` raises `NotImplementedError` ("asynchronous events are not implemented at this time"), so register an ordinary synchronous listener on `async_engine.sync_engine`, or on `AsyncSession.sync_session` for session events. The listener runs inside the greenlet, in the middle of the synchronous execution, so a blocking call there stalls the event loop.

  • Says the asyncio extension is a separate ORM written as native coroutines
  • Assumes pip install sqlalchemy is enough for create_async_engine in 2.1
  • Believes run_sync() moves the function to a thread pool
  • Calls a blocking HTTP client inside run_sync() and expects the loop to stay free
  • Passes the AsyncEngine straight to MetaData.create_all()

Assumes SQLAlchemy 2.1; until 2.0, greenlet was installed with SQLAlchemy by default, while 2.1 installs it only through the sqlalchemy[asyncio] extra.