CODEWITHSUNDEEP
pythonflasksqlalchemyflask-sqlalchemymulti-tenant
jstol
jstol

Reputation: 855

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

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.

Upvotes: 20

Views: 9283

Answers (3)

Rupesh Desai
Rupesh Desai

Reputation: 205

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!

Upvotes: 0

Junyong Yao
Junyong Yao

Reputation: 699

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

Upvotes: 2

AdriVelaz
AdriVelaz

Reputation: 563

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()

Upvotes: 0

Related Questions