Integrate SQLAlchemy with Falcon framework (second version)

A long time ago I published a blog post how to integrate SQLAlchemy with Falcon framework.

Although it worked, I had issues with sessions when I wanted to use it with gevent.

The improved version of SQLAlchemySessionManager middleware creates a session for every request and closes it when the request ends.

Let’s say that we have a settings.py file, something like this:

POSTGRESQL = {
    'engine': 'psycopg2',
    'pool_size': 100,
    'debug': False,
    'username': 'user',
    'password': 'qwerty123',
    'host': 'example.com',
    'port': 5432,
    'db_name': 'db',
}

SQLALCHEMY = {
  'debug': False,
}

Engine.

from sqlalchemy import create_engine

import settings


engine = create_engine(
    '{engine}://{username}:{password}@{host}:{port}/{db_name}'.format(
        **settings.POSTGRESQL
    ),
    pool_size=settings.POSTGRESQL['pool_size'],
    echo=settings.SQLALCHEMY['debug']
)

Session.

from contextlib import contextmanager

from sqlalchemy.orm import sessionmaker

import settings

from core.db.engine import engine


Session = sessionmaker(
    bind=engine,
    **settings.SQLALCHEMY['sessionmaker']
)

Middleware.

class SQLAlchemySessionManager:
    """
    Create a session for every request and close it when the request ends.
    """

    def __init__(self, Session):
        self.db_session = Session

    def process_resource(self, req, resp, resource, params):
        if req.method == 'OPTIONS':
            return

        req.context['db_session'] = self.db_session()

    def process_response(self, req, resp, resource, req_succeeded):
        if req.method == 'OPTIONS':
            return

        if req.context.get('db_session'):
            if not req_succeeded:
                req.context['db_session'].rollback()
            req.context['db_session'].close()

import falcon


app = falcon.API(middleware=[
    SQLAlchemySessionManager(Session),
])

Session is accessible for every request.

import falcon


class MyAPI:

    def on_get(self, req, resp):
        # You can access self.session here
        # self.session.add(foo)
        # self.session.commit()