Migrate Database Interactions to Async Using asyncpg

[vineetshar]

Description:
To improve the performance and responsiveness of our APIs and services, we should transition from synchronous database interactions to asynchronous ones. By adopting asyncpg as our database interface, we align with the goal of providing truly asynchronous interfaces and runtime for all APIs and services.

Proposed Solution:

• Replace current synchronous database connections and queries with asynchronous implementations using asyncpg.
• Ensure all relevant service calls are updated to handle async database operations properly.

Tasks:

• Research asyncpg capabilities and compatibility with our existing database structure.
• Refactor database connection initialization to use asyncpg.
• Update all current synchronous queries to asynchronous with asyncpg.
• Test async implementations to verify expected performance improvements.
• Update documentation to reflect the new async architecture.

Acceptance Criteria:

• All database interactions are asynchronous, providing a non-blocking runtime.
• All API endpoints and services leverage async for better concurrency and performance.

[sarfarazsiddiquii]

Hi @vineetshar, can i work on this issue? can you please assign me?

[vineetshar]

Yes @sarfarazsiddiquii this still needs to be done, sure you can pick it up. Let us know the plan of action here before you start implementing.

[sarfarazsiddiquii]

Hey @vineetshar
sure! I'll replace the current synchronous SQLAlchemy queries with asynchronous queries using asyncpg in database.py. Then, I will update all the database interactions (mainly in the modules directory) to use this new async approach.

[sarfarazsiddiquii]

Hi @vineetshar is everything alright in the method I mentioned?
Should I start with the issue?

[vineetshar]

Hey @sarfarazsiddiquii yes you can proceed

[dhirenmathur]

Hey @sarfarazsiddiquii let us know if you need any help

[sarfarazsiddiquii]

Hey @dhirenmathur, I am a bit confused about the approach. I’ve updated database.py to use async operations.
However, many files use synchronous database operations. Do we need to manually update all of these to async, or is there a way to streamline the process?