How to implement global database connection pool with databases wrapper and set the schema at run time in a schema based multi tenant app

[cs-satish-mishra]

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) # how to set the schema dynamically here or make db connection schema aware 
    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.