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.

Ply

Have you seen #7924466 and if so, what further questions do you have? – Solan

@SeanVieira Thanks - I just checked that answer out and it seems to make sense. I'm thinking of making a scoped session per request and storing it in flask.g. I'm mostly confused about how to set up the class itself. In the example, User is a subclass of db.Model, and db is initialized from the SQLALCHEMY_DATABASE_URI. Would having User inherit from a base class created with sqlalchemy.ext.declarative.declarative_base() work as well, or is there another class I can use instead of Flask-SQLAlchemy's db.Model which doesn't depend on a specific database configuration? – Ply

Read this: flask-sqlalchemy.pocoo.org/2.1/binds then check out this: gist.github.com/adhorn/b84dc47175259992d406. – Rora

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

Tamp

this is the right answer for vertical partitioning but I dont think you can do horizontal with this. i.e. if I have a client_id column on all of my tables and I want everything with odd numbers on one box and everything with even values on the other, I dont think binds will work. I'll need sqlalchemy.ext.horizontal_shard.ShardedSession – Plowboy

Answer 2

You could do something like this. FYI this is pure SQLAlchemy, not with Flask-SQLAlchemy. Hopefully you need this for read-only stuff. You can figure out some other stuff to have to write stuff, like even listeners on your session/models

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# an Engine, which the Session will use for connection
# resources
some_engine_1 = create_engine('postgresql://scott:tiger@localhost/')
some_engine_2 = create_engine('postgresql://adriel:velazquez@localhost/')

# create a configured "Session" class
Session_1 = sessionmaker(bind=some_engine_1)
Session_2 = sessionmaker(bind=some_engine_2)

# create a Session
session_1 = Session_1()
session_2 = Session_2()

Base = declarative_base()

class ModelBase(Base):
    #different custom queries for each database
    master_query = session_1.query_property()
    client_1_query = session_2.query_property()


class User(ModelBase):
    pass
    #ETC


##Accessing the different databases:
User.master_query.filter(User.id == 1).all()
User.client_1_query.filter(User.id == 1).all()

Inellineloquent

Answer 3

You will need to use SQLALCHEMY_BINDS method to bind to mult[le client database.

app.config['SQLALCHEMY_BINDS'] = { 'user1': mysql database', user2: mysqal2database'} 

You will also need to refer to the bind_key name such as "user1" in the model for example:

class users(db.Model):
   __bind_key__
   id = db.Column(Integer, primary_key=True)
   name = db.Column(String)

These method above are in addition to your main SQLALCHEMY_DATABASE_URI that you should set. While you can dynamically bind the other database on user logins method.

Hope this is helpful!

Gery