Creating multi Tenant system with multiple Database. Switching between databases based on subdomain related to tenant

MultiTenantFlask.py

from functools import wraps
from flask import Flask, g, session, request, abort, jsonify
from flask_migrate import MigrateCommand, Migrate
from flask_script import Manager
from flask_sqlalchemy import SQLAlchemy


flask_app = Flask(__name__, static_folder='./static')
db = SQLAlchemy()
migrate = Migrate()

flask_app.add_url_rule('/static/<path:filename>',
                       endpoint='static',
                       subdomain='<tenant>',
                       view_func=flask_app.send_static_file)


flask_app.config['SERVER_NAME'] = 'localhost:5000'


class Hello(db.Model):
    """
    demo Table.

    contains name for eg.
    """
    __tablename__ = 'hello'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(2000), index=True, nullable=False)


@flask_app.before_request
def start():
    """
    Switching DataBase based on subdomain.

    This is example multi Tenant with multi DataBase rather than multi schema.
    Logic for both is same, before request find the subdomain and
    switch its schema or db.
    """
    from urlparse import urlparse
    subdomain = urlparse(request.url).hostname.split('.')[0]
    if subdomain == 'tenant':
        flask_app.config.update({
            "SQLALCHEMY_DATABASE_URI": 'postgresql://postgres:postgres@localhost/tenant'
            })
    else:
        flask_app.config.update({
            "SQLALCHEMY_DATABASE_URI": 'postgresql://postgres:postgres@localhost/tenant2'
            })


@flask_app.url_value_preprocessor
def before_route(endpoint, values):
    """Remove tenant from values so it not added all url path."""
    if values is not None:
        values.pop('tenant', None)


@flask_app.route('/hello', subdomain='<tenant>')
def display_name():
    """Display all object in table hello for each tenant subdomain."""
    oHello = Hello.query.all()
    return jsonify(tenant=[e.name for e in oHello]), 200


if __name__ == '__main__':
    db.init_app(flask_app)
    # default db
    flask_app.config.update({
        "SQLALCHEMY_DATABASE_URI": 'postgresql://postgres:postgres@localhost/tenant2'
        })
    migrate.init_app(flask_app, db)
    manager = Manager(flask_app)
    manager.add_command('db', MigrateCommand)
    manager.run()

This is using flask framework to create multi tenant using subdomain.

Logic

• Check subdomain of the hostname before request.
• Switch between db connection or scheme.
• Execute the code.

Hi Tony, using before_request method for switching the SQLALCHEMY_DATABASE_URI at runtime is a good approach. But for me switching SQLALCHEMY_DATABASE_URI at runtime did not switch the DB actually. Can you please provide me what more details are needed to get it work?

Legal