middleware request transaction handling with deferred callable return for refresh of orm

[jim-obrien-orig]

Is your feature request related to a problem? Please describe.
From sqlalchemy docs, "Keep the lifecycle of the session (and usually the transaction) separate and external" (https://docs.sqlalchemy.org/en/13/orm/session_basics.html)

This is difficult to do at a request level, because create requests need to commit to the database in order to get things like the primary key back to return in json.

In the fastapi documentation on sql databases, the commit happens within the create statement, which makes sense in order to return the user with the id. However, this doesn't allow for request level global transaction handling, which is really what is desired.
For REST operations, everything should rollback on failure of even a nested transaction.

def create_user(db: Session, user: schemas.UserCreate):
    fake_hashed_password = user.password + "notreallyhashed"
    db_user = models.User(email=user.email, hashed_password=fake_hashed_password)
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user

Describe the solution you'd like

It seems like you would want the route that's handling the request to return a function instead of a response. This function would get called after the commit with the db session (post commit) and could refresh objects. That function would handle translating the orm model to the pydantic model, and should return the Response with the pydantic model as the response object. So if the middleware returned the result of the function, it would work.

Something like:

@app.middleware("http")
async def db_session_middleware(request: Request, call_next) -> \
        Union[Response, JSONResponse]:
    response: JSONResponse = JSONResponse(
        status_code=500,
        content={
            "error": "Internal Error",
            "message": "Internal error in request processor"},
    )
    db =  LocalSession()
    request.state.db = db
    try:
         response_func: Callable[Response] = await call_next(request)
         db.commit()
         response = await response_func(db)
    except RequestValidationError as re:
        response.status_code = 400
    except (ProcessorError, Exception) as e:
            request.state.db.rollback()
    finally:
            request.state.db.close()

    return response


**Describe alternatives you've considered**
For now, I'm just handling rollbacks and closes in the middleware, and otherwise handling transactions in a processor. 

[dmontagu]

Is there a reason you can't just create a transaction in the middleware, and (if desired) a nested transaction inside the endpoint?

Having your routes return functions instead of responses is a major deviation from what starlette expects, and is likely to cause fragility. In particular, the suggested implementation would force you to return a response_func from all endpoints, even if they have no interaction with the sqlalchemy session.

[jim-obrien-orig]

That's what I ended up doing. Thanks.

[tiangolo]

Thanks for the help @dmontagu ! 🚀

And thanks @jim-obrien-orig for reporting back and closing the issue 🎉