# SQLAlchemy Session Usage Patterns in Web Applications

DONG Yuxuan @ Jan 12, 2019

## Abstract

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.

## 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")


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>")
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

• It is not that convenient as we need create a context in each request handling function manually. Maybe we can write a decorator to simplify it, but how simpler a decorator can be than a with statement? Besides, we need pass the session object many times if we handle the request within more than one functions.

## 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>")
user = User(name=user)
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.