[bug] After a database restart, the first database transaction fails

[chennin]

Checklist

• Describe the bug.
• Copy of the output/log.
• Include your .env file.
• Steps to Reproduce
• The exact command that failed. This is what you typed at the command line, including any options.
• FitTrackee version, this is reported by pip show fittrackee
• Operating System and Python Version
• Installed Python packages

Describe the bug

When the database is restarted, such as for an upgrade, and fittrackee is not restarted, the next transaction that tries to query the database fails. I automated my workout uploads, so inconveniently causes me to have to upload them manually.

Proposed fix

Add the pool_pre_ping option. This fixes it in my testing when building the container from source.

Reference: https://flask-sqlalchemy.readthedocs.io/en/stable/config/#timeouts

diff --git a/fittrackee/__init__.py b/fittrackee/__init__.py
index b9f9b8f2a..18d16682b 100644
--- a/fittrackee/__init__.py
+++ b/fittrackee/__init__.py
@@ -60,7 +60,7 @@ class Base(DeclarativeBase):

 db = SQLAlchemy(
     model_class=Base,
-    engine_options={"future": True},
+    engine_options={"future": True, "pool_pre_ping": True},
     session_options={"future": True},
 )

Copy of the output/log

Exception on /api/sports [GET]
Traceback (most recent call last):
  File "/opt/venv/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
    ~~~~~~~~~~~~~~~~~~~~~~~^
        cursor, str_statement, effective_parameters, context
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/opt/venv/lib/python3.13/site-packages/sqlalchemy/engine/default.py", line 952, in do_execute
    cursor.execute(statement, parameters)
    ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.OperationalError: server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/opt/venv/lib/python3.13/site-packages/flask/app.py", line 1511, in wsgi_app
    response = self.full_dispatch_request()
  File "/opt/venv/lib/python3.13/site-packages/flask/app.py", line 919, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/opt/venv/lib/python3.13/site-packages/flask/app.py", line 917, in full_dispatch_request
    rv = self.dispatch_request()
  File "/opt/venv/lib/python3.13/site-packages/flask/app.py", line 902, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)  # type: ignore[no-any-return]
           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^
  File "/usr/src/app/fittrackee/oauth2/resource_protector.py", line 103, in decorated
    return f(auth_user, *args, **kwargs)
  File "/usr/src/app/fittrackee/workouts/sports.py", line 209, in get_sports
    sports = Sport.query.order_by(Sport.id).all()
  File "/opt/venv/lib/python3.13/site-packages/sqlalchemy/orm/query.py", line 2704, in all
    return self._iter().all()  # type: ignore
           ~~~~~~~~~~^^
  File "/opt/venv/lib/python3.13/site-packages/sqlalchemy/orm/query.py", line 2857, in _iter
    result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
                                                  ~~~~~~~~~~~~~~~~~~~~^
        statement,
        ^^^^^^^^^^
        params,
        ^^^^^^^
        execution_options={"_sa_orm_load_options": self.load_options},
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/opt/venv/lib/python3.13/site-packages/sqlalchemy/orm/session.py", line 2351, in execute
    return self._execute_internal(
           ~~~~~~~~~~~~~~~~~~~~~~^
        statement,
        ^^^^^^^^^^
    ...<4 lines>...
        _add_event=_add_event,
        ^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/opt/venv/lib/python3.13/site-packages/sqlalchemy/orm/session.py", line 2249, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
                          ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        self,
        ^^^^^
    ...<4 lines>...
        conn,
        ^^^^^
    )
    ^
  File "/opt/venv/lib/python3.13/site-packages/sqlalchemy/orm/context.py", line 306, in orm_execute_statement
    result = conn.execute(
        statement, params or {}, execution_options=execution_options
    )
  File "/opt/venv/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1419, in execute
    return meth(
        self,
        distilled_parameters,
        execution_options or NO_OPTIONS,
    )
  File "/opt/venv/lib/python3.13/site-packages/sqlalchemy/sql/elements.py", line 527, in _execute_on_connection
    return connection._execute_clauseelement(
           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        self, distilled_params, execution_options
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/opt/venv/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1641, in _execute_clauseelement
    ret = self._execute_context(
        dialect,
    ...<8 lines>...
        cache_hit=cache_hit,
    )
  File "/opt/venv/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ~~~~~~~~~~~~~~~~~~~~~~~~~^
        dialect, context, statement, parameters
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/opt/venv/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        e, str_statement, effective_parameters, cursor, context
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/opt/venv/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 2363, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/opt/venv/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
    ~~~~~~~~~~~~~~~~~~~~~~~^
        cursor, str_statement, effective_parameters, context
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/opt/venv/lib/python3.13/site-packages/sqlalchemy/engine/default.py", line 952, in do_execute
    cursor.execute(statement, parameters)
    ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.

[SQL: SELECT sports.id AS sports_id, sports.label AS sports_label, sports.is_active AS sports_is_active, sports.stopped_speed_threshold AS sports_stopped_speed_threshold, sports.pace_speed_display AS sports_pace_speed_display
FROM sports ORDER BY sports.id]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

Include the .env file

No response

To Reproduce

1. Bring up FitTrackee, the included example is fine
2. Call the API that calls the database, this should succeed: curl -s --no-progress-meter http://127.0.0.1:5000/api/sports
3. Restart just the DB: podman compose restart fittrackee-db
4. Repeat the curl, which returns HTTP 500: curl -s --no-progress-meter http://127.0.0.1:5000/api/sports

FitTrackee Version

1.2.1

Python Version

3.13

FitTrackee installation method

With Docker image

Operating System

GNU/Linux

Python Packages

No response

[chennin]

Additional issue that occurs to me: I already had health checking on /api/ping and it was all green. Maybe /api/ping could be extended to include database connection health as well? For now I've changed my healthcheck to /api/sports, that will also take care of the immediate problem for me.