Flask-SQLAlchemy - how do sessions work with multiple databases?

Question

I'm working on a Flask project and I am using Flask-SQLAlchemy.
I need to work with multiple already existing databases.
I created the "app" object and the SQLAlchemy one:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy


app = Flask(__name__)
db = SQLAlchemy(app)

In the configuration I set the default connection and the additional binds:

SQLALCHEMY_DATABASE_URI = 'postgresql://pg_user:pg_pwd@pg_server/pg_db'
SQLALCHEMY_BINDS = {
    'oracle_bind': 'oracle://oracle_user:oracle_pwd@oracle_server/oracle_schema',
    'mssql_bind': 'mssql+pyodbc://msssql_user:mssql_pwd@mssql_server/mssql_schema?driver=FreeTDS'
}

Then I created the table models using the declarative system and, where needed, I set the __bind_key__ parameter to indicate in which database the table is located.
For example:

class MyTable(db.Model):
    __bind_key__ = 'mssql_bind'
    __tablename__ = 'my_table'
    
    id = db.Column(db.Integer, nullable=False, primary_key=True)
    val = db.Column(db.String(50), nullable=False)

in this way everything works correctly, when I do a query it is made on the right database.

Reading the SQLAlchemy documentation and the Flask-SQLALchemy documentation I understand these things (i write them down to check I understand correctly):

• You can handle the transactions through the session.
• In SQLAlchemy you can bind a session with a specific engine.
• Flask-SQLAlchemy automatically creates the session (scoped_session) at the request start and it destroys it at the request end
  

so I can do:

record = MyTable(1, 'some text')
db.session.add(record)
db.session.commit()

I can not understand what happens when we use multiple databases, regarding the session, in Flask-SqlAlchemy.

I verified that the system is able to bind the table correctly at the right database through the __bind_key__ parameter, I can, therefore, insert data on different databases through db.session and, at the commit, everything is saved.

I can't, however, understand if Flask-SQLAlchemy create multiple sessions (one for each engine) or if manages the thing in a different way.
In both cases, how is it possible refer to the session/transaction of a specific database?
If I use db.session.commit() the system does the commit on all involved databases, but how can I do if I want to commit only for a single database?
I would do something like:

db.session('mssql_bind').commit()

but I can not figure out how to do this.

I also saw a Flask-SQLAlchemy implementation which should ease the management of these situations:

Issue: https://github.com/mitsuhiko/flask-sqlalchemy/issues/107
Implementation: https://github.com/mitsuhiko/flask-sqlalchemy/pull/249

but I can not figure out how to use it.

In Flask-SQLAlchemy how can I manage sessions specifically for each single engine?

Answer 1

Flask-SQLAlchemy uses a customized session that handles bind routing according to given __bind_key__ attribute in mapped class. Under the hood it actually adds that key as info to the created table. In other words, Flask does not create multiple sessions, one for each bind, but a single session that routes to correct connectable (engine/connection) according to the bind key. Note that vanilla SQLAlchemy has similar functionality out of the box.

In both cases, how is it possible refer to the session/transaction of a specific database? If I use db.session.commit() the system does the commit on all involved databases, but how can I do if I want to commit only for a single database?

It might not be a good idea to subvert and issue commits to specific databases mid session using the connections owned by the session. The session is a whole and keeps track of state for object instances, flushing changes to databases when needed etc. That means that the transaction handled by the session is not just the database transactions, but the session's own transaction as well. All that should commit and rollback as one.

You could on the other hand create new SQLAlchemy (or Flask-SQLAlchemy) sessions that possibly join the ongoing transaction in one of the binds:

session = db.create_scoped_session(
    options=dict(bind=db.get_engine(app, 'oracle_bind'),
                 binds={}))

This is what the pull request is about. It allows using an existing transactional connection as the bind for a new Flask-SQLAlchemy session. This is very useful for example in testing, as can be seen in the rationale for that pull request. That way you can have a "master" transaction that can for example rollback everything done in testing.

Note that the SignallingSession always consults the db.get_engine() method if a bind_key is present. This means that the example session is unable to query tables without a bind key and which don't exist on your oracle DB, but would still work for tables with your mssql_bind key.

The issue you linked to on the other hand does list ways to issue SQL to specific binds:

rows = db.session.execute(query, params,
                          bind=db.get_engine(app, 'oracle_bind'))

There were other less explicit methods listed as well, but explicit is better than implicit.