How to implement Multi tenancy in FastAPI, SQLAlchemy and Postgresql?

[abdulla-mindfire]

First Check

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

Commit to Help

• I commit to help with one of those options 👆

Example Code

from sqlalchemy import Boolean, Column, ForeignKey, Integer, String


class Tenant(Base):
    __tablename__ = "tenants"

    id = Column("id", Integer, primary_key=True, nullable=False)
    name = Column("name", String(256), nullable=False, index=True, unique=True)
    schema = Column("schema", String(256), nullable=False, unique=True)
    host = Column("host", String(256), nullable=False, unique=True)
    

    __table_args__ = ({"schema": "shared"},)

Description

Found an Article but unable to understand how to implement dynamic schema generation when new client sign up on the portal.

so that any client access like

client1.mysite.com
client2.mysite.com

And schema generation should be on the fly if a new client signup

Operating System

Linux

Operating System Details

No response

FastAPI Version

0.88.0

Python Version

3.10.9

Additional Context

No response

[yinziyan1206]

You should create or change schema by using dependency injection in FastAPI. (in Step 3, 4, 5)
And I suggest tenant column

[iudeen]

Depends on how much isolation you want. Let's say you work with SQL, you can either have a column (like @yinziyan1206 suggested), or have a independent table, or have a independent databases all together.

For all of the above, you need to have dependency injection. You can on-the-fly determine the database name just before opening a new session (from DI).

[abdulla-mindfire]

I have written a script for onboarding clients but what if I have to made any change in table then what will be the steps?

[dctalbot]

I would recommend the solution described here https://aws.amazon.com/blogs/database/multi-tenant-data-isolation-with-postgresql-row-level-security/

It's framework agnostic, so you can just use postgres session variables and the SQLAlchemy event listener API