Two independent sources of fixed per-request cost:
The async SQLite engine was created with pool_pre_ping=True. A pre-ping guards against server connections dropped by timeouts or restarts, which cannot happen to a local SQLite file; each ping still costs a hop into the aiosqlite worker thread plus a SELECT 1 on every connection checkout, and with session sharing off a single request checks out a connection for every model-layer call it makes. The Postgres engines keep their pre-ping, where it is actually protective.
CommitSessionMiddleware unconditionally ran ScopedSession.commit() plus remove() after every HTTP request. The scoped registry instantiates a session on first access, so on the vast majority of requests (which never touch the sync session, per the middleware's own docstring) this built a Session, opened and committed an empty transaction and tore everything down for nothing. The middleware now checks ScopedSession.registry.has() first: requests that used the sync session are committed and removed exactly as before, on success and on the rollback path alike, and idle requests skip the machinery entirely.
Benchmark (real SQLite database):
| metric | before | after |
| --- | --- | --- |
| user row fetch incl. session + connection checkout | 681 us | 514 us |
| idle-request sync session work (create + empty commit + teardown) | 12.3 us | 0.26 us |
The first row saves per model-layer call, not per request: a request making five DB calls saves the checkout ping five times.
Functionally verified: normal reads and writes work with pre-ping off; an idle request through the middleware leaves no sync session behind; a request that uses the sync session still gets committed and removed.
The SQLCipher engine used a dummy sqlite:// URL with a creator function,
which caused SQLAlchemy to auto-select SingletonThreadPool. This pool
non-deterministically closes in-use connections when thread count exceeds
pool_size (default 5), leading to use-after-free segfaults (exit code 139)
in the native sqlcipher3 C library during multi-threaded operations like
user signup.
Now defaults to NullPool (each operation creates/closes its own connection)
for maximum safety with the native C extension. Also respects the
DATABASE_POOL_SIZE setting: if explicitly set >0, QueuePool is used with
the configured pool parameters, matching the behavior of other DB paths.
Fixes#22258
* sequential
* zero default
* fix
* fix: preserve absolute paths in sqlite+sqlcipher URLs
Previously, the connection logic incorrectly stripped the leading slash
from `sqlite+sqlcipher` paths, forcibly converting absolute paths
(e.g., `sqlite+sqlcipher:////app/data.db`) into relative paths
(which became `app/data.db`). This caused database initialization failures
when using absolute paths, such as with Docker volume mounts.
This change removes the slash-stripping logic, ensuring that absolute
path conventions (starting with `/`) are respected while maintaining
support for relative paths (which do not start with `/`).
- Added sqlcipher3 dependency to requirements.txt for SQLCipher integration.
- Modified database connection handling in wrappers.py to support encrypted SQLite databases using the new sqlite+sqlcipher:// URL protocol.
- Updated db.py to handle SQLCipher URLs for SQLAlchemy connections.
- Enhanced Alembic migration environment to support SQLCipher URLs.
