1

I'm currently working on a Flask API using SQLAlchemy where users query to retrieve information but no changes are made to the database.

I would like to have a better understanding of best practices regarding opening new sessions.

My main question is whether it's best to open one session for the entire API, or multiple ones (one for each route).

Some of the guidance in the SQLAlchemy documentation would seem to argue for a single session.

As a general rule, keep the lifecycle of the session separate and external from functions and objects that access and/or manipulate database data. This will greatly help with achieving a predictable and consistent transactional scope.

However, in this answer from SQLAlchemy developer zzzeek, he says:

In web applications, we usually say, hey why don't you make a brand new Session on each request, rather than using the same one over and over again. This practice ensures that the new request begins "clean".

This would seem to argue for multiple sessions, which makes intuitive sense to me for this case.

Then again, maybe I am overthinking it: since the users of my API will not be making any changes to the database, does it matter one way or the other? If so, why?

Thanks!

# Option 1: open one session here for the entire API
# session = Session(engine)

app = Flask(__name__)

if __name__ == "__main__":
    app.run(debug=True)

@app.route("/url")
def func_name():
    # Option 2: open session here instead, and do it again in each route that retrieves data
    session = Session(engine)
    # do queries
    session.close()
    # return data
    pass

@app.route("/")
def welcome():
    return (
        f'''<h1>Welcome to the Generic API!</h1>'''
MarshViper
  • 11
  • 2
  • One session per request. The Flask-SQLAlchemy extension will configure this automatically and transparently, if you use it. – snakecharmerb Aug 26 '23 at 20:59

0 Answers0