SQLAlchemy used in Flask, Session management implementation

Question

As I cannot use the Flask-SQLAlchemy due to models definitions and use of the database part of the app in other contexts than Flask, I found several ways to manage sessions and I am not sure what to do.

One thing that everyone seems to agree (including me) is that a new session should be created at the beginning of each request and be committed + closed when the request has been processed and the response is ready to be sent back to the client.

Currently, I implemented the session management that way:

I have a database initialization python script which creates the engine (engine = create_engine(app.config["MYSQL_DATABASE_URI"])) and defines the session maker Session = sessionmaker(bind=engine, expire_on_commit=False).
In another file I defined two function decorated with flask's before_request and teardown_request applications decorators.

@app.before_request
def create_db_session():
    g.db_session = Session()

@app.teardown_request
def close_db_session(exception):
    try:
        g.db_session.commit()
    except:
        g.db_session.rollback()
    finally:
        g.db_session.close()

I then use the g.db_session when I need to perform queries: g.db_session.query(models.User.user_id).filter_by(username=username)

Is this a correct way to manage sessions ?

I also took a look at the scoped sessions proposed by SQLAlchemy and this might be anotherway of doing things, but I am not sure about how to change my system to use scoped sessions... If I understood it well, I would not use the g variable, but I would instead always refer to the Session definition declared by Session = scoped_session(sessionmaker(bind=engine, expire_on_commit=False)) and I would not need to initialize a new session explicitly when a request arrives.
I could just perform my queries as usual with Session.query(models.User.user_id).filter_by(username=username) and I would just need to remove the session when the request ends:

@app.teardown_request
def close_db_session(exception):
    Session.commit()
    Session.remove()

I am a bit lost with this session management topic and I would need help to understand how to manage sessions. Is there a real difference between the two approaches above?

user_11572438 · Answer

Your approach of managing the session via flask.g is completely acceptable to my point of view. Whatever we are trying to do with SQLAlchemy, one must remember the basic principles:

Always clean up after yourself. At web application runtime, if you spawn a lot of sessions without .close()ing them, this will eventually lead to connection overflow at your DB instance. You are handling this by calling finally: session.close()
Maintain session independence. It's not good if various application contexts ( requests, threads, etc..) share the same session instance, because it's not deterministic. You are doing this by ensuring only one session runs per one request.

The scoped_session can be considered as just an alternative of flask.g - it ensures that within one thread, each call to the Session() constructor returns the same object - https://docs.sqlalchemy.org/en/13/orm/contextual.html#unitofwork-contextual

It's a SQLA batteries included version of your session management code.

So far, if you are using Flask, which is a synchronous framework, I don't think you will have any issues with this setup.

SQLAlchemy used in Flask, Session management implementation

1 Answers