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
Support the author - Buy me a coffee!
Comments powered by Talkyard.