pytest: rollback between tests using SQLAlchemy and FastAPI

I have a FastAPI application where I have several tests written with pytest.

Two particular tests are causing me issues. test_a calls a post endpoint that creates a new entry into the database. test_b gets these entries. test_b is including the created entry from test_a. This is not desired behaviour.

When I run the test individually (using VS Code's testing tab) it runs fine. However when running all the tests together and test_a runs before test_b, test_b fails.

My conftest.py looks like this:

import pytest
from fastapi.testclient import TestClient
from sqlmodel import Session, SQLModel, create_engine

from application.core.config import get_database_uri
from application.core.db import get_db
from application.main import app


@pytest.fixture(scope="module", name="engine")
def fixture_engine():
    engine = create_engine(
        get_database_uri(uri="postgresql://user:secret@localhost:5432/mydb")
    )
    SQLModel.metadata.create_all(bind=engine)
    yield engine
    SQLModel.metadata.drop_all(bind=engine)


@pytest.fixture(scope="function", name="db")
def fixture_db(engine):
    connection = engine.connect()
    transaction = connection.begin()
    session = Session(bind=connection)
    yield session
    session.close()
    transaction.rollback()
    connection.close()


@pytest.fixture(scope="function", name="client")
def fixture_client(db):
    app.dependency_overrides[get_db] = lambda: db
    with TestClient(app) as client:
        yield client

The file containing test_a and test_b also has a module-scoped pytest fixture that seeds the data using the engine fixture:

@pytest.fixture(scope="module", autouse=True)
def seed(engine):
    connection = test_db_engine.connect()
    seed_data_session = Session(bind=connection)
    seed_data(seed_data_session)
    yield
    seed_data_session.rollback()

All tests use the client fixture, like so:

def test_a(client):
    ...

SQLAlchemy version is 1.4.41, FastAPI version is 0.78.0, and pytest version is 7.1.3.

My Observations

It seems the reason tests run fine on their own is due to SQLModel.metadata.drop_all(bind=engine) being called at the end of testing. However I would like to avoid having to do this, and instead only use rollback between tests.