Dynamically linking to databases using flask-sqlalchemy

Question

I'm building an api that creates users and each user get a new database. I'd like to link flask-sqlalchemy to that database created. Is there an easy way to do that?

I also want to make it clear that the reason each use gets their own database is to meet regulation so rearranging the schema so that everything is in one database is not an option.

I'm thinking of going with vanilla sqlalchemy as described here: https://dev.to/nestedsoftware/flask-and-sqlalchemy-without-the-flask-sqlalchemy-extension-3cf8 but I want to make sure I have no other options.

Answer 1

Without posting the entire code, the key concepts I use using Flask-SQLalchemy to have dynamic databases are:

from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()

Have a global database registry

DATABASE_REGISTRY = {}
SESSION_MAKER = db.sessionmaker()

create the database engine and add to registry (I have this in another helper function)

engine = db.create_engine(db_uri)
DATABASE_REGISTRY['username'] = { 'engine': engine}

Then to get the user db dynamically, have a helper function

def get_db_session(username):
    db_session = SESSION_MAKER(bind=DATABASE_REGISTRY.get(username)['engine'])
    return db_session

Then you can use:

db_session = get_db_session('user_bob')

bob_query = db_session.query(ATable).all()

And don't forget to close the session

db_session.close()