skip to content

Async Engines & Sessions

create_async_engine and async_sessionmaker give awaitable sessions over drivers such as asyncpg. Interviewers ask why one AsyncSession must not be shared across concurrent tasks.

on this pageshow

explore

questions

5

In SQLAlchemy 2.1, how do create_async_engine, async_sessionmaker and AsyncSession fit together to run one unit of work?

level: middleimportance: must knowfreq 50%

answer

  1. one engine, many short sessions
  2. a factory that fixes session settings
  3. async with for scope and transaction
  4. an async driver in the URL
  5. await the pool's disposal

basics

~20 s

create_async_engine builds one AsyncEngine and its pool per process; async_sessionmaker is a factory with fixed session settings; each unit of work opens its own AsyncSession with async with, wraps writes in session.begin(), and shutdown awaits engine.dispose().

solid answer

~40 s

`create_async_engine("postgresql+asyncpg://...")` returns an `AsyncEngine` that owns an `AsyncAdaptedQueuePool`; the URL must name an asyncio driver, and in SQLAlchemy 2.1 the `sqlalchemy[asyncio]` extra must be installed for greenlet. Create the engine once per process, then `async_sessionmaker(engine, expire_on_commit=False)` as the factory that fixes every session's settings. Each unit of work opens a fresh session with `async with SessionFactory() as session:` and wraps its writes in `async with session.begin():`, which commits on success and rolls back if the block raises; leaving the outer block closes the session and returns its connection to the pool. Every call that may touch the database is awaited: `await session.execute(...)`, `await session.scalars(...)`, `await session.commit()`. At shutdown, `await engine.dispose()` closes the pooled connections inside the event loop, because SQLAlchemy cannot await anything from garbage collection.

code

python · 40 lines
python
import asyncio

from sqlalchemy import String
from sqlalchemy.ext.asyncio import async_sessionmaker, 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] = mapped_column(String(50))
    payload: Mapped[str]


# production: create_async_engine("postgresql+asyncpg://ingest:***@db/events")
engine = create_async_engine("sqlite+aiosqlite:///events.db")
SessionFactory = async_sessionmaker(engine, expire_on_commit=False)


async def write_event(source: str, payload: str) -> int:
    async with SessionFactory() as session, session.begin():
        event = Event(source=source, payload=payload)
        session.add(event)
    # committed on leaving begin(), session closed on leaving the outer block
    return event.id


async def main() -> None:
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.drop_all)
        await conn.run_sync(Base.metadata.create_all)
    print(await write_event("sensor-7", '{"t": 21.5}'))
    await engine.dispose()


asyncio.run(main())

go deeper

for a junior

Recall the three names and their order: create_async_engine once, async_sessionmaker once, then a fresh AsyncSession inside async with for each piece of work.

for a middle

Explain what each object owns, why expire_on_commit=False is the usual factory setting, how session.begin() commits or rolls back, and why the URL needs an async driver.

for a senior

Show the lifecycle a production service needs: an engine per process and per loop, dispose() on shutdown, and what the RuntimeError at garbage collection tells you.

for a principal

Set the team's construction rule: where the engine and factory live, which session settings are fixed in the factory, and how tests reconfigure the bind.

