How to implement multi tenant application using tortoise-orm with postgres schema per tenant

[xalien10]

Problem Definition I'm trying to use tortoise-orm to build a multi-tenant application using postgres schema. I've two models:

1. Tenant: Tenant model will be kept in the default/public schema of the postgres database. And it'll have the following structure:

class Tenant(Model):
   name = fields.CharField(max_length=150)
   host_name = fields.CharField(max_length=100)
   tenant_schema_name = fields.CharField(max_length=200, null=True)
   db_url = fields.CharField(max_length=200, null=True)
   
   class Meta:
       table = "tenant"

And it'll be saved in the public schema of the postgres database.

2. User: User model will be kept in the non default schema of the database. And this schema name will be used from the tenant_schema_name field for a specific tenant record. For instance, when a new tenant will be created with the tenant_schema_name=tenant_1234 then we'll create a new database schema with the name tenant_1234 and will try to migrate the User model into that schema. User model will have following structure:

class User(Model):
    email = fields.CharField(
        max_length=50, unique=True,
        validators=[RegexValidator("([A-Za-z0-9]+[.-_])*[A-Za-z0-9]+@[A-Za-z0-9-]+(\.[A-Z|a-z]{2,})+", re.I)]
    )
    first_name = fields.CharField(max_length=100, null=True)
    last_name = fields.CharField(max_length=100, null=True)
    password = fields.CharField(max_length=128, null=False)
    date_joined = fields.DatetimeField(auto_now_add=True, use_tz=False, null=True)
    last_login_at = fields.DatetimeField(auto_now=True, use_tz=False, null=True)
    status = fields.CharEnumField(StatusChoices, default=StatusChoices.UNVERIFIED)
    
    class Meta:
        table = "user"

I tried to manually create database schema and set the schema search path to the tenant specific schema. And then tried to create the User record but it is creating the user record into the default/public schema instead of tenant specific schema.

I tried to use schema attribute of the Model.Meta but it seems having no effect. I tried to use db_url with optional parameter schema=tenant for Tortoise.init but still it can not create record on the tenant specific schema. I found no work around to use database schema to implement multi tenancy with postgres schemas and tortoise-orm. I've the Tortoise init configuration as follows:

TORTOISE_ORM = {
    "connections": {
        "public": "postgres://root:[email protected]:5432/root",
        "tenants": "postgres://root:[email protected]:5432/root"
    },
    "apps": {
        "public": {
            "models": ["apps.tenants.models", "aerich.models"],
            "default_connection": "public",
        },
        "tenants": {
            "models": ["apps.users.models"],
            "default_connection": "tenants",
        }
    },
}

So, I want to know if there is any way to set database schema for a database connection client with tortoise-orm? And I'm also interested to know if there is any way to dynamically change database models Meta attribute for instance, if it is possible to update table=users instead of keeping it as table=user for the above User model?

I was trying something similar with tortoise-orm that is described here @long2ice @grigi

[intigratech]

I've achieved something similar using FastAPI. I basically registered a middleware that is responsive for dynamically generating the db_config the first time a request for a particular db is processed. I then cache the instance for subsequent requests.

[xalien10]

@intigratech can you please share some snippet or links related to that?

[intigratech]

@xalien10 @grigi @long2ice

So, in my case I needed to support multiple databases because each client would have a separate database. The databases may have some shared apps/models but they could be different in some cases. The database name is always the subdomain so we extracted it from the host header to identify the db. Im then generating the db_config dynamically for the db with the relevant apps. The apps are also dynamic. Then Tortoise is inited and the db name is passed in the transaction. In theory this should work?

@app.middleware("http")
async def set_database_for_request(request: Request, call_next):
    if request.url.path.startswith('/static') or 'create-database' in request.url.path or 'delete-database' in request.url.path or 'backup-database' in request.url.path or 'restore-database' in request.url.path or 'database/manager' in request.url.path: 
        return await call_next(request)
    
    # Extracting environment variables and host header
    environment = ENVIRONMENT
    db_filter = DB_FILTER
    host_header = request.headers.get("Host")

    # Determine the database name based on the request
    db_name = DEFAULT_DB
    if environment == "development" or db_filter == "False":
        db_name = DEFAULT_DB
    elif db_filter == "domain":
        db_name = host_header.split(".")[1]  # Domain as DB name
    elif db_filter == "subdomain":
        db_name = host_header.split(".")[0]  # Subdomain as DB name
    
    #Set DB name in request for use in depedencies injection
    request.state.db = db_name


    # Dynamic initialization for each unique database
    if db_name not in initialized_orms:
        # Load installed modules for the determined database name
        apps_config = await load_installed_modules(db_name)
        # Database connection config, replace with actual dynamic config based on db_name
        db_config = {
            "connections": {
                db_name: {
                    "engine": "tortoise.backends.asyncpg",  # Engine for asyncpg (PostgreSQL)
                    "credentials": {
                        "database": db_name,  # Database name dynamically determined
                        "host": DB_HOST,  # Host for your PostgreSQL database
                        "port": DB_PORT,  # Port for your PostgreSQL database, typically 5432
                        "user": DB_USERNAME,  # User for your PostgreSQL database
                        "password": DB_PASSWORD,  # Password for your PostgreSQL database
                        "max_size": MAX_CONNECTIONS,  # Maximum number of connections in the pool
                        "min_size": MIN_CONNECTIONS,  # Minimum number of connections in the pool
                        "max_inactive_connection_lifetime": MAX_IDLE_TIME,  # Maximum idle time for connections
                    }
                }
            },
            "apps": apps_config
        }

        # Initialize Tortoise ORM for this db_name if not already done
        await Tortoise.init(config=db_config)
        await build_model_registry(db_name)
        
        # Store the config to avoid re-initialization
        initialized_orms[db_name] = db_config

    # Store db_name as a request attribute
    db_ctx.set(db_name)

    response = None
    try:
        async with in_transaction(db_ctx.get()) as connection:
            response = await call_next(request)
    except Exception as e:
        logger.error(f"An error occurred, rolling back the transaction: {e}")
        raise e
    
    return response