Fallback for Postgresql DB in AsyncSession

[junoriosity]

First Check

• [X] I added a very descriptive title to this issue.
• [X] I used the GitHub search to find a similar issue and didn't find it.
• [X] I searched the SQLModel documentation, with the integrated search.
• [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
• [X] I already read and followed all the tutorial in the docs and didn't find an answer.
• [X] I already checked if it is not related to SQLModel but to Pydantic.
• [X] I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• [X] I commit to help with one of those options 👆

Example Code

from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import sessionmaker

primary_engine = create_async_engine("PRIMARY_DATABASE_URL", echo=False, future=True)
replica_engine = create_async_engine("REPLICA_DATABASE_URL", echo=False, future=True)


async def get_primary_session() -> AsyncSession:
    async_session = sessionmaker(primary_engine, class_=AsyncSession, expire_on_commit=False)
    async with async_session() as session:
        yield session

async def get_replica_session() -> AsyncSession:
    async_session = sessionmaker(replica_engine, class_=AsyncSession, expire_on_commit=False)
    async with async_session() as session:
        yield session

Description

I am currently having two Postgresql Clusters attached to my Python FastAPI server (see the source code).

Now assume, that the replica crashes. What would be the best strategy to redirect the traffic back to the primary?

I currently have tried this:

async def get_replica_session() -> AsyncSession:
    try:
        async_session = sessionmaker(replica_engine, class_=AsyncSession, expire_on_commit=False)
        async with async_session() as session:
            yield session
    except:
        async_session = sessionmaker(primary_engine, class_=AsyncSession, expire_on_commit=False)
        async with async_session() as session:
            yield session

However, it used to throw Runtime errors? Hence, it would be great if you could give me some thoughts about it.

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

3.10

Additional Context

No response

[antont]

This is not about SQLModel actually but SQLAlchemy, your example also literally only imports sqlalchemy libs. Maybe you'll have better luck asking there or in StackOverflow with sqlalchemy tag.

Am also interested in the answer though :)

[sparklog]

First Check

• [x] I added a very descriptive title to this issue.
• [x] I used the GitHub search to find a similar issue and didn't find it.
• [x] I searched the SQLModel documentation, with the integrated search.
• [x] I already searched in Google "How to X in SQLModel" and didn't find any information.
• [x] I already read and followed all the tutorial in the docs and didn't find an answer.
• [x] I already checked if it is not related to SQLModel but to Pydantic.
• [x] I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• [x] I commit to help with one of those options 👆

Example Code

from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import sessionmaker

primary_engine = create_async_engine("PRIMARY_DATABASE_URL", echo=False, future=True)
replica_engine = create_async_engine("REPLICA_DATABASE_URL", echo=False, future=True)


async def get_primary_session() -> AsyncSession:
    async_session = sessionmaker(primary_engine, class_=AsyncSession, expire_on_commit=False)
    async with async_session() as session:
        yield session

async def get_replica_session() -> AsyncSession:
    async_session = sessionmaker(replica_engine, class_=AsyncSession, expire_on_commit=False)
    async with async_session() as session:
        yield session

Description

I am currently having two Postgresql Clusters attached to my Python FastAPI server (see the source code).

Now assume, that the replica crashes. What would be the best strategy to redirect the traffic back to the primary?

I currently have tried this:

async def get_replica_session() -> AsyncSession:
    try:
        async_session = sessionmaker(replica_engine, class_=AsyncSession, expire_on_commit=False)
        async with async_session() as session:
            yield session
    except:
        async_session = sessionmaker(primary_engine, class_=AsyncSession, expire_on_commit=False)
        async with async_session() as session:
            yield session

However, it used to throw Runtime errors? Hence, it would be great if you could give me some thoughts about it.

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

3.10

Additional Context

No response

I have tried to use asyncSession which import from sqlalchemy, and then the problem solved. I hope it is useful