Web request and database transaction integration with Pyramid and SQLAlchemy

I recently had to debug a thorny issue in Hypothesis with the integration between our web framework Pyramid and database ORM SQLAlchemy. This integration is responsible for ensuring that changes to business objects (annotations, users, groups) made when processing API requests or form submissions actually get persisted to the database. Understanding how this works from the existing documentation was not straightfoward. These are my notes on how the various pieces fit together.

SQLAlchemy is a Python database ORM which uses the Unit of Work design pattern. In UoW, changes made to database rows via manipulations of ORM objects are grouped into transactions, which are then either committed or rolled-back as an atomic unit. The application makes commands to the database via a “Session” object.

In a web service, a natural place to start a Unit of Work is when an HTTP request is received. Likewise a natural place to either commit or rollback the Unit of Work is just before the response is returned to the client. If the request succeeded with a 200 status, then the UoW will be committed. If the request failed with a 4xx or 5xx status, then the UoW will be rolled back. When building a Python web application using the Pyramid framework, this behavior can be achieved using a combination of three packages:

• transaction is a generic transaction-management package. Other packages can register data managers which represent a specific resource, such as a database session, which can begin a group of changes that are later committed or rolled back as a unit.
• zope.sqlalchemy handles integration between the generic transaction package and SQLAlchemy sessions.
• pyramid_tm integrates the transaction package with the Pyramid web framework.

pyramid_tm operates as a “tween” (short for “between”, often referred to in other frameworks as “middleware”), which starts a transaction when a request enters Pyramid, before the main handler for the current URL is invoked. If the main handler executes successfully and returns a response pyramid_tm commits the transaction, otherwise it rolls back the changes.

When a transaction from the transaction package is committed or rolled back, the transaction will in turn ask each of its associated data managers to commit or roll back its changes. When the data manager registered by zope.sqlalchemy receives this request, it will in turn call SQLAlchemy’s Session.commit or Session.rollback methods. This will ultimately result in a COMMIT or ROLLBACK command being sent to the database.

Handling transient failures

Transactions can sometimes fail due to transient issues. For example, a concurrent modification to a database row made as part of another web request. In these cases one solution to avoid the client needing to handle the issue is for the web framework to automatically retry the request, including the database commands involved. The transaction package supports a notion of “retryable” exceptions and pyramid_tm handles transactions which fail for such reasons by retrying the full web request processing cycle, up to a specified number of attempts.

pyramid_tm and exception views

As with other web frameworks, Pyramid allows multiple middlewares / tweens to be involved in processing each request. The tweens are nested. Each tween gets as input the request from its outer/parent tween and returns its response to that tween. In some cases it is important to consider the order in which tweens are organized.

When the main view function for a Pyramid request throws an exception, it is caught by the exception view (EXCVIEW) tween, which looks up the appropriate view to render an error response and invokes it.

In earlier versions of pyramid_tm, the pyramid_tm tween was positioned “under” the exception view tween. This means that it executes “closer to” the main request handler:

Consequently the transactions it manages are not active when the exception view executes. Therefore the exception view would have to manage any transactional activities on the database directly. Since pyramid_tm 2.0, it is positioned “over” the exception handling tween or “further away from” the main view in the request processing pipeline:

Further reading and resources

The pyramid-cookiecutter-alchemy project shows how to combine these three packages in a simple Pyramid app. In particular, see the initialization code for the models package.

The init code for the db package handles the setup in Hypothesis.

The documentation for the transaction, pyramid_tm and zope.sqlalchemy packages are useful to read once you familiar with what they do and how they fit together. Unfortunately they are not easy to follow if you are not already familiar with Zope.