Flask-SQLAlchemy - on the fly connections to multiple databases

Question

I have a flask webapp where users will be able to connect to their own mysql databases and query their own tables

What's the best way to create multiple connections (to different databases) using flask-sqlalchemy. It seems like it needs to be done with scoped_session and sessionmaker but cant seem to wrap my head around it.

Also the second part of the question, once I create a connection to a mysql db for one of the users, how do i persist the connection across requests ?

Currently, i put the connection string for each user on the flask session variable and at each new request, i create the engine and connection as such

engine = create_engine(connection_string, convert_unicode=True)
conn = engine.connect()
db_session = Session(bind=conn) # Session - i create it globally on the __init__ like this Session = scoped_session(sessionmaker()) and import it in the view

## Query using the db_session

This seems super wasteful to create the engine and the connection with every request - cant the connection be persisted across requests ?

I'm having troubles with the same task. Could you post an answer of yours please explaining how did you manage to connect new databases after the app has been started? — MattSom, Nov 09 '20 at 08:07

univerio · Accepted Answer · 2016-04-27T16:27:32.527

One Database

The engine is what allows you to use connection pooling. By default, it will persist connections across requests. The basic usage (without fancy things like scoped_session or sessionmaker) is like this:

engine = create_engine(...)

@app.route(...)
def foo():
    session = Session(bind=engine)
    try:
        session.query(...)
        session.commit()
    finally:
        session.close()
    return ""

On top of this, you can add scoped_session and sessionmaker:

engine = create_engine(...)
Session = sessionmaker(bind=engine)
session = scoped_session(Session, scopefunc=...)

@app.route(...)
def foo():
    try:
        session.query(...)
        session.commit()
    finally:
        session.close()
    return ""

flask-sqlalchemy makes your life easier by providing all of this:

db = SQLAlchemy(app)

@app.route(...)
def foo():
    db.session.query(...)
    db.session.commit()
    return ""

Multiple Databases

You can easily extend this concept to multiple databases:

engine1 = create_engine(...)
engine2 = create_engine(...)

@app.route(...)
def foo():
    session = Session(bind=choose_engine_for_user())
    try:
        session.query(...)
        session.commit()
    finally:
        session.close()
    return ""

When you add scoped_session and sessionmaker:

engine1 = create_engine(...)
engine2 = create_engine(...)
Session1 = sessionmaker(bind=engine1)
Session2 = sessionmaker(bind=engine2)
session1 = scoped_session(Session1, scopefunc=...)
session2 = scoped_session(Session2, scopefunc=...)

@app.route(...)
def foo():
    session = choose_session_for_user()
    try:
        session.query(...)
        session.commit()
    finally:
        session.close()
    return ""

This gets a little annoying when you have many databases, in which case you should probably write a registry class to keep track of all the engines and sessions:

class SessionRegistry(object):
    _registry = {}

    def get(self, url, **kwargs):
        if url not in self._registry:
            engine = create_engine(url, **kwargs)
            Session = session_maker(bind=engine)
            session = scoped_session(Session, scopefunc=...)
            self._registry[url] = session
        return self._registry[url]

registry = SessionRegistry()

@app.route(...)
def foo():
    session = registry.get(...)
    try:
        session.query(...)
        session.commit()
    finally:
        session.close()
    return ""

You'll need to add some kind of LRU on top of it so that there's no unbounded creation of engines.

flask-sqlalchemy has support for a limited form of multiple databases where each of your model connects to a different database. If this applies to you, the documentation is here.

Thanks Univerio. The problem is that i dont have multiple databases for the app itself - its the app users who need to create connections to their databases on the fly to query their tables. Can you please elaborate a little more on "registry class to keep track of all the engines and sessions" — Shankar ARUL, Apr 27 '16 at 09:29
@sarul See edit. If you need to be able to connect to arbitrary (unbounded number of) databases during the life time of your app then you'll need to add some kind of LRU on top of the registry to keep memory leaks in check. — univerio, Apr 27 '16 at 16:28
How can I use this way in flask-sqlalchemy. Bind is not the correct way for me. I need to change db on fly too. `app.config[ 'SQLALCHEMY_DATABASE_URI'] = DataBaseConfig.generate_database_uri()` is for entire app. How can I use your solution in flask-sqlalchemy? — Amir Shabani, Nov 24 '18 at 06:59
@AmirShabani Use what? `SessionRegistry`? You can just copy the code and then add some LRU logic. — univerio, Nov 24 '18 at 07:04
@univerio Thanks. I found a new word that I add to my glossary: **Multitenant**. And I found [this link](https://quanttype.net/posts/2016-03-15-flask-sqlalchemy-and-multitenancy.html) — Amir Shabani, Dec 09 '18 at 07:37
@AmirShabani Unfortunately, that's not what multitenancy is. Multitenancy refers to using a single database to house multiple things, usually different users (tenants); multiple binds is the use of multiple databases in a single app. — univerio, Dec 13 '18 at 04:38
@univerio thank you for this, it is great. I have an issue with your solution, but it is too long for a comment. Would you have a look at https://stackoverflow.com/questions/61395802/flask-sqlalchemy-uwsgi-db-connection-problem-when-more-than-on-process-is-use, please? Thanks — Libra, Apr 23 '20 at 19:54

Flask-SQLAlchemy - on the fly connections to multiple databases

1 Answers1

One Database

Multiple Databases

Linked