title: Async SqlAlchemy with FastAPI: Getting single session for all requests
body: <p>I have recently migrated a REST API coded with FastApi to the new SQLAlchemy 1.4+ Async Version.
  My app compiles correctly and the database seems to setup just fine. The problem appears when I try to execute multiple requests, an error appears that seems to indicate that the same session is being used for all my requests. I've put the Error message at the end</p>
  <p>Here's my code, I've based myself on the SQLAlchemy Async Docs and on <a href="https://github.com/uriyyo/fastapi-pagination/blob/main/examples/pagination_async_sqlalchemy.py" rel="noreferrer">this example</a></p>
  <p><strong>App Engine initialization</strong></p>
  <pre><code>from typing import AsyncIterator
  from sqlalchemy.ext.declarative import declarative_base
  
  Base = declarative_base()
  
  from .notification import Notification
  from .devices import Device
  
  
  
  from sqlalchemy import MetaData
  from sqlalchemy.orm import sessionmaker
  from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
  from sqlalchemy import create_engine
  
  from app.core.config import Config
  from app.core.constants import logger
  import asyncio
  
  engine = create_async_engine(Config.RDS_DB_URL)
  metadata = MetaData(engine)
  
  #if not engine.dialect.has_table(engine, C.NOTIFICATIONS):
  #Base.metadata.create_all(engine)
  
  async def init_connection():
      async with engine.begin() as conn:
          await conn.run_sync(Base.metadata.create_all)
      
  
  asyncio.run(init_connection())
  
  async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
  
  async def get_session() -&gt; AsyncIterator[AsyncSession]:
      async with async_session() as session:
          yield session
  
  
  
  
  </code></pre>
  <p><strong>Endpoints using the session depends loading</strong></p>
  <pre><code>@registry_router.get(R.REGISTRY_PRESCRIPTION_ID, tags=[TAGS.REGISTRY])
  async def get_patient_prescription_by_id(id: str, x_authorization: str = Header(None), 
                  session: AsyncSession = Depends(get_session)):
      #Other code
      return (await session.execute(select(Prescription).where(Prescription.id==id, 
                  Prescription.customerId==customerId))).scalars().first()
  </code></pre>
  <pre><code>
  Traceback (most recent call last):
    File &quot;/usr/local/lib/python3.7/dist-packages/hypercorn/asyncio/context.py&quot;, line 28, in _handle
      await invoke_asgi(app, scope, receive, send)
    File &quot;/usr/local/lib/python3.7/dist-packages/hypercorn/utils.py&quot;, line 219, in invoke_asgi
      await app(scope, receive, send)
    File &quot;/usr/local/lib/python3.7/dist-packages/fastapi/applications.py&quot;, line 208, in __call__
      await super().__call__(scope, receive, send)
    File &quot;/usr/local/lib/python3.7/dist-packages/starlette/applications.py&quot;, line 112, in __call__
      await self.middleware_stack(scope, receive, send)
    File &quot;/usr/local/lib/python3.7/dist-packages/starlette/middleware/errors.py&quot;, line 181, in __call__
      raise exc
    File &quot;/usr/local/lib/python3.7/dist-packages/starlette/middleware/errors.py&quot;, line 159, in __call__
      await self.app(scope, receive, _send)
    File &quot;/usr/local/lib/python3.7/dist-packages/starlette/middleware/cors.py&quot;, line 84, in __call__
      await self.app(scope, receive, send)
    File &quot;/usr/local/lib/python3.7/dist-packages/starlette/exceptions.py&quot;, line 82, in __call__
      raise exc
    File &quot;/usr/local/lib/python3.7/dist-packages/starlette/exceptions.py&quot;, line 71, in __call__
      await self.app(scope, receive, sender)
    File &quot;/usr/local/lib/python3.7/dist-packages/starlette/routing.py&quot;, line 656, in __call__
      await route.handle(scope, receive, send)
    File &quot;/usr/local/lib/python3.7/dist-packages/starlette/routing.py&quot;, line 259, in handle
      await self.app(scope, receive, send)
    File &quot;/usr/local/lib/python3.7/dist-packages/starlette/routing.py&quot;, line 61, in app
      response = await func(request)
    File &quot;/usr/local/lib/python3.7/dist-packages/fastapi/routing.py&quot;, line 227, in app
      dependant=dependant, values=values, is_coroutine=is_coroutine
    File &quot;/usr/local/lib/python3.7/dist-packages/fastapi/routing.py&quot;, line 159, in run_endpoint_function
      return await dependant.call(**values)
    File &quot;/home/ubuntu/rest/app/api/registry.py&quot;, line 504, in get_entity_schedules
      for row in (await data).items:
    File &quot;/usr/local/lib/python3.7/dist-packages/fastapi_pagination/ext/async_sqlalchemy.py&quot;, line 23, in paginate
      total = await session.scalar(select(func.count()).select_from(query.subquery()))  # type: ignore
    File &quot;/usr/local/lib/python3.7/dist-packages/sqlalchemy/ext/asyncio/session.py&quot;, line 230, in scalar
      **kw
    File &quot;/usr/local/lib/python3.7/dist-packages/sqlalchemy/ext/asyncio/session.py&quot;, line 206, in execute
      **kw
    File &quot;/usr/local/lib/python3.7/dist-packages/sqlalchemy/util/_concurrency_py3k.py&quot;, line 128, in greenlet_spawn
      result = context.throw(*sys.exc_info())
    File &quot;/usr/local/lib/python3.7/dist-packages/sqlalchemy/orm/session.py&quot;, line 1689, in execute
      result = conn._execute_20(statement, params or {}, execution_options)
    File &quot;/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py&quot;, line 1611, in _execute_20
      return meth(self, args_10style, kwargs_10style, execution_options)
    File &quot;/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py&quot;, line 324, in _execute_on_connection
      self, multiparams, params, execution_options
    File &quot;/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py&quot;, line 1488, in _execute_clauseelement
      cache_hit=cache_hit,
    File &quot;/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py&quot;, line 1843, in _execute_context
      e, statement, parameters, cursor, context
    File &quot;/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py&quot;, line 2024, in _handle_dbapi_exception
      sqlalchemy_exception, with_traceback=exc_info[2], from_=e
    File &quot;/usr/local/lib/python3.7/dist-packages/sqlalchemy/util/compat.py&quot;, line 207, in raise_
      raise exception
    File &quot;/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py&quot;, line 1800, in _execute_context
      cursor, statement, parameters, context
    File &quot;/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/default.py&quot;, line 717, in do_execute
      cursor.execute(statement, parameters)
    File &quot;/usr/local/lib/python3.7/dist-packages/sqlalchemy/dialects/postgresql/asyncpg.py&quot;, line 451, in execute
      self._prepare_and_execute(operation, parameters)
    File &quot;/usr/local/lib/python3.7/dist-packages/sqlalchemy/util/_concurrency_py3k.py&quot;, line 70, in await_only
      return current.driver.switch(awaitable)
    File &quot;/usr/local/lib/python3.7/dist-packages/sqlalchemy/util/_concurrency_py3k.py&quot;, line 123, in greenlet_spawn
      value = await result
    File &quot;/usr/local/lib/python3.7/dist-packages/sqlalchemy/dialects/postgresql/asyncpg.py&quot;, line 379, in _prepare_and_execute
      await adapt_connection._start_transaction()
    File &quot;/usr/local/lib/python3.7/dist-packages/sqlalchemy/dialects/postgresql/asyncpg.py&quot;, line 687, in _start_transaction
      self._handle_exception(error)
    File &quot;/usr/local/lib/python3.7/dist-packages/sqlalchemy/dialects/postgresql/asyncpg.py&quot;, line 653, in _handle_exception
      raise translated_error from error
  sqlalchemy.exc.InterfaceError: (sqlalchemy.dialects.postgresql.asyncpg.InterfaceError) &lt;class 'asyncpg.exceptions._base.InterfaceError'&gt;: cannot perform operation: another operation is in progress
  [SQL: SELECT count(*) AS count_1
  FROM (SELECT schedules.&quot;profileId&quot; AS &quot;profileId&quot;, schedules.&quot;customerId&quot; AS &quot;customerId&quot;, schedules.&quot;practitionerId&quot; AS &quot;practitionerId&quot;, schedules.practitioner AS practitioner, schedules.patient AS patient, schedules.id AS id, schedules.reason AS reason, schedules.comments AS comments, schedules.state AS state, schedules.duration AS duration, schedules.time AS time, schedules.ts AS ts, schedules.responses AS responses, schedules.&quot;attentionType&quot; AS &quot;attentionType&quot;, schedules.history AS history, tb_user.specialty AS specialty
  FROM schedules LEFT OUTER JOIN tb_user ON CAST(tb_user.id AS VARCHAR) = schedules.&quot;practitionerId&quot;
  WHERE (schedules.&quot;practitionerId&quot; = %s OR (EXISTS (SELECT 1
  FROM participants, tb_user
  WHERE schedules.id = participants.&quot;scheduleId&quot; AND tb_user.id = participants.&quot;participantId&quot; AND tb_user.id = %s))) AND schedules.&quot;customerId&quot; = %s AND schedules.ts BETWEEN %s AND %s ORDER BY schedules.ts DESC) AS anon_1]
  [parameters: ('c42a1400-4534-11eb-8918-eb0d5241f5a7', 'c42a1400-4534-11eb-8918-eb0d5241f5a7', '8f69db20-4533-11eb-8918-eb0d5241f5a7', 1632970800000, 1635735599000)]
  (Background on this error at: https://sqlalche.me/e/14/rvf5)
  
  </code></pre>
  <p>I've tried to get the session to run on each request in a variety of ways with no success. Any clues on what I'm missing?</p>