How to close sessions on Postgresql with sqlalchemy?

Question

In this configuration

from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine, async_sessionmaker

db_engine = create_async_engine('<DATABASE_URL>', echo=False, future=True)
async_session = async_sessionmaker(db_engine, class_=AsyncSession, expire_on_commit=False)


async def get_session() -> AsyncSession:
    async with async_session() as session:
        yield session
        await session.close()

I can connect to the Postgresql DB, but by some several connections stay open. Is that an issue and how can I fix it?

Answer 1

Since you are using a connection pool to get a session, SQLAlchemy will keep some connections open so that you can reuse them without waiting for a new connection to be established.

By default this number is 5, but you can change it by passing pool_size parameter to async_sessionmaker.

https://docs.sqlalchemy.org/en/20/core/pooling.html

BTW, you don't have to call session.close() since you are using context manager and it will do it for you when you exit from with