SQLAlchemy Session Usage Patterns in Web Applications

DONG Yuxuan @ Jan 12, 2019 Asia/Shanghai

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.

Using the Context Manager

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

Advantages

Disadvantages

Using the Scoped Session with Web Framework Hooks

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.

Advantages

Disadvantages

Conclusions

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.