My question for this post: Is using and relying on before_request and after_request to handle opening and closing database sessions ok or just a mess?
Background
I have a Python Flask Website, a different Flask app that is the API, and a service Python app that the API can call when needed that connect to the same MySQL database. They all use SQLAlchemy to communicate with the same MySQL database.
Every morning, I find a similar Broken Pipe Error which prevents the first user for the day from logging in right away. They will need to try a few times to rollback and close the session. This is the problem I am ultimately trying to resolve.
My approach has been, instead of trying to find the loose end database session, I thought I could cut off an expiring database session by using the before_request decorator to instantiate a db session. Then after_request decorator to close the session after each request.
This doesn’t seem to be working. I have not prevented Broken Pipe errors.
Should I just create a database session when I need it and close it? Then if I find Broken Pipe error try to find the source of the problem.
Diagram of my overall structure simplified
full size image here
If someone has a GitHub repo that follows some best practices with Flask and SQLAlchemay (using MySQL and no Flask-SQLAlchemy) that would also be helpful.
Reserach
I have tried asking ChatGPT but this is too general of a question and not getting helpful answers. Any advice greatly appreciated.
Here are two questions that get close to what I am asking:
- how to use after_request in flask to close database connection and python?
- Best practices for persistent database connections in Python when using Flask
It seems in (#1) someone else is doing what I am doing. Is this a good way to handle database sessions? Would creating a database session and closing it in the routes each time be uncommon and a lot more work?