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 ?

Answer 1

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.