How can I implement a safe and efficient global database connection pool in a multi-tenant application using the PostgreSQL Schema-based approach, FastAPI, and asyncpg (databases wrapper)? Specifically, I need to dynamically set the appropriate schema in connection or query based on the API subdomain or headers received from the request at runtime. How can I achieve this?
from databases import Database
from sqlmodel import select
# Global databases pool
database = Database(DATABASE_URI, min_size=5, max_size=20)
async def list(db: Database):
# User is SQLModel
query = select(User)
return await database.fetch_all(query)
In the above sample code I want to safely set the intended schema at the time of query but utilizing same global connection pool.
I am aware of sqlalchemy schema translation map but do not know how I can achieve similar with databases wrapper and connection pool.
