DatabaseSessionService uses sync DB operations - needs async variant

[polaris340]

Is your feature request related to a problem? Please describe.
DatabaseSessionService uses async method signatures but internally calls SQLAlchemy’s sync APIs

Describe the solution you'd like
Add an AsyncDatabaseSessionService that uses SQLAlchemy’s async APIs (AsyncSession, etc.).

Describe alternatives you've considered
Fix existing DatabaseSessionService implementation to use async database operations.

Additional context
n/a

[mbroton]

I was trying to use DatabaseSessionService in an async way (passing db_url starting with postgresql+asyncpg) and there's more to that issue - I couldn't even initialize the class, because there's creation of sync engine, sync sessionmaker and tables creation (Base.metadata.create_all(self.db_engine)) which is causing:
exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place?

I was thinking about creating a PR with a quick fix, however I wasn't sure how to handle __init__ without introducing breaking changes to the API.

Additional notes:

• It would be nice to have more control over tables creation, e.g. I want to use alembic and keep tables creation in migration files.
• There's redundant line self.inspector = inspect(self.db_engine) because self.inspector is never used

[condorcet]

@DeanChensj Hello! do we have news here? In our project we use patched version of database service like this #1316 it works, but wondering, will it be solved by ADK team in next releases?

[leslyarun]

@DeanChensj When will this support be added? Please priortise it

[hangfei]

We are evaluating if we can merge #2889 or there is a better option.

[hangfei]

Also related: #1750

[DeanChensj]

This is now closed by #2889