Dynamically setting Flask-SQLAlchemy database connection in multi-tenant app

Question

I have a "multi-tenant" Flask web application which interfaces with 1 "master" MySQL database (used to look up the client information) and dozens of "client" MySQL databases (which all have the same schema).

I'm currently trying to use SQLAlchemy along with the Flask-SQLAlchemy extension to interface with the databases, but I'm struggling to find a way to allow the Models I define in my app to dynamically switch context from one client database to another, depending on the client.

On the Flask-SQLAlchemy site, a simple example is shown along the lines of:

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://username:[email protected]/db1'
db = SQLAlchemy(app)

class User(db.Model):
    # Etc.

The only problem is, the SQLALCHEMY_DATABASE_URI configuration is done statically. I may need to switch between mysql://username:[email protected]/db1 and mysql://username:[email protected]/db1 (or any other arbitrary MySQL URI), depending on which client is making the request.

I've found some similar questions (see below), but I have yet to figure out a clean way to do it when using the Flask-SQLAlchemy extension specifically.

With sqlalchemy how to dynamically bind to database engine on a per-request basis

Flask SQLAlchemy setup dynamic URI

I've also seen some examples that were provided for handling sharded databases (which should apply as well, as the databases are essentially sharded logically by client), but, again, nothing specific to Flask-SQLAlchemy.

If it makes sense, I'm also open to using SQLAlchemy directly, without the Flask-SQLAlchemy extension. I'm new to SQLAlchemy - any help would be greatly appreciated!

Edit: Being able to reflect the table schemas from the database would be a bonus.

Answer 1

If you're using flask-sqlalchemy 0.12 or later, this feature is supported with BINDS.

SQLALCHEMY_DATABASE_URI = 'postgres://localhost/main'
SQLALCHEMY_BINDS = {
'users':        'mysqldb://localhost/users',
'appmeta':      'sqlite:////path/to/appmeta.db'
}

And you can specify connection database in the model definition.

class User(db.Model):
    __bind_key__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)

It will use the mysqldb auto. For more details, you can refer official document. Multiple Databases with Binds