Fief causes numerous idle sessions in PostgreSQL

[meteoDaniel]

Describe the bug

In my database, I can see numerous idle sessions that caused an outage. No more clients were allowed in my PostgreSQL

To Reproduce

Expected behavior

Sessions will be closed correctly

Configuration

• If self-hosted, Fief version: latest

Additional context

I recommend to use a contextmanager to handle database sessions:

@contextmanager
def get_scoped_session(database: str) -> Generator[Session, None, None]:
    """
    Returns a scoped sqlalchemy session for the specified database

    Args:
        database: database name

    Returns:
        scoped session object

    """
    session = sessionmaker(bind=create_engine(database))()

    try:
        yield session
        session.commit()
    except Exception:
        session.rollback()
        raise
    finally:
        session.close()

# Usage: 
with get_scoped_session(engine):
    # do stuff 

[frankie567]

We do use a context manager to manage session lifetime:

fief/fief/dependencies/db.py

Lines 9 to 19 in 9c7e72e

	async def get_main_async_session(
	request: Request,
	) -> AsyncGenerator[AsyncSession, None]:
	try:
	async with request.state.main_async_session_maker() as session:
	yield session
	except ConnectionRefusedError as e:
	raise HTTPException(
	status_code=status.HTTP_503_SERVICE_UNAVAILABLE,
	detail=APIErrorCode.SERVER_DATABASE_NOT_AVAILABLE,
	) from e

It's done implicitly by FastAPI, but it's there.

What's happening however is that SQLAlchemy maintains connection pools (i.e. idle opened connection ready to answer incoming queries). The default of SQLAlchemy is 5 opened connections at most. The thing is, they can pile up quite quickly if you have several server processes. We don't have a setting right now to tweak the number of connections, but it should probably be added.

Meanwhile, I suggest you to reduce the number of processes of the server: set the WEB_CONCURRENCY variable to 1 or 2.

[meteoDaniel]

Dear @frankie567 , thanks for the Support :)

I will adapt the config.

Best regards

[frankie567]

Tracking the connection pool setting addition here: #386