Flask and sqlalchemy: handling sessions

Question

I recently started using Flask + Sqlalchemy for my project, and have been noticing 500 errors after a day of leaving the server up. I think this is due to the database session timing out, but I'm not sure. Are we supposed to make a new session for every request, or is it one for one the Flask app starts? I have this at the top of my app.py

from sqlalchemy import Column, ForeignKey, Integer, String, create_engine, func, cast, Float 
from sqlalchemy.ext.declarative import declarative_base 
from sqlalchemy.orm import relationship,scoped_session,sessionmaker,aliased
engine = createengine(DB_PATH) 
Session = sessionmaker(bind=engine) 
session = Session() 
app = Flask(name_)

And then for all queries in the views, I do something like: "session.query(Table)..." Is this wrong, should I be making a session for every endpoint call?

Answer 1

There are cases when using the Flask-SQLAlchemy Extension may not be appropriate. For example, if you are managing your model classes and database connection details in a completely different Python module for reuse with software outside of Flask, you don't want/need the extension to manage these things for you.

Assuming you have your own code to connect to the database and create the Session class via something like (also assuming engine is provided):

Session = scoped_session(sessionmaker(bind=engine))

For pages that require a database connection, you would create an session instance using that object:

# import the "Session" object created above from wherever you put it

def my_page():
    session = Session() # creates a new, thread-local session
    ...

When the response is finished we need to remove the session that was created. That needs to be done after the end of the my_page function (so we can't close it there), but before the end of the response. To remove it at the right time, add this code when you create the Flask application:

# import the "Session" object created above from wherever you put it

# despite the name, this is called when the app is
# torn down _and_ when the request context is closed

@app.teardown_appcontext
def shutdown_session(exception=None):
    ''' Enable Flask to automatically remove database sessions at the
    end of the request or when the application shuts down.
    Ref: http://flask.pocoo.org/docs/patterns/sqlalchemy/
    '''
    Session.remove()

Note that in the latter instance remove() is being called on Session (capital S), not session (lower s, the thread local instance). SQLAlchemy is aware of which session is in which thread and will close the session that was created for the current thread.

There are probably other ways to do this, but this is the idea. Note that SQLAlchemy provides connection pooling for you.

Answer 2

The accepted answer has a couple things wrong with it, though admittedly it should work.

1. It relies implicitly on threading.local(). While for most applications are fine, it ignores the possibility of greenlet being installed, in which case the local thread ID is not enough.
2. It unnecessarily uses g. As noted in a comment, the scoped_session already handles this part.

Flask itself does not manage threads, that is the responsibility of the WSGI server. Appropriately, per the documentation, relying on thread scope is not the recommended way to store the db session, though it should work fine because the request is likely directly associated with the thread.

In particular, while using a thread local can be convenient, it is preferable that the Session be associated directly with the request, rather than with the current thread. Thus, it is preferable to use a custom scope instead according to the documentation so we may associate our session directly with the request context. This can be accomplished using a custom created scope.

pseudocode from the SQLAlchemy docs

from my_web_framework import get_current_request, on_request_end
from sqlalchemy.orm import scoped_session, sessionmaker

Session = scoped_session(sessionmaker(bind=some_engine), scopefunc=get_current_request)

@on_request_end
def remove_session(req):
    Session.remove()

For SQLAlchemy, the cleanest object to attach the session to appears to be the Application Context, as this is the highest level variable directly associated with a request. Here is the flask documentation on how the Flask context works. You may access the internal LocalStack with the AppContext instances via the _app_ctx_stack. This stackoverlow answer points to the same solution. The _app_ctx_stack.__ident_func__ function is helpful because it either returns the thread id or calls a greenlet function to give a usable identifier if that is installed. That said, flask does appear to use the thread local itself for many things. I searched and searched but could not find anything that guaranteed a WSGI server such as gunicorn or uwsgi would create a thread for each request. If anyone has a source on that, I'd love to see it. Regardless, the recommended approach is to use the application context, and this is semantically cleaner than relying on a thread having the same lifetime as a request.

Finally, another comment mentions using Flask-SQLAlchemy. While this is a good idea for most projects, I don't think it always makes sense. Personally, I wanted my model definition to be defined with SQLAlchemy, not through Flask-SQLAlchemy. I think it is likely (in my case) that the models will be used outside of Flask in the near future. I also wanted didn't want a different API than SQLAlchemy. period. While I suppose they're likely extremely similar if not entirely the same, it's not using SQLAlchemy itself which I didn't like. I retroactively found a blog from towardsdatascience that went to the same conclusion.

All this said, my solution looks pretty much the same to what the towardsdatascience folks did. I'm adding in relevant portions from a repo they published that does this.

main.py

from flask import Flask, _app_ctx_stack
from sqlalchemy.orm import scoped_session
from .database import SessionLocal, engine

app = Flask(__name__)
app.session = scoped_session(SessionLocal, scopefunc=_app_ctx_stack.__ident_func__)

@app.teardown_appcontext
def remove_session(*args, **kwargs):
    app.session.remove()

database.py

  
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

SQLALCHEMY_DATABASE_URL = "sqlite:///./test.db"
# SQLALCHEMY_DATABASE_URL = "postgresql://user:password@postgresserver/db"

engine = create_engine(
    SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

The subject get's pretty complex, so I welcome comments and I'll update the answer, though hopefully this research helps others.