pytest-flask-sqlalchemy

A pytest plugin providing fixtures for running tests intransactions using Flask-SQLAlchemy.

Contents

• Motivation
• Quick examples
• Usage
  • Installation
    • From PyPi
    • Development version
    • Supported backends
  • Configuration
    • Conftest setup
    • Test configuration
      • mocked-engines
      • mocked-sessions
      • mocked-sessionmakers
    • Writing transactional tests
  • Fixtures
    • db_session
    • db_engine
  • Enabling transactions without fixtures
• Development
  • Running the tests
  • Acknowledgements
  • Copyright

Motivation

Inspired by Django's built-in support for transactionaltests, this pluginseeks to provide comprehensive, easy-to-use Pytest fixtures for wrapping tests indatabase transactions for Flask-SQLAlchemyapps. The goal is to make testing stateful Flask-SQLAlchemy applications easier byproviding fixtures that permit the developer to make arbitrary database updateswith the confidence that any changes made during a test will roll back once the test exits.

Quick examples

Use the db_session fixture to make database updates that won't persist beyondthe body of the test:

def test_a_transaction(db_session):
   row = db_session.query(Table).get(1) 
   row.name = 'testing'

   db_session.add(row)
   db_session.commit()

def test_transaction_doesnt_persist(db_session):
   row = db_session.query(Table).get(1) 
   assert row.name != 'testing'

The db_engine fixture works the same way, but copies the API ofSQLAlchemy's Engineobject:

def test_a_transaction_using_engine(db_engine):
    with db_engine.begin() as conn:
        row = conn.execute('''UPDATE table SET name = 'testing' WHERE id = 1''')

def test_transaction_doesnt_persist(db_engine):
    row_name = db_engine.execute('''SELECT name FROM table WHERE id = 1''').fetchone()[0]
    assert row_name != 'testing'

Use configuration properties tomock database connections in an app and enforce nested transactions,allowing any method from the codebase to run inside a test with the assurancethat any database changes made will be rolled back at the end of the test:

# In setup.cfg

[tool:pytest]
mocked-sessions=database.db.session
mocked-engines=database.engine

# In database.py

db = flask_sqlalchemy.SQLAlchemy()
engine = sqlalchemy.create_engine('DATABASE_URI')

# In models.py

class Table(db.Model):
    __tablename__ = 'table'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80))

    def set_name(new_name):
        self.name = new_name
        db.session.add(self)
        db.session.commit()

# In tests/test_set_name.py

def test_set_name(db_session):
    row = db_session.query(Table).get(1)
    row.set_name('testing')
    assert row.name == 'testing'

def test_transaction_doesnt_persist(db_session):
   row = db_session.query(Table).get(1) 
   assert row.name != 'testing'

Usage

Installation

From PyPi

Install using pip:

pip install pytest-flask-sqlalchemy

Once installed, pytest will detect the plugin automatically during test collection.For basic background on using third-party plugins with pytest, see the pytestdocumentation.

Development version

Clone the repo from GitHub and switch into the new directory:

git clone git@github.com:jeancochrane/pytest-flask-sqlalchemy.git
cd pytest-flask-sqlalchemy

You can install using pip:

pip install .

Supported backends

So far, pytest-flask-sqlalchemy has been most extensively tested againstPostgreSQL 9.6. It should theoretically work with any backend that is supportedby SQLAlchemy, but Postgres is the only backend that is currently tested by thetest suite.

Official support for SQLite and MySQL is planned for a futurerelease.In the meantime, if you're using one of those backends and you run in toproblems, we would greatly appreciate your help! Open anissue ifsomething isn't working as you expect.

Configuration

Conftest setup

This plugin assumes that a fixture called _db has beendefined in the root conftest file for your tests. The _db fixture shouldexpose access to a valid SQLAlchemy Session object that can interact with your database,for example via the SQLAlchemy initialization classthat configures Flask-SQLAlchemy.

The fixtures in this plugin depend on this _db fixture to access yourdatabase and create nested transactions to run tests in. You must definethis fixture in your conftest.py file for the plugin to work.

An example setup that will produce a valid _db fixture could look like this(this example comes from the test setup for this repo):

