pytest-flask-sqlalchemy

You can install the plugin via PyPi using pip. Once installed, pytest will automatically detect the plugin during test collection.

To install the development version, clone the repository and install the current directory using pip.

# From PyPi
pip install pytest-flask-sqlalchemy

# Development version
git clone git@github.com:jeancochrane/pytest-flask-sqlalchemy.git
cd pytest-flask-sqlalchemy
pip install .

The pytest-flask-sqlalchemy plugin requires a fixture named _db to be defined in your root conftest.py file. This fixture must provide access to a valid SQLAlchemy Session object (for example, via a Flask-SQLAlchemy SQLAlchemy instance). The plugin uses this _db fixture to manage database access and create the nested transactions used for testing.

You can define _db by creating a new fixture or by simply aliasing an existing database fixture.

@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

To set up a development environment for this plugin, install it in editable mode including the tests extra dependencies.

pip install -e .[tests]

To avoid including a specific fixture in every single test signature, you can use a pytest autouse fixture. By creating a fixture that depends on db_session and setting autouse=True, you ensure that db_session is automatically injected and used for every test in your suite, wrapping them in transactions without explicit imports in your test functions.

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

The test suite requires a database connection string provided via the TEST_DATABASE_URL environment variable. Note that the database referenced by this string will be created automatically during test setup, so the database itself does not need to exist beforehand.

export TEST_DATABASE_URL=<db_connection_string>

To ensure that any method in your codebase (even those that call .commit() internally) runs inside a test transaction and rolls back, you must configure the plugin to mock your application's database connections.

Add the following configuration to your setup.cfg file, pointing to the import paths of your session and engine objects:

# In setup.cfg

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

The db_engine fixture provides a way to run transactions using an API that copies SQLAlchemy's Engine object. Like db_session, any changes made via this fixture will be rolled back after the test completes.

def test_a_transaction_using_engine(db_engine):
    with db_engine.begin() as conn:
        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' 

The db_engine fixture provides a MagicMock object with a spec matching SQLAlchemy's Engine. It is used to perform direct updates or raw SQL execution within a transaction that rolls back at the end of the test. It also activates configured mocks.

Exposed methods:

• db_engine.begin(): Begins a new nested transaction.
• db_engine.execute(): Executes a raw SQL query.
• db_engine.raw_connection(): Returns a raw DBAPI connection.

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''')

# The changes above will not persist after the test exits
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' 

The db_session fixture allows you to perform direct database updates using an API identical to SQLAlchemy's scoped_session. When included in a test function's arguments, the test is wrapped in a transaction that is rolled back upon exit. This fixture also activates any mocks configured via mocked-engines, mocked-sessions, or mocked-sessionmakers for the duration 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()

# The changes above will not persist after the test exits
def test_transaction_doesnt_persist(db_session):
   row = db_session.query(Table).get(1) 
   assert row.name != 'testing'