Repository: couchers-org/couchers
Issue: postgres server closed the connection unexpectedly after upgrade

After running an upgrade, I get this error message in the prod container on the first api call after recreating containers:


```
2021-02-15 14:30:27.211: 10: Got request: /org.couchers.auth.Auth/Login
2021-02-15 14:30:27.211: 10: Attempting login for request.user='aapeli'
2021-02-15 14:30:27.212: 10: Field matched to type username
/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/dynamic.py:44: SAWarning: On relationship Node.contained_users, 'dynamic' loaders cannot be used with many-to-one/one-to-one relationships and/or uselist=
False.  This warning will be an exception in a future release.
  util.warn(
2021-02-15 14:30:27.411: 10: (psycopg2.OperationalError) server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

[SQL: SELECT users.id AS users_id, users.username AS users_username, users.email AS users_email, users.hashed_password AS users_hashed_password, users.phone AS users_phone, users.phone_status AS users_phone_s
tatus, ST_AsEWKB(users.geom) AS users_geom, users.geom_radius AS users_geom_radius, users.city AS users_city, users.joined AS users_joined, users.last_active AS users_last_active, users.name AS users_name, us
ers.gender AS users_gender, users.birthdate AS users_birthdate, users.full_name AS users_full_name, users.hosting_status AS users_hosting_status, users.verification AS users_verification, users.community_stan
ding AS users_community_standing, users.occupation AS users_occupation, users.about_me AS users_about_me, users.about_place AS users_about_place, users.color AS users_color, users.avatar_filename AS users_ava
tar_filename, users.languages AS users_languages, users.countries_visited AS users_countries_visited, users.countries_lived AS users_countries_lived, users.is_banned AS users_is_banned, users.max_guests AS us
ers_max_guests, users.multiple_groups AS users_multiple_groups, users.last_minute AS users_last_minute, users.accepts_pets AS users_accepts_pets, users.accepts_kids AS users_accepts_kids, users.wheelchair_acc
essible AS users_wheelchair_accessible, users.smoking_allowed AS users_smoking_allowed, users.sleeping_arrangement AS users_sleeping_arrangement, users.area AS users_area, users.house_rules AS users_house_rul
es, users.accepted_tos AS users_accepted_tos, users.new_email AS users_new_email, users.new_email_token AS users_new_email_token, users.new_email_token_created AS users_new_email_token_created, users.new_emai
l_token_expiry AS users_new_email_token_expiry
FROM users
WHERE users.username = %(username_1)s]
[parameters: {'username_1': 'aapeli'}]
(Background on this error at: http://sqlalche.me/e/13/e3q8)
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 608, 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 "/app/src/couchers/interceptors.py", line 137, in sanitizing_function
    res = prev_func(req, context)
  File "/app/src/couchers/interceptors.py", line 108, in timetaking_function
    res = prev_func(request, context)
  File "/app/src/couchers/servicers/auth.py", line 268, in Login
    user = get_user_by_field(session, request.user)
  File "/app/src/couchers/db.py", line 136, in get_user_by_field
    return session.query(User).filter(User.username == field).one_or_none()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 3459, in one_or_none
    ret = list(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 3535, in __iter__
    return self._execute_and_instances(context)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 3560, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 608, 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 users.id AS users_id, users.username AS users_username, users.email AS users_email, users.hashed_password AS users_hashed_password, users.phone AS users_phone, users.phone_status AS users_phone_s
tatus, ST_AsEWKB(users.geom) AS users_geom, users.geom_radius AS users_geom_radius, users.city AS users_city, users.joined AS users_joined, users.last_active AS users_last_active, users.name AS users_name, us
ers.gender AS users_gender, users.birthdate AS users_birthdate, users.full_name AS users_full_name, users.hosting_status AS users_hosting_status, users.verification AS users_verification, users.community_stan
ding AS users_community_standing, users.occupation AS users_occupation, users.about_me AS users_about_me, users.about_place AS users_about_place, users.color AS users_color, users.avatar_filename AS users_ava
tar_filename, users.languages AS users_languages, users.countries_visited AS users_countries_visited, users.countries_lived AS users_countries_lived, users.is_banned AS users_is_banned, users.max_guests AS us
ers_max_guests, users.multiple_groups AS users_multiple_groups, users.last_minute AS users_last_minute, users.accepts_pets AS users_accepts_pets, users.accepts_kids AS users_accepts_kids, users.wheelchair_acc
essible AS users_wheelchair_accessible, users.smoking_allowed AS users_smoking_allowed, users.sleeping_arrangement AS users_sleeping_arrangement, users.area AS users_area, users.house_rules AS users_house_rul
es, users.accepted_tos AS users_accepted_tos, users.new_email AS users_new_email, users.new_email_token AS users_new_email_token, users.new_email_token_created AS users_new_email_token_created, users.new_emai
l_token_expiry AS users_new_email_token_expiry
FROM users
WHERE users.username = %(username_1)s]
[parameters: {'username_1': 'aapeli'}]
(Background on this error at: http://sqlalche.me/e/13/e3q8)
2021-02-15 14:30:27.421: 10: Probably an unknown error! Sanitizing...
```

Changed files:
app/backend/src/couchers/db.py

diff --git a/app/backend/src/couchers/db.py b/app/backend/src/couchers/db.py
index 4c3b7b410..83053ecfd 100644
--- a/app/backend/src/couchers/db.py
+++ b/app/backend/src/couchers/db.py
@@ -41,9 +41,15 @@ def apply_migrations():
 @functools.lru_cache
 def _get_base_engine():
     if config.config["IN_TEST"]:
-        return create_engine(config.config["DATABASE_CONNECTION_STRING"], poolclass=NullPool, future=True)
+        poolclass = NullPool
     else:
-        return create_engine(config.config["DATABASE_CONNECTION_STRING"], future=True)
+        poolclass = None
+    # `future` enables SQLalchemy 2.0 behaviour
+    # `pool_pre_ping` checks that the connections in the pool are alive before using them, which avoids the "server
+    # closed the connection unexpectedly" errors
+    return create_engine(
+        config.config["DATABASE_CONNECTION_STRING"], future=True, pool_pre_ping=True, poolclass=poolclass
+    )
 
 
 def get_engine(isolation_level=None):