Scoping SQLAlchemy's Session while using Tornado.gen

Note: This article is not about using SQLAlchemy and Tornado to do asynchronous calls to your database. It is about making sure each request uses the correct session even if it does async calls to other services.

Session management with SQLAlchemy and Tornado is a little bit tricky if you are doing asynchronous calls. You have to be careful to make sure your request is using the same session before and after an async call. The recommended way to do this is to associate a session with the request itself, but this is not feasible if you are using declarative_base. I got around this issue by making my own scoping function and patching tornado. 

The scoping function is extremely simple. The get method it defines is used by scoped_session to determine the correct session object to provide from its registry. The handlers and patches will use to the set method to set the correct session scope.

We create a base handler that sets the correct scope using the scope object created in database.py.

Finally, we do a little bit of monkey patching so the correct scope will be maintained before and after async calls using tornado.gen

With all this in place, we can safely have declarative base models and commit sessions whenever we want.

6 responses
Hi, thanks for sharing, I was looking for it. Not sure if I understand if the monkey patch is really necessary, is it? It would be nice to know what to do in case I am using ioloop not tornado gen. Any idea? Thanks!
Hi @Akira. If you do not monkey patch, you would have to manually set and get the database scope before and after every single call async call. For example: scope = database.scope.get() response = yield http.fetch(self.url) database.scope.restore(scope)
Hi, interesting post. Stumbled upon it while researching for getting tornado and sqlalchemy to work together. I was starting to do something like having a session for each request, sort like of layed out here - http://yashh.com/integrating-sqlalchemy-tornado/ , but you write that that is not feasible if using declaritive base. I am curious, why is that? Why is that a problem?
@Jeppe - If you save your session on the request handler object and reference it only there, you should be fine. However, if you want to reference the database session inside the models it becomes cumbersome to do it that way.
database.scope.set(nil) and database.scope.restore(self.scope) . They is not exists Code not running ?
1 visitor upvoted this post.