Using Storm and SQLite in Multithreaded Web Applications

Pysqlite doesn't allow you to access the same connection from different threads. The pysqlite manual says: "SQLite connections/cursors can only safely be used in the same thread they were created in."

When using Storm (the ORM) with Werkzeug (the WSGI utility lib) we suffer from the problem that the Werkzeug reloader runs code in a thread. Ok this feature is not exactly important in a production environment, but I can't guarantee that whatever platform I will be deploying the application on will not be threaded, so database access should be proofed against this.

The solution? The Storm manual mentions that you should use a Store/connection per thread. Someone has already done this with the Middlestorm application, which provides a threadsafe store in the WSGI environ. Rightly or wrongly (since I really don't want to have to wait to have a WSGI environ to get the store instance), and I am not exactly sure this kind of thing should be middleware, but that is a debate for another day.

Looking at the code, it uses threading.local(), which is a thread-local attribute store. In other words, each thread will have its own values for the local object's attributes.

So a very simple implementation:

from threading import local

from storm.locals import create_database, store

class ThreadSafeStorePool(object):

 def __init__(self, uri):
     self._db = create_database(uri)
     self._local = local()

 def get(self):
     try:
         return self._local.store
     except AttributeError:
         self._local.store = Store(self._db)
         return self._local.store 

Using an instance of this class, and calling get() when you require the thread-local store (which can be easily hidden behind a property descriptor ensures that each thread has its own Store, and pysqlite stops complaining.

uri = 'sqlite:test.sqlite'

store_pool = ThreadSafeStorePool(uri)

# From one thread
store = store_pool.get()
# Will always return the same store in that thread

# From another thread
store = store_pool.get()

Et cetera.