Español

One Fixture, Three Databases

One decorator. Three databases. Zero extra tests. You wrote a solid suite against SQLite because it's fast and needs no setup — but production runs PostgreSQL, and the gap between the two is exactly where the bugs hide. pytest closes that gap with fixture parametrization: mark one fixture with a list of backends, and every test that depends on it silently multiplies.

The mechanism

A pytest fixture accepts a params list. When it has one, pytest calls the fixture once per parameter, and re-runs the whole set of dependent tests each time. The current value arrives through the built-in request fixture as request.param:

import pytest
from sqlalchemy import create_engine

DB_URLS = [
    "sqlite://",                                      # in-memory
    "postgresql+psycopg2://app:secret@localhost/app_test",
    "mysql+pymysql://app:secret@localhost/app_test",
]

@pytest.fixture(params=DB_URLS, ids=["sqlite", "postgres", "mysql"])
def db_engine(request):
    engine = create_engine(request.param)
    yield engine
    engine.dispose()

def test_saves_order(db_engine):
    ...  # written once, runs three times

test_saves_order shows up in the report as three tests — test_saves_order[sqlite], [postgres], [mysql] — with zero changes to the test body. The pytest docs state it directly: params is "an optional list of parameters which will cause multiple invocations of the fixture function and all of the tests using it."

Why the SQLite-only suite lies to you

SQLite is famously permissive. Differences that pass locally and explode in production include:

Any one of these is a test that's green on your laptop and red in prod. Running the same assertions against the real engine catches the whole class at once.

Rules of thumb

• Parametrize the fixture, not the tests. @pytest.mark.parametrize on each test duplicates the backend list everywhere; params on the fixture states it once and applies to the whole suite.
• Name the runs. Pass ids=["sqlite", "postgres", "mysql"] so failures read test_x[postgres], not test_x[db_engine2].
• Widen the scope for slow engines. @pytest.fixture(scope="session", params=...) builds each engine once per run instead of once per test — just make sure tests clean up after themselves (truncate tables in the fixture teardown).
• Select one backend when iterating. pytest -k sqlite runs only the fast in-memory variant while you're in the edit-run loop; CI runs all three.

Power moves

Skip what isn't installed. Wrap a param in pytest.param(...) to attach marks — the same trick the docs show for parametrized fixtures:

@pytest.fixture(params=[
    "sqlite://",
    pytest.param(PG_URL, marks=pytest.mark.skipif(not has_pg(), reason="no postgres")),
])
def db_engine(request):
    ...

Locally the suite degrades gracefully to SQLite; in CI, where the services exist, all backends run.

Stack parametrization. A parametrized fixture composes with @pytest.mark.parametrize on the test: 3 backends x 4 input cases = 12 generated tests, still one function.

Point it at real services. The fixture body doesn't care where the URL comes from — a docker compose service, an env var, or a throwaway container. The pattern stays identical: one fixture, one request.param, N backends.

Resources

• pytest — parametrizing fixtures (how-to guide)
• pytest — parametrize how-to (pytest.param, marks, ids)
• pytest API reference — @pytest.fixture(params=..., ids=...)
• SQLAlchemy — engine configuration and database URLs