Properly starting async connection with SQLAlchemy Core in FastAPI (ASGI)

user_16942295 0
Viewed 617

I'm trying to build an async backend API service using FastAPI, Postgres, and SQLAlchemy Core.

When I was testing my API service without the frontend, it ran perfectly, but as soon as the frontend started to hit the backend many times concurrently, I started seeing the following error:

ERROR:    Exception in ASGI application
...
sqlalchemy.exc.ResourceClosedError: This Connection is closed

Here's my setup:

  1. service/utils/db.py
from fastapi import Request
from sqlalchemy.ext.asyncio import create_async_engine
from service.utils.config import DB_DSN
...
def get_async_engine(echo: bool = True, future: bool = Ture):
    global ENGINE

    if not ENGINE:
        ENGINE = create_async_engine(
            str(DB_DSN), echo=echo, future=future
        )

    return ENGINE

async def get_async_conn():
    engine = get_async_engine()

    async with engine.begine() as conn:
        Request.db_conn = conn
        yield
  1. service/api/main.py
from fastapi import Depends, FastAPI

from service.utils.db import get_async_conn

def get_app():
    app = FastAPI(
        title="app", dependencies=[Depends(get_async_conn)]
    )
    ...

    return app
  1. Example endpoint
from fastapi import APIRouter

router = APIRouter(...)

@router.get("")
async def get_items():
    query = await Request.db_conn.execute(...)  # some SQL query
    items = query.fetchall()

Any suggestion on how to properly manage async engine & connection with SQLAlchemy Core?

fastapi sqlalchemy postgresql python
1 Answers

I realized that I was assigning DB connection to a global location. This gets into a problem when there are more than 1 request hitting the API server, causing get_async_conn() to be called in a row before the first connection gets released, and the second request replaces the connection that was set up in the Request.db_conn. This was largely due to my stupidity + me trying to find a place that I could access to use the connection without passing the connection object from function to function. Nonetheless, I solved this problem by using encode/databases. Very easy integration, while it lacks details YET. Try this if you are looking for an easy DB connection/session/engine management.

user_169422950
Related