DONG Yuxuan @ Jan 12, 2019
Two usage patterns of SQLAlchemy session in web applications are introduced and analyzed:
1. Using the context manager, which is definitely safe and flexible, but not that convenient
2. Using the
scoped_session with web framework hooks, which is convenient but not that safe and flexible
Session is one of the hardest concept in SQLAlchemy that the official document has a special FAQ for it. However, as the document mentioned, there’re recommended patterns if you’re developing a web application. Those patterns have one core idea: Keep the session scope the same as the request scope.
The first pattern is using the context manager. As all SQLAlchemy-based programs, we must construct the session factory first after the program started.
import sqlalchemy as sa from sqlalchemy.orm import sessionmaker engine = sa.create_engine("sqlite:///:memory:") Session = sessionmaker(bind=engine)
Then we construct a context manager to create, commit/rollback, and close the session.
from contextlib import contextmanager @contextmanager def session(auto_commit=True): sess = Session() try: yield sess if auto_commit: sess.commit() except: sess.rollback() raise finally: sess.close()
When we need database operations, we use the
with statement to build the scope.
with session() as db: smith = User(name="John Smith") db.add(smith)
Now it’s easy to keep the session scope the same as the request scope. We always enter the session context with the
with statement at the beginning of a request. Most web frameworks allow us to handle each request in a corresponding function; for example, in Flask it may look like the code below.
@app.route("/user/add/<name>") def adduser(name): with session() as db: # All the logical code we need to handle this request
It is definitely safe. No matter how we handle the concurrency, threads or processes, coroutines or queues, we always create a new session for a request;
It is flexible as we can manually control the scope of a session. It can be used in all programs, not just web applications
withstatement? Besides, we need pass the session object many times if we handle the request within more than one functions.
The second pattern is using the
scoped_session with web framework hooks. The
scoped_session is a thread-local session registry. It is like a session pool but you will always get the same session within the same thread and get a diffrent one from a diffrent thread. To use this technology, we need modify our startup code.
import sqlalchemy as sa from sqlalchemy.orm import sessionmaker from sqlalchemy.orm import scoped_session engine = sa.create_engine("sqlite:///:memory:") Session = scoped_session(sessionmaker(bind=engine)) # Here we created a session registry
To get a session from the
Session object, you can call
session = Session() but you need not. The
Session object includes proxy behaviors, meaning that the registry itself can be treated just like a session directly; when methods are called on the registry, they are proxied to the underlying thread-local session being maintained by the registry.
What’s the benifit here? OK, in most web applications, a request is associated with a thread. That means the request scope is the same as the thread scope, so we can directly use the
Session object in a request and talk to the database without worrying about the concurrency.
@app.route("/user/add/<name>") def adduser(name): user = User(name=user) Session.add(smith) Session.commit()
This is very convenient, but where do we close the session? Lukily, web frameworks often provide a hook allowing programmers to do things at the end of the request; for example, in Flask it looks like the code below.
# Or you can use `@app.teardown_request` @app.teardown_appcontext def remove_session(): Session.remove()
Session.remove() will close the underlying session first, then discard itself. Once we did this, we can never worry about the session anymore. Just use the global
Session object when we need database operations. Our request handling function can include only the logical code.
This pattern is used with a little improvement in the famous Flask extension Flask-SQLAlchemy and works well. The SQLAlchemy document recommends that you should use the extension if you use Flask, quoted below.
Some web frameworks include infrastructure to assist in the task of aligning the lifespan of a Session with that of a web request. This includes products such as Flask-SQLAlchemy, for usage in conjunction with the Flask web framework, and Zope-SQLAlchemy, typically used with the Pyramid framework. SQLAlchemy recommends that these products be used as available.
It is not that safe because there is a strong assumption that we use threads to handle the concurrency; (The improvement in Flask-SQLAlchemy solved the problem)
It’s not flexible because the session scope is binding to the request scope and you have no choice
Computer science in the real world is all about tradeoff. Only the one who is developing the project can make a conclusion for the project. When I first built a project with SQLAlchemy, I used the first pattern, because that makes me feel everything is in controll when I faced a new framework which I was not familar with. However, I use the second pattern when I started my second SQLAlchemy-based project. In fact, I used Flask-SQLAlchemy, because there’re a lot of good tools greatly integrated with Flask-SQLAlchemy, like Flask-Marshmallow, Flask-Restless, Flask-REST-JSONAPI and they make the convenient pattern more convenient.