As part of the CI pipeline of our tool automating load tests with live traffic, we fire up some integration tests to make sure our main use cases are covered.
Now, here’s the deal: our tool dances with MariaDB, and these integration tests go all the way through the DB. The catch? Our CI environment wasn’t cool with MariaDB as it hadn’t discovered the joys of a containerized environment yet. So, we had to make do with SQLite for our tests. Why? Because it plays nice with most of our schema definitions and queries and could be used on the CI nodes.
It is also quick to set up, fast to run and everything was alright for a while. But then, enter the troublemaker: testing concurrent code. Every now and then, the tests would trip over themselves, blaming it on a pesky lock in the database.
This sure wouldn’t happen on every CI run, but just enough to sprinkle a bit of chaos into our otherwise serene dev experience.

Our DB test setup was a piece of cake. A temporary DB file was summoned into existence, migrated, and made available for the tests. Easy-peasy. But here’s the twist: this DB was shared for the entire test session.
So no data isolation between tests (problematic when checking a test result using the DB state) and conditions favoring sqlite locks failures.

@pytest.fixture()
def use_db(tmp_path):
    if not tmp_path.is_dir():
        os.makedirs(tmp_path, exist_ok=False)
    sqlite_file = f"{tmp_path}/test.db"
    os.environ["DB_URI"] = f"sqlite:///{sqlite_file}"
    alembic_config = Config(f"{ROOT_DIR}/alembic.ini")
    alembic_config.set_main_option("script_location", f"{ROOT_DIR}/alembic")
    command.upgrade(alembic_config, "head")
    yield

As our project’s contributors multiplied, flaky tests frequency obviously grew, making it hard to figure out if a test hiccup was legit or just playing hard to get. So, I put on my problem-solving hat and devised a few potential fixes:

• Dedicated Preproduction DB Drama - I pondered the idea of a special DB for these tests. The cool part? Tests would also deal with MariaDB and avoid sqlite db locks. The not-so-cool part? It wouldn’t exactly solve the data isolation issue; in fact, it might make things messier in CI. Picture this: multiple reviews, CI-ing simultaneously, all eyeing the same DB and sharing data like it’s a potluck. If a test or migration hit a snag, it could put the brakes on other reviews until our preproduction DB got a manual makeover.
• DB Per Test Extravaganza - Another brainwave: creating a new DB for each test, riding on the coattails of our fixture’s basics. Sounds neat, right? Well, with a growing tests suite and DB migrations doing their own thing, there was a risk that CI runs could turn into marathons with every new integration test.
• Migrate Once, Test Many - I took a cue from the previous idea but added a twist: migrate once per session and spawn a fresh DB for each test by copying the already upgraded DB file. There were other variations but in the end, I went with the third option which looked like the following

@pytest.fixture(scope="session", name="_init_db")
def fixture_init_db(tmpdir_factory):
    tmp_path = tmpdir_factory.mktemp("sqlite")
    sqlite_file = f"{tmp_path}/test.db"
    db_uri = f"sqlite:///{sqlite_file}"
    runner = CliRunner(charset="utf-8")
    with mock.patch("loadbrok.settings.DB_URI", db_uri):
        runner.invoke(
            main,
            args=[
                "db",
                "upgrade",
            ],
            color=True,
        )
    yield sqlite_file

@pytest.fixture(name="use_db")
def fixture_use_db(_init_db, tmpdir_factory):
    tmp_path = tmpdir_factory.mktemp("sqlite")
    tmp_db = shutil.copy2(_init_db, tmp_path / "test.db")
    db_uri = f"sqlite:///{tmp_db}"
    with mock.patch("loadbrok.settings.DB_URI", db_uri):
        engine.cache_clear()
        yield db_uri

The era of flaky tests haunting us was officially over with this. No more unraveling mysteries of whether a hiccup was a legit issue or just tests being drama queens. Goodbye to the worries of database locks. With this, we made sure that each test got their own, dedicated space as “isolated by design.” And guess what? The impact on test duration was practically negligible and no need to break a sweat tweaking tests. No changes were needed as the same fixture was used and just refactored.
Looking ahead, even running tests concurrently in the future would be possible as this solution also laid out the red carpet.

Last modified on 2023-12-25