Bump sqlalchemy to version 2.0.17
Getting sqlachemy version 2 running as well as fixing session management.
Commit 1 is addressing changes with sqlalchemy v2: Mainly the removal of "auto-commit". The current codebase is heavily relying on that feature. We can work around that by using a wrapper that performs the commit.
Commit 2 implements request based sessions using a middle-ware. Ideally we'd go for the dependency injection approach, but that requires a huge amount of changes in the codebase. Now, almost all routes have database interaction, so it should not be too much of concern to create a new session for each request because we'd need it anyways.
Some of the tests only worked properly since every DB operation was run within the same session. Utilizing separate sessions, we need to refresh (re-query) objects from the DB. With the default
REPEATABLE READ however, the DB is effectively NOT re-querying data, it simply returns what has been queried before. Thus
db.refresh won't reload from the DB. We can either set the isolation level to
READ COMMITTED, making
db.refresh working as expected or simply close our transaction (forcing the creation of a new one) with
db.commit before we access our db object.
Check impact change for isolation_level (
READ COMMITTEDinstead of the default
We might want to simply use
More (manual/) "real-world" testing
Stress testing (benchmarks; DDoS the thing)
Ask @artafinde how fast he can downgrade to a previous release in case of issues 😉