InvalidRequestError: A transaction is already begun on this Session.

[michaelxswe]

I have a simple FastAPI app that connects to postgres using sqlalchemy + psycopg2

from datetime import datetime
from pydantic import BaseModel
from sqlalchemy import create_engine, select
from sqlalchemy.sql import func
from sqlalchemy.dialects.postgresql import INTEGER, TIMESTAMP, VARCHAR
from sqlalchemy.orm import DeclarativeBase, Mapped, MappedAsDataclass, mapped_column, sessionmaker, Session
from fastapi import Depends, FastAPI

class Base(DeclarativeBase, MappedAsDataclass):
    pass

class AppUserOrm(Base):
    __tablename__ = "app_user"

    id: Mapped[int] = mapped_column(INTEGER, primary_key=True, init=False, autoincrement=True)
    username: Mapped[str] = mapped_column(VARCHAR(length=25), unique=True)
    password: Mapped[str] = mapped_column(VARCHAR(length=25))
    created_at: Mapped[datetime] = mapped_column(TIMESTAMP(timezone=True), server_default=func.now(), init=False)

class AppUserCreateModel(BaseModel):
    username: str
    password: str

app = FastAPI()
engine = create_engine(url = "postgresql+psycopg2://postgres:0000@localhost:5432/dev")
SessionLocal = sessionmaker(bind=engine)
Base.metadata.create_all(bind=engine)

def get_database_session():
    try:
        session: Session = SessionLocal()
        yield session
    finally:
        session.close()

@app.post("/api/v1/app-users", status_code=201)
def add_add_user(data: AppUserCreateModel, session: Session = Depends(get_database_session)):
    app_user_orm = AppUserOrm(**data.model_dump())
    session.add(app_user_orm)
    session.flush()
    session.commit()
    return app_user_orm


@app.delete("/api/v1/app-users/{id}", status_code=200)
async def delete_app_user_by_id(id: int, session: Session = Depends(get_database_session)):
    with session.begin():
        query = select(AppUserOrm).where(AppUserOrm.id == id)
        result = session.execute(query)
        app_user_orm = result.scalar()
        session.delete(app_user_orm)

i'm trying to test the application and here is my test case

from fastapi.testclient import TestClient
from test import app, engine, SessionLocal, get_database_session
from pytest import fixture
from sqlalchemy.orm import Session


@fixture(scope="function")
def db_session():
    #revert all commits made during testing.

    connection = engine.connect()
    transaction = connection.begin()
    session = SessionLocal(bind=connection)

    def _get_database_session():
        try:
            yield session
        finally:
            pass

    app.dependency_overrides[get_database_session] = _get_database_session

    yield session
    session.close()
    transaction.rollback()
    connection.close()


@fixture(scope="function")
def client(db_session: Session):
    with TestClient(app=app) as client:
        yield client

@fixture(scope="function")
def create_app_user(client: TestClient):
    data = {"username": "mike", "password": "123"}
    res = client.post("/api/v1/app-users", json=data)
    assert res.status_code == 201
    yield res.json()


def test_delete_app_user(client: TestClient, create_app_user):
    user = create_app_user
    id = user["id"]

    res = client.delete(f"/api/v1/app-users/{id}")       <- Where error occurs
    assert res.status_code == 200

Why am I getting a transaction is already begun error?

The error:

E           sqlalchemy.exc.InvalidRequestError: A transaction is already begun on this Session.

../.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py:1883: InvalidRequestError

[zzzeek]

the full stack trace is quite important to give context but we would assume your delete_app_by_user_id function is where the trace originates, since that's where there is an explicit call to session.begin(). if the session delivered by the Session = Depends(get_database_session) construct (this is a fastapi thing that we dont have information on) was already being used in a different method , then it would already be ongoing in a transaction and you can't call begin() on a session in that state.

well looking at your code if I had to guess I would say that since you have a single session used for everything, you are first calling into client.post("/api/v1/app-users", json=data) which is going to use that Session, then calling yield res.json() which perhaps is calling upon model objects again to lazy load some state (since that previous commit would have expired everything), which will start a new transaction. then you call delete where you have that same session that's calling begin(), which errors because your client.post() / json() sequence has left it in a transaction already.

[zzzeek]

short answer is while you can have that connection / transaction ready for the entire scope of the test, I would not hardcode things to use just one Session the whole time. keep the creation of new sessions for each get_database_session() call intact, just bind to that connection instead of the engine.

[michaelxswe]

@fixture(scope="function")
def db_session():
    #revert all commits made during testing.

    connection = engine.connect()
    transaction = connection.begin()
    session = SessionLocal(bind=connection)

    def _get_database_session():
        try:
            yield session
        finally:
            pass

    app.dependency_overrides[get_database_session] = _get_database_session

    yield session
    session.close()
    transaction.rollback()
    connection.close()

The reason why I'm using 1 session thorough and overriding using dependency injection is so that later on I can revert all commits that I have been made during testing(some api calls will commit changes). Is there a better of doing it? I would like to achieve something like this

@fixture(scope="function")
def db_session():
    #transaction being
    yield
    #revert all commits

@fixture(scope="function")
def create_app_user(db_session):
    #db call and commit using new session

[zzzeek]

your harness has a single Connection with one Transaction at the top of the whole thing, that's your actual database transaction. each of your @app.post methods would appear to be designed to use a brand new Session each. so you have to maintain that pattern.