I wanted to implement a flask app with multi tenant support. I will get the tenant name from request header.

*

import os from dotenv import load_dotenv

load_dotenv()

class Config: # SQLALCHEMY_DATABASE_URI = os.getenv('SQLALCHEMY_DATABASE_URI') # or 'postgresql://admin:admin@localhost:5433/login' # + os.path.join(basedir, 'app.db') SQLALCHEMY_TRACK_MODIFICATIONS = False

# SQLALCHEMY_DATABASE_URI='postgresql://postgres:admin@localhost:5433/login'

SQLALCHEMY_BINDS={ 'SET': 'postgresql://postgres:admin@localhost:5433/login', 'esi': 'postgresql://postgres:admin@localhost:5433/esi_login' }

Below I'm setting into global variable

middleware.py

in this i'm getting the tenant name and setting it to a global variable g.tenant_id = tenant_name

from flask import request, g
from app import app
from appmon.db import db
from app.utils import Database

@app.before_request
def before_request():
    tenant_name = request.headers.get('X-Tenant-Name')
    try:
        print('X-Tenant-Name: ' + str(tenant_name))
        if tenant_name:
                g.tenant_id = tenant_name
                print(f'Bind key: {g.tenant_id}')
        else:
            raise Exception("Tenant header missing")

        Database(tenant_name).switch_schema()
        print(f'Schema switched to {tenant_name}')
    except Exception as e:
         print(str(e))

models.py In models I'm setting bind key like below ** def init(self,bind_key): self.bind_key = bind_key**

from appmon.db import db
from flask import jsonify as jsonif

class User(db.Model):
    __tablename__ = 'user'
    __table_args__ = {'schema':'iam'}
    __bind_key__ = None

    uid = db.Column(db.Integer, primary_key=True, server_default=db.text("nextval('iam.users_seq')"))
    username = db.Column(db.String, nullable=False)
    password = db.Column(db.String, nullable=False)
    email = db.Column(db.String, nullable=False)
    tenant_id = db.Column(db.Integer, nullable=False)

    def __init__(self,bind_key):
        self.__bind_key__ = bind_key

    def jsonify(self):
        return jsonif({'uid':self.uid,'username':self.username,'password':'','tenant_id':self.tenant_id})

routes.py

In this file I have created POST api to store user info based on tenant but its inserting to default db if default bind key is provided else getting Could not locate a bind configured on SQL expression or this Session. or Bind key 'None' is not in 'SQLALCHEMY_BINDS' config. error

from flask import Blueprint, request, jsonify, g
from app.models.user import User
from appmon.db import db

user_bp = Blueprint('user', __name__)

@user_bp.route('/profile', methods=['GET'])
def profile():
    user = 'Raju'
    return jsonify({'message':f"Hello from {user}"})

@user_bp.route('/', methods=['POST'])
def create_user():
    try:
        data = request.get_json()

        new_user = User(str(g.tenant_id))
        # new_user.__bind_key__ = str(g.tenant_id)

        print(f'__bind_key__: {new_user.__bind_key__}')

        new_user.username = data['username']
        new_user.password = data['password']
        new_user.email = data['email']
        new_user.tenant_id = data['tenant_id']
        
        db.session.add(new_user)
        print('=============get_tenant_session')

        db.sessionmit()
        return new_user.jsonify(), 200
    except Exception as e:
        print(str(e))
        return jsonify({'error': str(e)}),500

Let me know what's wrong in the above code Thanks in advance!!

I wanted to implement a flask app with multi tenant support. I will get the tenant name from request header.

*

import os from dotenv import load_dotenv

load_dotenv()

class Config: # SQLALCHEMY_DATABASE_URI = os.getenv('SQLALCHEMY_DATABASE_URI') # or 'postgresql://admin:admin@localhost:5433/login' # + os.path.join(basedir, 'app.db') SQLALCHEMY_TRACK_MODIFICATIONS = False

# SQLALCHEMY_DATABASE_URI='postgresql://postgres:admin@localhost:5433/login'

SQLALCHEMY_BINDS={ 'SET': 'postgresql://postgres:admin@localhost:5433/login', 'esi': 'postgresql://postgres:admin@localhost:5433/esi_login' }

Below I'm setting into global variable

middleware.py

in this i'm getting the tenant name and setting it to a global variable g.tenant_id = tenant_name

from flask import request, g
from app import app
from appmon.db import db
from app.utils import Database

@app.before_request
def before_request():
    tenant_name = request.headers.get('X-Tenant-Name')
    try:
        print('X-Tenant-Name: ' + str(tenant_name))
        if tenant_name:
                g.tenant_id = tenant_name
                print(f'Bind key: {g.tenant_id}')
        else:
            raise Exception("Tenant header missing")

        Database(tenant_name).switch_schema()
        print(f'Schema switched to {tenant_name}')
    except Exception as e:
         print(str(e))

models.py In models I'm setting bind key like below ** def init(self,bind_key): self.bind_key = bind_key**

from appmon.db import db
from flask import jsonify as jsonif

class User(db.Model):
    __tablename__ = 'user'
    __table_args__ = {'schema':'iam'}
    __bind_key__ = None

    uid = db.Column(db.Integer, primary_key=True, server_default=db.text("nextval('iam.users_seq')"))
    username = db.Column(db.String, nullable=False)
    password = db.Column(db.String, nullable=False)
    email = db.Column(db.String, nullable=False)
    tenant_id = db.Column(db.Integer, nullable=False)

    def __init__(self,bind_key):
        self.__bind_key__ = bind_key

    def jsonify(self):
        return jsonif({'uid':self.uid,'username':self.username,'password':'','tenant_id':self.tenant_id})

routes.py

In this file I have created POST api to store user info based on tenant but its inserting to default db if default bind key is provided else getting Could not locate a bind configured on SQL expression or this Session. or Bind key 'None' is not in 'SQLALCHEMY_BINDS' config. error

from flask import Blueprint, request, jsonify, g
from app.models.user import User
from appmon.db import db

user_bp = Blueprint('user', __name__)

@user_bp.route('/profile', methods=['GET'])
def profile():
    user = 'Raju'
    return jsonify({'message':f"Hello from {user}"})

@user_bp.route('/', methods=['POST'])
def create_user():
    try:
        data = request.get_json()

        new_user = User(str(g.tenant_id))
        # new_user.__bind_key__ = str(g.tenant_id)

        print(f'__bind_key__: {new_user.__bind_key__}')

        new_user.username = data['username']
        new_user.password = data['password']
        new_user.email = data['email']
        new_user.tenant_id = data['tenant_id']
        
        db.session.add(new_user)
        print('=============get_tenant_session')

        db.sessionmit()
        return new_user.jsonify(), 200
    except Exception as e:
        print(str(e))
        return jsonify({'error': str(e)}),500

Let me know what's wrong in the above code Thanks in advance!!

• flask
• sqlalchemy
• flask-sqlalchemy
• multi-tenant

Share Improve this question Follow edited Mar 7 at 9:30 Raju asked Mar 7 at 9:27 RajuRaju 122 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Ultimately, the session looks up the bind key on the metadata associated with the model or table. That association happens during creation. Therefore, changing the bind key after creating a model or table will have no effect.

Documentation link.