DB closing issue

[mithun2003]

async def async_get_db() -> AsyncSession:
    async_session = local_session

    async with async_session() as db:
        yield db

db is yielding here but after the use where should it close? Now after each db operations I manually close db like db.close()

[igorbenav]

You don't need to manually close, when the session is yielded in an async context manager, it's automatically closed. From SQLAlchemy docs:

_In the example above, the AsyncSession is instantiated using the optional async_sessionmaker helper, which provides a factory for new AsyncSession objects with a fixed set of parameters, which here includes associating it with an AsyncEngine against particular database URL. It is then passed to other methods where it may be used in a Python asynchronous context manager (i.e. async with: statement) so that it is automatically closed at the end of the block; this is equivalent to calling the AsyncSession.close() method._

[Hari10007]

Have some doubts regarding this session close:

What if I am using a crud operation and fetch data from db then calling other function for other operations. So will the session be open until all the operation have been completed?.

[igorbenav]

Hey, @Hari10007, I believe the dependency is resolved once per request, so if you use multiple methods in a request, the connection will be closed only when the request is finished.

[Hari10007]

So there are two scenarios that I have doubts,

1. If I don't want our session to wait till all the operation to be completed(Because I am doing a CPU intensive task after the crud operation). Then we have to manually close this session just after the db operation's right.
2. If I manually close the db ,Then can I use this session for any other db crud operation's in that request.

NB: Just like opening the session only for the db operation in a function

[igorbenav]

Conceptually, if you close the session, you shouldn't be able to use it in the same request. You would need to open a new DB session for any subsequent DB operations within that request. I haven't actually tested this, though.

If you don't need the CPU-intensive task's result for the second DB operation, you might be better off just offloading the CPU-intensive task to a job queue. This way, you can keep both DB operations within the same request.

If the second DB operation requires the result of the CPU-intensive task, consider splitting the workflow into multiple requests. The first request would handle the initial DB operation and enqueue the CPU-intensive task. The second request, triggered after the task completes, would perform the second DB operation based on the task's result.

In any case, test different ways to approach this and see what works best for you.