Synchronous Session support.

[fotinakis]

This PR implements support for SQLAlchemy synchronous Session, alongside existing support for AsyncSession.

Based on @frankie567's answer in this discussion on "Synchronous SQLAlchemy?": https://github.com/fastapi-users/fastapi-users/discussions/1144#discussioncomment-4653607

• Copied SQLAlchemyUserDatabase --> SQLAlchemySynchronousUserDatabase and then:
  • Accept a sqlalchemy Session arg instead of an AsyncSession
  • Remove the await keyword when working with this session (e.g. await self.session.commit() becomes self.session.commit())
  • The queries didn't have to change
• Copied SQLAlchemyAccessTokenDatabase --> SQLAlchemySynchronousAccessTokenDatabase and applied the same changes

Tested:

• Added a SYNC_DATABASE_URL that uses a non-async sqlite adapter
• Used pytest parameterized fixtures to run the same tests with the sync & async engines
• Also tested similar setup in a real-world app

Followup:

• If this is accepted, I'd be happy to update documentation to reference these new object types

[fotinakis]

@frankie567 friendly ping on this — I believe this is feature complete & fully tested in the existing style.

[fotinakis]

@frankie567 friendly ping here, don't want this to get stale

[BredoGen]

It'll be great if sync SQLAlchemy adapter will be supported out-of-box.

Are there any plans to merge this?

[jerivas]

For anyone who is using sync sessions and doesn't want to maintain mirror classes, here's a hack to make all methods on Session awaitable based on AsyncSession:

import inspect
from typing import Annotated, Any, Generic

from fastapi_users.authentication.strategy.db import AP
from fastapi_users.models import ID, UP
from fastapi_users_db_sqlalchemy import (
    SQLAlchemyBaseOAuthAccountTable,
    SQLAlchemyUserDatabase,
)
from fastapi_users_db_sqlalchemy.access_token import SQLAlchemyAccessTokenDatabase
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import Session


class FakeAsyncSession:
    def __init__(self, session: Session):
        self.session = session

    def __getattr__(self, name: str) -> Any:
        """
        If the method being called is async in AsyncSession, create a fake async version
        for Session so callers can `await` as usual. Think `commit`, `refresh`,
        `delete`, etc.
        """
        async_session_attr = getattr(AsyncSession, name, None)
        session_attr = getattr(self.session, name)
        if not inspect.iscoroutinefunction(async_session_attr):
            return session_attr

        async def async_wrapper(*args, **kwargs):
            return session_attr(*args, **kwargs)

        return async_wrapper


class UserDatabase(Generic[UP, ID], SQLAlchemyUserDatabase[UP, ID]):
    session: Session

    def __init__(
        self,
        session: AsyncSession,
        user_table: type[UP],
        oauth_account_table: type[SQLAlchemyBaseOAuthAccountTable] | None = None,
    ):
        super().__init__(session, user_table, oauth_account_table)
        self.session = FakeAsyncSession(session)


class AccessTokenDatabase(Generic[AP], SQLAlchemyAccessTokenDatabase[AP]):
   session: Session

    def __init__(self, session: AsyncSession, access_token_table: type[AP]):
        super().__init__(session, access_token_table)
        self.session = FakeAsyncSession(session)

Then both UserDatabase and AccessTokenDatabase can be used in place of the classes provided by this library.