Switching postgres schema from client side for SaaS projects

[tiangolo]

Switching postgres schema from client side for SaaS projects

[Chunkford]

When you create a model you can define the schema using __table_args__ = {'schema': 'name_of_schema_here'}
I'm guessing you could probably use a variable instead e.g. __table_args__ = {'schema': f'{schema_name}'} to dynamically
change the name of the schema on the fly.

[torugok]

I found a solution for this, using sqlalchemy schema translation, an example:

class MultiTenantTable(SQLModel):
    __table_args__ = {"schema": "tenant_schema"}

class MyTable(MultiTenantTable, table=True):
    id: int = Field(primary_key=True)

# execution
tenant_schema = "MyTenantSchema"
session.connection(execution_options={"schema_translate_map": {"tenant_schema": tenant_schema}})
session.execute(select(MyTable))