## Three objects, three lifetimes SQLAlchemy's asyncio extension mirrors the synchronous API one object at a time. An **engine** is the long-lived source of database connections, a **session factory** stamps out sessions with the same settings, and an **AsyncSession** is one short unit of work: one transaction, one set of loaded objects. | Object | Built by | Owns | Typical lifetime | |---|---|---|---| | `AsyncEngine` | `create_async_engine(url, **kw)` | the dialect, the async driver and an `AsyncAdaptedQueuePool` | the whole process | | `async_sessionmaker` | `async_sessionmaker(engine, **session_kw)` | default arguments for every new session | the whole process | | `AsyncSession` | calling the factory: `SessionFactory()` | one transaction, its connection, an identity map of loaded objects | one task's unit of work | ## Building the engine - The URL names an **asyncio driver**, such as `postgresql+asyncpg://` or `sqlite+aiosqlite://`. A synchronous one fails at once with `InvalidRequestError: The asyncio extension requires an async driver to be used. The loaded 'pysqlite' is not async.` - Its keyword arguments are mostly those of `create_engine()`: `echo`, `pool_size`, `max_overflow`, `pool_pre_ping` and the rest. Sizing them is a pool question, not an asyncio one. - The default pool is **`AsyncAdaptedQueuePool`**, a `QueuePool` that uses an asyncio-compatible queue instead of `threading.Lock`; the plain `QueuePool` is not compatible with `create_async_engine`. - Since **SQLAlchemy 2.1**, greenlet is not installed with SQLAlchemy. Install `sqlalchemy[asyncio]`, or importing `sqlalchemy.ext.asyncio` raises an `ImportError` naming that extra. ## The session factory `async_sessionmaker` (added in 2.0) holds the arguments every session should share. Its defaults match `sessionmaker`: `autoflush=True` and `expire_on_commit=True`. Most asyncio code passes **`expire_on_commit=False`**, so that objects stay readable after a commit without an attribute access trying to reload them; why that reload fails under asyncio is the subject of the implicit IO material. The factory offers two ways in: - `SessionFactory()` returns a new `AsyncSession`; you start the transaction yourself. - `SessionFactory.begin()` returns a context manager that creates the session **and** begins a transaction; leaving it commits (or rolls back on an exception) and closes the session. `async_sessionmaker.configure(bind=...)` changes the defaults later, for example in a test. ## One unit of work, step by step 1. Open the session: `async with SessionFactory() as session:`. Creating it does no IO; a connection is checked out of the pool only when the first statement runs. 2. Begin the transaction: `async with session.begin():`. The two can share one line: `async with SessionFactory() as session, session.begin():`. 3. Stage and run work: `session.add(obj)` is synchronous, while `await session.execute(stmt)`, `await session.scalars(stmt)`, `await session.get(Event, 7)` and `await session.flush()` are awaited. 4. Leave `begin()`: it commits, or rolls back and re-raises if the block raised. 5. Leave the outer block: the session closes and its connection goes back to the pool. ```python async with SessionFactory() as session, session.begin(): session.add(Event(source="sensor-7", payload="{}")) # committed, then closed ``` Without `begin()`, the session **autobegins** on the first statement and you finish with `await session.commit()`; closing it without a commit rolls the work back. ## Reading results inside the session The awaitable methods return the same objects the synchronous API does, already fully fetched: - `result = await session.execute(select(Event))` gives a buffered `Result`, so `result.scalars().all()` afterwards needs no `await`. - `await session.scalars(stmt)` and `await session.scalar(stmt)` are shortcuts for entity lists and single values. - `await session.get(Event, 7)` looks in the identity map first and queries only when the object is not already loaded. - Large reads that must not be fully buffered use `stream()` or `stream_scalars()` instead, which return asynchronous result objects. ## Shutdown and event loops - `await engine.dispose()` closes every **checked-in** pooled connection. Connections still checked out are detached from the engine and close when they are returned. - Skipping it lets the pool be collected after the loop has stopped. SQLAlchemy cannot `await` inside `__del__` or a weakref finalizer, so the documentation warns of messages such as `RuntimeError: Event loop is closed` at garbage collection. - An `AsyncEngine` should not be shared between event loops with the default pool. Call `await engine.dispose()` before reusing it on a new loop, or create it with `poolclass=NullPool` so no connection is reused. ## What to remember - One engine and one factory per process; one session per unit of work and per task. - Await everything that can reach the database; `add()` and `add_all()` are the synchronous exceptions. - Fix shared settings such as `expire_on_commit` in the factory, not at each call site. - Dispose of the engine inside the loop that used it.

  • What does async_sessionmaker.begin() give you that calling the factory does not?
    `async with SessionFactory.begin() as session:` creates a new `AsyncSession` and starts its transaction in one step. Leaving the block commits, or rolls back if it raised, and then closes the session. Calling `SessionFactory()` only creates the session, so you either open `async with session.begin():` yourself or let it autobegin and finish with `await session.commit()`.
  • Why can reusing one AsyncEngine on a second event loop fail, and what are the options?
    The default `AsyncAdaptedQueuePool` hands back connections that were opened under the first loop, which can fail with a `RuntimeError` such as "got Future attached to a different loop". SQLAlchemy's documentation gives two options: `await engine.dispose()` before the engine is used on the new loop, or create it with `poolclass=NullPool` so that no connection is ever reused.
  • What happens if you pass sqlite:///app.db to create_async_engine?
    It raises `InvalidRequestError`: "The asyncio extension requires an async driver to be used. The loaded 'pysqlite' is not async." The URL has to name an asyncio driver, such as `sqlite+aiosqlite:///app.db` or `postgresql+asyncpg://...`; the new `AsyncEngine` checks the dialect's `is_async` flag.

  • Creates a new AsyncEngine for every request or task
  • Believes async_sessionmaker opens a database connection when it is created
  • Expects the pool to close itself cleanly when the AsyncEngine is garbage collected
  • Calls session.execute() on an AsyncSession without await and expects rows
  • Thinks any database URL works with create_async_engine, whatever its driver

Assumes SQLAlchemy 2.1: greenlet comes only with the sqlalchemy[asyncio] extra since 2.1, and async_sessionmaker exists since 2.0.

open as a page

An async SQLAlchemy ingestion worker shares one AsyncSession across tasks started with asyncio.gather and fails with InvalidRequestError; why, and how should it be structured?

level: seniorimportance: must knowfreq 45%

basics

~20 s

An AsyncSession is one unsynchronised transaction on one connection, so gathered tasks race on its state and SQLAlchemy can raise errors such as "This session is provisioning a new connection; concurrent operations are not permitted". Give each task its own AsyncSession from one async_sessionmaker.

open as a page

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%

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.

open as a page

An async SQLAlchemy export job loads a huge events table with AsyncSession.scalars() and runs out of memory; why, and how do stream() and stream_scalars() fix it?

level: seniorimportance: should knowfreq 25%

basics

~20 s

AsyncSession.scalars() and execute() return buffered results: every row is fetched and every object built before the await returns. stream() and stream_scalars() use a server-side cursor and return an async result you iterate in batches set by yield_per.

open as a page

When a team standardises its Python data layer on SQLAlchemy 2.1, how do you choose between the asyncio extension and synchronous Sessions run in threads?

level: principalimportance: should knowfreq 30%

basics

~20 s

Pick the asyncio extension when the program is already asyncio and waits on many concurrent queries; keep synchronous Sessions in threads when lazy loading, sync-only libraries or simplicity matter more. AsyncSession runs the same ORM underneath, so it buys concurrency, not faster SQL.

open as a page