[Question] scoped_session versus Session sqlalchemy about full-stack-fastapi-postgresql

[xinbinhuang]

Just want to drop a message here to provide some extra context and remind myself and people who may come across this in the future:

• The discussion here is based on the previous implementation of getting the database session using middleware which is different from the current method of using yield.

Ref:

1. codebase time travel back to 2019

https://github.com/tiangolo/full-stack-fastapi-postgresql/blob/44d8a4358bc3f8c608bfad2d546aaeca074d2990/%7B%7Bcookiecutter.project_slug%7D%7D/backend/app/app/main.py#L31-L36

2. Current relevant documentation on db session middleware: https://fastapi.tiangolo.com/tutorial/sql-databases/#alternative-db-session-with-middleware

[tiangolo]

Great responses as always @dmontagu, thanks for all your help here! 👏🍰🚀

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

[jim-obrien-orig]

Just wanted to update that I ran parallel tests on this (curls and gnu parallel). I scattered get all on endpoints with posts - pretty heavy json and writing to a wide table.
All was good.

[jim-obrien-orig]

Great. That makes sense. So the approach in this app seems like it will work. I may do some pen testing on it to verify, and will let you know if anything comes up. Thanks for your help and answers.

[dmontagu]

I don't believe using a scoped_session would play nicely with session middleware, since the middleware will be executed in a different thread than the endpoint callable (since it will be run in the threadpool, as you noted).

---

I was also worried about the threadsafety issue, and I actually had a lengthy discussion about this with @tiangolo on gitter (a couple months ago, I believe), and in the end I became convinced there isn't actually any risk here:

• A new session is created for each request, and is only accessed on the main (middleware) thread and in the run_in_threadpool thread
• the main (middleware) thread does NOT interact with the session while the threadpool call is being executed; similarly, the run_in_threadpool thread does NOT interact with the session before or after the run_in_threadpool call is finished
• So there is no need for any sort of extra thread-safety synchronization
  • (alternatively, you could think of the endpoint execution as providing the synchronization)

If anyone reads this and thinks it is wrong, please correct me!

[jim-obrien-orig]

Ok - that's good to know. Follow up question is about the caching of depends. In this implementation, it uses Depends(get_db) without use_cache=False. Does that mean you could get a different Session than the one created for the request. Or is the caching session level?

[dmontagu]

If use_cache=True (the default), the callable will be executed at most once per request.

However, this only applies to Depends-based dependencies. Since the session is usually created in middleware, you just need to make sure you aren't creating a separate session in your Depends calls.

If you create the session in middleware, and just access it in the Depends calls, you shouldn't have any problems with multiple sessions being created.

Does that answer your question?

[dmontagu]

@jim-obrien-orig if you find any issues in your testing, please do share!