@pytest.fixture(scope='session')
def database(request):
    '''
    Create a Postgres database for the tests, and drop it when the tests are done.
    '''
    pg_host = DB_OPTS.get("host")
    pg_port = DB_OPTS.get("port")
    pg_user = DB_OPTS.get("username")
    pg_db = DB_OPTS["database"]

    init_postgresql_database(pg_user, pg_host, pg_port, pg_db)

    @request.addfinalizer
    def drop_database():
        drop_postgresql_database(pg_user, pg_host, pg_port, pg_db, 9.6)


@pytest.fixture(scope='session')
def app(database):
    '''
    Create a Flask app context for the tests.
    '''
    app = Flask(__name__)

    app.config['SQLALCHEMY_DATABASE_URI'] = DB_CONN

    return app


@pytest.fixture(scope='session')
def _db(app):
    '''
    Provide the transactional fixtures with access to the database via a Flask-SQLAlchemy
    database connection.
    '''
    db = SQLAlchemy(app=app)

    return db

Alternatively, if you already have a fixture that sets up database access foryour tests, you can define _db to return that fixture directly:

@pytest.fixture(scope='session')
def database():
    # Set up all your database stuff here
    # ...
    return db

@pytest.fixture(scope='session')
def _db(database):
    return database

Test configuration

This plugin allows you to configure a few different properties in asetup.cfg test configuration file in order to handle the specific database connection needsof your app. For basic background on setting up pytest configuration files, seethe pytest docs.

All three configuration properties (mocked-engines,mocked-sessions, and mocked-sessionmakers)work by patchingone or more specified objects during a test, replacing them with equivalent objects whosedatabase interactions will run inside of a transaction and ultimately berolled back when the test exits. Using these patches, you can call methods fromyour codebase that alter database state with the knowledge that no changes will persistbeyond the body of the test.

The configured patches are only applied in tests where a transactional fixture(either db_session or db_engine) is includedin the test function arguments.

mocked-engines

The mocked-engines property directs the plugin to patchobjects in your codebase, typically SQLAlchemy Engineinstances, replacing them with the db_engine fixture such thatany database updates performed by the objects get rolled back at the end ofthe test.

The value for this property should be formatted as a whitespace-separated listof standard Python import paths, like database.engine. This property is optional.

Example:

# In database.py

engine = sqlalchemy.create_engine(DATABASE_URI)

# In setup.cfg

[tool:pytest]
mocked-engines=database.engine

To patch multiple objects at once, separate the paths with a whitespace:

# In setup.cfg

[tool:pytest]
mocked-engines=database.engine database.second_engine

mocked-sessions

The mocked-sessions property directs the plugin to patchobjects in your codebase, typically SQLAlchemy Sessioninstances, replacing them with the db_session fixture such thatany database updates performed by the objects get rolled back at the end ofthe test.

The value for this property should be formatted as a whitespace-separated listof standard Python import paths, like database.db.session. This property is optional.

Example:

# In database.py

db = SQLAlchemy()

# In setup.cfg

[tool:pytest]
mocked-sessions=database.db.session

To patch multiple objects at once, separate the paths with a whitespace:

# In setup.cfg

[tool:pytest]
mocked-sessions=database.db.session database.second_db.session

mocked-sessionmakers

The mocked-sessionmakers property directs the plugin to patchobjects in your codebase, typically instances of SQLAlchemy's sessionmakerfactory,replacing them with a mocked class that will return the transactionaldb_session fixture. This can be useful if you havepre-configured instances of sessionmaker objects that you import in the codeto spin up sessions on the fly.

The value for this property should be formatted as a whitespace-separated listof standard Python import paths, like database.WorkerSessionmaker. This property is optional.

Example:

# In database.py

WorkerSessionmaker = sessionmaker()

[tool:pytest]
mocked-sessionmakers=database.WorkerSessionmaker

To patch multiple objects at once, separate the paths with a whitespace.

[tool:pytest]
mocked-sessionmakers=database.WorkerSessionmaker database.SecondWorkerSessionmaker

Writing transactional tests

Once you have your conftest file set up and you've overrided thenecessary connectables in your test configuration, you're readyto write some transactional tests. Simply import one of the module's transactionalfixtures in your test signature, and the test will be wrapped in a transaction.

Note that by default, tests are only wrapped in transactions if they import one ofthe transactional fixtures provided by this module. Tests that do notimport the fixture will interact with your database without opening a transaction:

