Session Management and Flask SQLAlchemy - SeanGrady/life_tracker GitHub Wiki

There are a few different ways to handle the database session(s) in Flask. Sessions can be thread-local--meaning that all individual flask processes/threads have their own session implementation--or request-local--meaning that each request has its own session--or context-local, meaning that a python context manager is used to encapsulate the session as detailed in the SQLAlchemy docs. For various reasons, the creator of SQLAlchemy recommends request-local or context-local sessions as the best pattern, but this is non-trivial to achieve without using flask-sqlalchemy, which I would like to avoid.

One way is to use the context manager as SQLAlchemy describes and instantiate it in every case where the database needs to be accessed. The main problem with this method is that either database objects need to be detached from sessions, handed around, and then reattached when they're used--which is hard to debug and hard to read--or the sessions themselves need to be handed around, which is cumbersome and also difficult to maintain/debug.

Another way is to use Flask's request context management callbacks to set up and tear down a session for each request, but I have yet to dive into this and figure out things like whether I'll ever need a session outside a request context (for example, a scheduled database operation? Should that live in the app? Should it just be something that makes a request?) and if I do, how to make sessions external to a request play nice with the other children.

Currently I am using SQLAlchemy's scoped_session() factory to create a session that is local to the app context (which is effectively thread-local and thread-safe but I think not guaranteed to be request-local). A custom teardown_appcontext function flask_app.callbacks.shudown_session() ensures that the thread-local session instance created at the start of the thread by the scoped_session factory is released at the end of the thread, but I am unsure how Flask manages threads or whether this may mean that one requests's session instance is suddenly removed by another request in a different thread shutting down.