Transaction inside a transaction (such as for test suites)

[wwarne]

Hello!
I'm very interested in speeding up a test suite. I worked with Django before and I loved their approach to it.
Basically, each test has its own transaction which is rolled back after the test is finished.

I've tried to do it with SqlAlchemy and there is a recipe for it. The best thing is that if your database supports the "SAVEPOINT" feature you can use transactions inside of your test and run .commit() or .rollback() methods and they are not going to affect your external transaction.

https://docs.sqlalchemy.org/en/14/orm/session_transaction.html#joining-a-session-into-an-external-transaction-such-as-for-test-suites
(and here is a discussion on how to do it with async sqlalchemy - sqlalchemy/sqlalchemy#5811 - solution at the end).

These feature relies on events (@event.listens_for after_transaction_end) and starting a nested transaction.

If I understood correctly I can't repeat such a setup with Gino right now but probably it will be possible in Gino 1.4/2 version?

Maybe someone could give me a piece of advice.

Right now I ended up with this approach.

fixture migrated_db_for_tests (with session scope) creates a clean database 
(using  create_database from sqlalchemy_utils), 
applies alembic migrations,
initialize Gino - (db.set_bind(db_for_tests_url...)), 
makes sure database is deleted after test run)

And each test uses the next fixture

@pytest.fixture()
async def db(migrated_db_for_tests):
    yield
    sorted_model_cls_list = 
        [  # Sorted from child to parent to prevent FK constraint errors
            orm.ModelOne,
            orm.ModelTwo,
            orm.ModelThree,
        ]
    # cleaning every table in db after each test (it's faster than recreate the whole db)
    for model in sorted_model_cls_list:
        await model.delete.gino.status()