Multitenancy with Flask

What is multi-tenancy

Consider a SaaS platform that provide access to multiple client organizations. These organizations - tenants - may have each its own database for safety and data protection reasons. It can be a database on a single RDBMS server or physically different servers. Usually additional central database (i.e., General) stores metadata and list of available tenants.

Flask-SQLAlchemy

Flask-SQLAlchemy provides interface only to one database. Flask app configuration defines SQLALCHEMY_DATABASE_URI for connection information for it. It possible to extend it to multiple tenant databases using binds

Tenant dependent endpoint

Consider this endpoint that displays tenant users and gets tenant name as its parameter

@app.route("/<tenant_name>/users")
def index(tenant_name):
    tenant_session = get_tenant_session(tenant_name)
    if not tenant_session:
        abort(404)
    users = tenant_session.query(User).all()
    return jsonify({tenant_name: [i.username for i in users]})

Models used are very basic:

class User(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(100), nullable=False)


class Tenant(db.Model):
    __tablename__ = 'tenants'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)

Users have two fields - just id and username. These tables are in tenant database. And tenant model is in general database. I keep track of available tenants here so if query refers to unknown tenant it will give 404 error.

Building tenant session

To get tenant session

• check if tenant name is in tenants table from General database
• create a URI in SQLALCHEMY_BINDS dictionary that is in Flask app configuration - current_app.config
• get SQLAlchemy engine that accepts that bind
• form session from a session factory - sessionmaker - that uses the engine

MYSQL_URI = 'mysql+pymysql://user:pwd@localhost/{}?charset=utf8'

@simple_cache
def get_known_tenants():
        tenants = Tenant.query.all()
        return [i.name for i in tenants]


def prepare_bind(tenant_name):
        if tenant_name not in current_app.config['SQLALCHEMY_BINDS']:
            current_app.config['SQLALCHEMY_BINDS'][tenant_name] = MYSQL_URI.format(tenant_name)
        return current_app.config['SQLALCHEMY_BINDS'][tenant_name]


def get_tenant_session(tenant_name):
        if tenant_name not in get_known_tenants():
            return None
        prepare_bind(tenant_name)
        engine = db.get_engine(current_app, bind=tenant_name)
        session_maker = db.sessionmaker()
        session_maker.configure(bind=engine)
        session = session_maker()
        return session

I check tenant availability at every request. This information doesn’t change often so it’s cached.

Cache slow changing data

I’ll be using simple werkzeug cache. Every process and uwsgi worker will get its own instance. To share the data between processes this cache can be easily extended to use external store like Redis/Memcached.

Simple_cache is a decorator using function name as a key like so:

cache = SimpleCache()


def simple_cache(f):
    @wraps(f)
    def wrapper(*args, **kwargs):
        function_name = f.__name__
        if cache.has(function_name):
            result = cache.get(function_name)
        else:
            result = f(*args, **kwargs)
            cache.set(function_name, result)
        return result
    return wrapper

Database initialization

I’ve prepared a SQL dump to generate mock data here. It creates all databases and tables with two tenants: TenantA and TenantB. Each has 2 users:

• userA and userB for TenantA;
• userA and userC for TenantB;

It can be imported with mysql client

mysql --host="<host>" --user="<user>" --password="<password>" < all_databases.sql

Results

The whole code is available at GitHub

Do you use multitenant database? Drop me a message on LinkedIn