A single database session is used for the lifetime of the application

[sersorrel]

Each Database uses exactly one SQLAlchemy session:

backend/cogs/db/interface.py

Lines 53 to 54 in ca67e80

	Session = sessionmaker(bind=self._engine)
	self._session = Session()

and the Database is shared across the whole application:

backend/cogs/main.py

Line 64 in ca67e80

app["db"] = db = Database(c["database"])

which implies that the entire application – all async contexts – shares a session.

This is terrible!

The Session is very much intended to be used in a non-concurrent fashion, which usually means in only one thread at a time.

– from https://docs.sqlalchemy.org/en/13/orm/session_basics.html#is-the-session-thread-safe

In practice, nothing should go wrong until the application is handling multiple things at once (requests, scheduled jobs, ...), and since there are fairly few users and those users are unlikely to be working at the same time as the scheduled jobs (midnight UTC), it's unlikely this will actually cause any problems. However: if anything does go wrong because of this, it will be an absolute nightmare to work out what's happened.

Fixing this will probably involve using SQLAlchemy's scoped_session. One way would be to make the Database store a custom subclass of scoped_session instead of sessionmaker. The subclass would be very similar to SQLAlchemy's implementation, except it would use a new ContextVarsRegistry instead of SQLAlchemy's ScopedRegistry or ThreadLocalRegistry; this new registry would use Python 3.7's contextvars module (supported by aiohttp) to store one session per request. An aiohttp middleware to remove the session at the end of each request will also be necessary.