Issue with 'Avoid Writing Data' test documentation

[Jackevansevo]

I have a simple app, setup according to the documentation

from __future__ import annotations

from flask import Flask
from flask_sqlalchemy_lite import SQLAlchemy

from datetime import datetime
from datetime import UTC
from sqlalchemy import ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship

db = SQLAlchemy()


class Model(DeclarativeBase):
    pass


class User(Model):
    __tablename__ = "user"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    posts: Mapped[list[Post]] = relationship(back_populates="author")


class Post(Model):
    __tablename__ = "post"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    body: Mapped[str]
    author_id: Mapped[int] = mapped_column(ForeignKey(User.id))
    author: Mapped[User] = relationship(back_populates="posts")
    created_at: Mapped[datetime] = mapped_column(default=lambda: datetime.now(UTC))


def create_app(test_config=None):
    app = Flask(__name__)
    app.config |= {"SQLALCHEMY_ENGINES": {"default": "sqlite:///default.sqlite"}}

    if test_config is None:
        app.config.from_prefixed_env()
    else:
        app.testing = True
        app.config |= test_config

    db.init_app(app)

    return app

I have configured my pytest fixtures following the guidance on this page: https://flask-sqlalchemy-lite.readthedocs.io/en/latest/testing/

import pytest
from app import create_app

from sqlalchemy_utils import create_database, drop_database
from app import db, Model


@pytest.fixture
def app_ctx(app):
    with app.app_context() as ctx:
        yield ctx


@pytest.fixture(scope="session", autouse=True)
def _manage_test_database():
    app = create_app(
        {"SQLALCHEMY_ENGINES": {"default": "postgresql+psycopg:///testdb"}}
    )

    with app.app_context():
        engines = db.engines

    for engine in engines.values():
        create_database(engine.url)

    Model.metadata.create_all(engines["default"])

    yield

    for engine in engines.values():
        drop_database(engine.url)


@pytest.fixture
def app():
    app = create_app(
        {"SQLALCHEMY_ENGINES": {"default": "postgresql+psycopg:///testdb"}}
    )

    with app.app_context():
        engines = db.engines

    cleanup = []

    for key, engine in engines.items():
        connection = engine.connect()
        transaction = connection.begin()
        engines[key] = connection
        cleanup.append((key, engine, connection, transaction))

    yield app

    for key, engine, connection, transaction in cleanup:
        transaction.rollback()
        connection.close()
        engines[key] = engine

Following the guidance under Avoid Writing Data

If code in a test writes data to the database, and another test reads data from the database, one test running before another might affect what the other test sees. This isn’t good, each test should be isolated and have no lasting effects.

Each engine in db.engines can be patched to represent a connection with a transaction instead of a pool. Then all operations will occur inside the transaction and be discarded at the end, without writing anything permanently.

I'd expect the DB state to be rolled back after each test, and no DB state/records to persist/leak between tests.

In practice, I'm instead seeing test_b will always fail, because the user from test_a hasn't been cleaned up.

import pytest

from sqlalchemy import func, select
from app import User, db


@pytest.mark.usefixtures("app_ctx")
def test_a():
    user = User(name="test")
    db.session.add(user)
    db.session.commit()


@pytest.mark.usefixtures("app_ctx")
def test_b():
    assert db.session.scalar(select(func.count(User.id))) == 0