# This test will be wrapped in a transaction.
def transactional_test(db_session):
    ...
    
# This test **will not** be wrapped in a transaction, since it does not import a
# transactional fixture.
def non_transactional_test():
    ...

The fixtures provide a way for you to control which tests require transactions andwhich don't. This is often useful, since avoiding transaction setup can speed uptests that don't interact with your database.

For more information about the transactional fixtures provided by this module, read onto the fixtures section. For guidance on how to automatically enabletransactions without having to specify fixtures, see the section on enabling transactionswithout fixtures.

Fixtures

This plugin provides two fixtures for performing database updates inside nestedtransactions that get rolled back at the end of a test: db_session anddb_engine. The fixtures provide similar functionality, butwith different APIs.

db_session

The db_session fixture allows you to perform direct updates that will berolled back when the test exits. It exposes the same API as SQLAlchemy'sscoped_session object.

Including this fixture as a function argument of a test will activate any mocks that are definedby the configuration properties mocked-engines, mocked-sessions,or mocked-sessionmakers in the test configuration file forthe duration of that test.

Example:

def test_a_transaction(db_session):
   row = db_session.query(Table).get(1) 
   row.name = 'testing'

   db_session.add(row)
   db_session.commit()

def test_transaction_doesnt_persist(db_session):
   row = db_session.query(Table).get(1) 
   assert row.name != 'testing'

db_engine

Like db_session, the db_engine fixture allows you to perform direct updatesagainst the test database that will be rolled back when the test exits. It isan instance of Python's built-in MagicMockclass, with a spec set to match the API of SQLAlchemy'sEngine object.

Only a few Engine methods are exposed on this fixture:

• db_engine.begin: begin a new nested transaction (API docs)
• db_engine.execute: execute a raw SQL query (API docs)
• db_engine.raw_connection: return a raw DBAPI connection (API docs)

Since db_engine is an instance of MagicMock with an Engine spec, othermethods of the Engine API can be called, but they will not perform any usefulwork.

Including this fixture as a function argument of a test will activate any mocks that are definedby the configuration properties mocked-engines, mocked-sessions,or mocked-sessionmakers in the test configuration file forthe duration of that test.

Example:

def test_a_transaction_using_engine(db_engine):
    with db_engine.begin() as conn:
        row = conn.execute('''UPDATE table SET name = 'testing' WHERE id = 1''')

def test_transaction_doesnt_persist(db_engine):
    row_name = db_engine.execute('''SELECT name FROM table WHERE id = 1''').fetchone()[0]
    assert row_name != 'testing'

Enabling transactions without fixtures

If you know you want to make all of your tests transactional, it can be annoying to haveto specify one of the fixtures in every test signature.

The best way to automatically enable transactions without having to include an extra fixturein every test is to wire up anautouse fixturefor your test suite. This can be as simple as:

# Automatically enable transactions for all tests, without importing any extra fixtures.
@pytest.fixture(autouse=True)
def enable_transactional_tests(db_session):
    pass

In this configuration, the enable_transactional_tests fixture will be automatically used inall tests, meaning that db_session will also be used. This way, all tests will be wrappedin transactions without having to explicitly require either db_session or enable_transactional_tests.

Development

Running the tests

To run the tests, start by installing a development version of the plugin thatincludes test dependencies:

pip install -e .[tests]

Next, export a database connection stringthat the tests can use (the database referenced by the string will be createdduring test setup, so it does not need to exist):

export TEST_DATABASE_URL=<db_connection_string>

Finally, run the tests using pytest:

pytest

Acknowledgements

This plugin was initially developed for testingDedupe.io, a web app for record linkage and entityresolution using machine learning. Dedupe.io is built and maintainedby DataMade.

The code is greatly indebted to Alex Michael,whose blog post "Delightful testing with pytest andFlask-SQLAlchemy" helpedestablish the basic approach on which this plugin builds.

Many thanks to Igor Ghisi, who donated the PyPipackage name. Igor had been working on a similar plugin and proposed combiningefforts. Thanks to Igor, the plugin name is much stronger.

Copyright

Copyright (c) 2019 Jean Cochrane and DataMade. Released under the MIT License.

Third-party copyright in this distribution is noted where applicable.