Flask unittests with in memory sqlite

To homepage

While writing unittests for the flask stack powering the website of my startup, liwwa, the db fixture tests quickly became quite the bottleneck and slowed down my tests by increments of 1 second per new test on my dev machine. The reason was that I was dropping and recreating the whole db on each test using SQLAlchemy’s Metadata.create_all and Metadata.drop_all methods, to ensure cleanliness of test environment at each run.

Once in this situation, I decided to try to use a sqlite database instead of the local MySQL instance, hoping that sqlite DDLs would be much more lightweight than MySQL’s. I changed the SQLAlchemy connection string to be to a temporary sqlite file that gets deleted and recreated with create_all at the beginning of each test. Since my models do not use any MySQL specific features, SQLAlchemy abstraction made sqlite a drop in replacement for MySQL. However, if the need arises, its pretty simple to have some tests – instead of all of them – simply write to the MySQL backend if we need to test MySQL specific features.

This cut down my testing time by half. But 0.5s is still a lot of time to execute a single test. The next option I explored is whether I can put the sqlite file in a ramdisk. This was kind of klunky and platform dependent, and fortunately a quick google search yielded sqlite’s sweet in-memory database support. So I quickly changed the SQLAlchemy connect string so that its just sqlite:// and ran nosetests. To my dismay I started getting a bunch of errors that look like:

OperationalError: (OperationalError) no such table: <table_name_redacted>

Stepping through a debbugger I could see that Metadata.create_all command was running correctly and I could even see the tables by doing a select * from sqlite_master in the current db connection. However, once it got to the body of the tests, the tables disappeared!

Looking into the sqlite in-memory database documentation, I noticed it says: “The database ceases to exist as soon as the database connection is closed. Every :memory: database is distinct from every other. So, opening two database connections each with the filename “:memory:” will create two independent in-memory databases.” Aha! So if SQLAlchemy is doing some connection pooling or dropping and recreating connectiongs, it is effectively dropping the whole DB!

The solution turned out to be simple, thanks to SQLAlchemy’s StaticPool feature which shares one connection among all threads. The fix itself is documented here:

from sqlalchemy.pool import StaticPool
engine = create_engine('sqlite://',

Once I did that change, all tests passed in under 0.5 second, yielding about 10x speedup over the MySQL implementation. Not too shabby :)