Writing Python Tests in a Database Transaction

If you have python tests that want to exercise writing and reading to a database, you may find it helpful to wrap each test in a database transaction so any records created are automatically deleted.

You can elegantly accomplish this using SQLAlchemy and Python decorators, with code like this:

def run_test_in_database_transaction(func):
    @wraps(func)
    def inner_function(*args, **kwargs):
        db_session.begin(subtransactions=True)
        try:
            await func(*args, **kwargs)
        finally:
            db_session.rollback()
            db_session.close()
    return inner_function

Then in your test you can write:

@run_test_in_database_transaction
def test_reading_users():
    # create some users
    # read some users

No matter what database records, in this case, users you create, your records will be deleted at the end of the test because they happen within a transaction that is always rolled back.

To learn more about transactions in our favorite database, PostgreSQL, please read these docs.