Support for Async SQLAlchemy

[jrlopes2005]

Hi everyone,

Is there any perspective of support for async SQLAlchemy?

Im using AssyncSession.

Thanks

[awtkns]

Hi yes! There is support coming in the next couple days (#121)

[unidesigner]

@awtkns Would it be possible to merge this PR? I'd like to test if the async support for SQLAlchemy would already also work with SQLModel mentioned in #109. Thanks.

[awtkns]

Hi @unidesigner. I am still working on adding tests for Async SQLAlchemy.

In the meantime, if you would like to test it out, you can install the feature branch with:

pip install git+https://github.com/il-s/fastapi-crudrouter.git

[unidesigner]

Hi @awtkns - I tested the feature branch in the meantime.

In my particular setup (perhaps useful to others), I am running into the following exception: sqlalchemy.exc.InvalidRequestError: Can't operate on closed transaction inside context manager. Please complete the context manager before emitting further commands.

I think the problem is related to the function I set for the db field in SQLAlchemyCRUDRouter. I need to setup an execution_options for my session to set a schema_translate_map like so:

session.connection(execution_options={"schema_translate_map": {"per_user": project}})

in order for queries to run with different (Postgres) schemas. The way I setup the functions are like:

async def get_async_session() -> AsyncGenerator[AsyncSession, None]:
    async with async_session_maker() as session:
        yield session

async def get_query_project(project: str, session: AsyncSession = Depends(get_async_session)):
    async with session.begin():
        await session.connection(execution_options={
            "schema_translate_map": {"per_user": project}})
        yield session

and I use the SQLAlchemyCRUDRouter like so:

router = SQLAlchemyCRUDRouter(
    schema=MyModel,
    db_model=MyModel,
    create_schema=MyModelCreate,
    update_schema=MyModelUpdate,
    db=get_query_project,
    use_async=True
)

I think this is related to beginning the transaction with async with session.begin() already, e.g. see this SO question.

I guess my problem is how to properly yield the session in this context. Do you have any idea? Thanks.

[unidesigner]

I should add that creating a new object via the generated API already works, so the session already commits data to the proper schema/table.

[unidesigner]

FYI. I think I fixed by my issue by replacing

                await db.commit()
                await db.refresh(db_model)

with

                await db.flush()

here

[dsethlewis]

@awtkns At the risk of pestering you, can I ask when the new version with async support will be released? This package is exactly what I need for my API, but I need async support, and I want to work with a stable build. If it's going to be a while (e.g., months), I'll make do without and manually write my endpoints.

[caniko]

I can't wait for this to be implemented, just thought I'd leave my +1 here for the future. Thank you!

+1