Multitenant Flask-SQLAlchemy

So you’re writing a web backend with Flask and Flask-SQLAlchemy. Now you want to make the same backend connect to different databases based on the request parameters. What do you do?

Flask-SQLAlchemy supports multiple databases through the bind mechanism. The binds allow you to specify in which database each table lives. What I want to do is to choose the database for all the tables in one go but on per-request basis.

I couldn’t find a definitive solution from the Internet, so I’ll share what I came up with. Here is a small extension of Flask-SQLAlchemy that allows you to (ab)use binds for this:

from flask import g
from flask_sqlalchemy import SQLAlchemy


class MultiTenantSQLAlchemy(SQLAlchemy):
    def choose_tenant(self, bind_key):
        if hasattr(g, 'tenant'):
            raise RuntimeError('Switching tenant in the middle of the request.')
        g.tenant = bind_key

    def get_engine(self, app=None, bind=None):
        if bind is None:
            if not hasattr(g, 'tenant'):
                raise RuntimeError('No tenant chosen.')
            bind = g.tenant
        return super().get_engine(app=app, bind=bind)

We essentially have a per-request default bind for all the tables without a bind key. Now, before you do any database queries, do db.choose_tenant(name). This tells SQLAlchemy which bind to use. For example, you could implement the tenant choosing logic in the @app.before_request hook:

app = Flask(__name__)
app.config['SQLALCHEMY_BINDS'] = {
    'test1': 'sqlite:///test1.db',
    'test2': 'sqlite:///test2.db'
}
db = MultiTenantSQLAlchemy(app)


@app.before_request
def before_request():
    # Just use the query parameter "tenant"
    db.choose_tenant(request.args['tenant'])

Now http://localhost:5000/?tenant=test1 goes to test1.db and http://localhost:5000/?tenant=test2 goes to test2.db.

It was surprisingly simple to make this work. Making Alembic work with this is left as an exercise for the reader.

The full source for the demo is available.