Flask, SQLAlchemy and testing

6 minute read


If introductions are needed, Flask is a great Python microframework (which can also be seen as a “framework” of frameworks). With it, one can create a simple API or web application in no time.

I most recently started using it for a personal project and at work, having been using Django exclusively for over 2 years. “Great” needs to be defined better of course, where it excels or would be better replaced by Django or others.

Since starting this path, I made similar findings (the hard way) to what was recently published by Tony Narlock in this comprehensive comparison of Flask and Django.

Soon after going through the introductory docs and writing a first simple app, I wanted to add tests to it. And this is where one finds one of the main differences reported above:

Flask developers will be forced to reinvent the wheel to catch up for things that’d be quick wins with Django.


One popular measure of the quality of a codebase is the test coverage of such a codebase. Some go as far as assuming that “no unit tests mean that the code does not work and cannot be expanded upon”. Not everyone likes to write tests, but not doing comprehensive tests is not an option. As such, tests should be easy to write. They should also be fast. In general, they should not introduce friction or get in your way.

As Patrick covers in his post:

I believe that you should create an environment for developing unit test that is both easy to use and fun to use. These may sound like very fluffy words, but they have meaning for me:

  • create helper methods to allow quick generation of unit tests
  • provide quick feedback on progress of unit testing
  • create an environment that allows for tests to be added quickly
  • make sure that the unit tests can be executed with a single command
  • provide visual feedback of path coverage

As with so many things in software development, if it’s easy to use, it will be used. If it’s tough to use, it won’t be used as much as it should be.

So how do we get this in Flask, if we want to use a relational database?

I won’t cover all of this, but only what concerns Flask and SQL-Alchemy, but the others are easier. Several articles and gists have been written regarding this section I’m covering, but of those that I found, many are outdated, have suggestions I disagree with (you shouldn’t use sqlite for tests if you run a different database in production) or cover specific subtopics separately, so I wrote yet another post.

Flask and SQLAlchemy

If you haven’t heard about SQL-Alchemy and you’re starting in Flask, read this. Django has a ORM, but in Flask it’s a separate framework.

In Django one might take for granted some of the nice features that run in the background. In testing, one is:

Tests that require a database (namely, model tests) will not use your “real” (production) database. Separate, blank databases are created for the tests. Regardless of whether the tests pass or fail, the test databases are destroyed when all the tests have been executed.

No such thing exists in the Flask side. There is of course a compromise between having very lightweight and specialized frameworks or “batteries included” frameworks, opiniated or not. But perhaps extensions here can be improved to save some of the boilerplate. In hindsight such boilerplate is small and I’ll easily port it to new projects, but it was the result of time consuming API reviewing, testing and tinkering.

App config

In Flask, we take care of the details of starting the app. A recommended path is to use a factory for apps. This factory takes as argument a config, for instance:

def create_app(config_object):
app = Flask(**name**.split('.')[0])

Then somewhere in your __init__.py or wherever you start your app object, you detect the environment and load the respective config object. The tests would create a separate app, with the config that is best suited for tests only (although the less you deviate from production, the better).

One of the extensions you’ll want to register above is Flask-SQLAlchemy as it takes care of the boilerplate integration bit between Flask and SQLAlchemy.

With the Flask-SQLAlchemy extension registered, in your config, you’ll need to have specific variables such as SQLALCHEMY_DATABASE_URI, to point to the database each environment should be using.

To have tests running as Django though, we need to have a different database just for tests. This could be solved by having two databases per environment (one being for the tests only). But again in Django “separate, blank databases are created for the tests (…) [and] destroyed when all the tests have been executed..

For this I found testing.postgresql, which can be used to set SQLALCHEMY_DATABASE_URI at test runtime:

from testing.postgresql import Postgresql

\_app = create_app(TestConfig) # TestConfig is defined further down
with Postgresql() as postgresql:
\_app.config['SQLALCHEMY_DATABASE_URI'] = postgresql.url()

We’re not done yet though, as we’re missing an equivalent of TestCase, something to allow writing tests easily.


I made the jump to Flask and PyTest at the same time. I’m not yet sure whether it’s better than unittest.

Instead of classes, in PyTest we make extensive use of the advanced fixtures mechanism. Fixtures here take a more general meaning than usual, it’s anything consumed by a test. The test app will be a fixture, along with the database.

One thing we don’t want though is to generate a database per test, as that would render tests very slow. For this, PyTest allows us to define a scope per fixture. Scopes can be function and session for instance - meaning that the fixture will be used and run per function or session respectively.

However if we scope the database fixture per session, how to do we make sure that the database is clean and at the same state when the different tests are run? At the end of each test, we rollback any changes made to the database during the test.

Putting it all together in code, you want a conftest.py similar to:

from testing.postgresql import Postgresql

from yourapp.app.app import create_app
from yourapp.app.db import db as \_db

class TestConfig(object):
DEBUG = True
ENV = 'test'

def app():
\_app = create_app(TestConfig)
with Postgresql() as postgresql:
\_app.config['SQLALCHEMY_DATABASE_URI'] = postgresql.url()
ctx = \_app.app_context()

        yield _app


def testapp(app):
return app.test_client()

def db(app):
\_db.app = app

    yield _db


@pytest.fixture(scope='function', autouse=True)
def session(db):
connection = db.engine.connect()
transaction = connection.begin()

    options = dict(bind=connection, binds={})
    session_ = db.create_scoped_session(options=options)

    db.session = session_

    yield session_


where yourapp\app\db.py has

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

and create_app is as above.

Now I went a little further and set the session fixture at autouse, but you might not want that. If True it’s automatically used per each test, otherwise you need to invoke it each test function by adding session as an argument. conftest needs to be set as a parent to all the test files that would use it, and PyTest takes care of discoverability.

Now let’s say you have a models.py defining a simple Request table:

class Request(SurrogatePK, Model):
**tablename** = 'request'
request = Column(db.JSON)

Somewhere in your app you save in this table each POST request that is received. You can now test this with:

def test_save_request(testapp):
request_data = # some example request params
resp = testapp.post('/', content_type='application/json', data=request_data)

    # the test should also filter by the request_data content, this is a simplified example
    assert Request.query.filter_by(method='POST').count() == 1

And what is SurrogatePK you ask? This is taken from sloria/cookiecutter-flask where you can take on more good ideas on how to structure your Flask project.

If I find more time, I’ll try and write an extension of this cookiecutter to include the setup above, more helpers and the goals Patrick covered above, meanwhile I hope this helps!