JSONField serializes with JSONCodec, but columns declared as SQLAlchemy's own JSON
type go through the engine's serializer instead, and no engine set one. That left
Chat.chat - the largest blob the app stores - on stdlib json.dumps/loads no matter
what ENABLE_ORJSON was set to, while the rest of the app used the codec. SQLAlchemy
invokes it once per write and once per read, so every chat read and write paid a
full stdlib pass over the whole conversation on top of whatever the caller did.
Both engine constructors are now wrapped so the codec is wired in by default and
cannot be missed by a call site that forgets it; an explicit json_serializer still
wins. The 10 create_engine/create_async_engine calls in this module go through the
wrappers. Vector-store engines (pgvector, mariadb, opengauss) are separate databases
and are left alone.
Serializing and deserializing chat-shaped blobs, median of 11 runs:
| chat blob | write | read |
| --- | --- | --- |
| 600 msgs (2.8 MB) | 10.1 -> 1.7 ms | 8.4 -> 3.7 ms |
| 3000 msgs (14.2 MB) | 51.9 -> 8.0 ms | 48.5 -> 27.8 ms |
| 6000 msgs (28.5 MB) | 105.9 -> 29.5 ms | 112.7 -> 80.5 ms |
With ENABLE_ORJSON off JSONCodec is stdlib json, so this is a no-op until the flag
is set - the change cannot regress a default deployment.
With it on, a round-trip probe through a native JSON column returns objects equal to
the stdlib ones on all 12 shapes tried: ASCII, CJK, emoji, astral-plane, unicode
keys, null bytes, lone surrogates, floats, ints above 2**63 and 2**64, line
separators, empty and deeply nested. Stored text changes for non-ASCII, which is
written as raw UTF-8 rather than backslash-uXXXX escapes and is correspondingly
smaller. Nothing queries that text by escape except two Postgres safety filters in
chats.py, and both still hold: a null byte is escaped identically by both codecs,
and the title filter reads a text column rather than JSON. The ->> and json_extract
searches decode the string before matching, so escaping cannot reach them.
Two differences are inherent to JSONCodec and already apply to every JSONField
column: ints beyond 2**64-1 come back as float, and NaN/Infinity serialize to null
rather than the bare literals stdlib emits - the latter being invalid JSON that a
Postgres json column rejects today. Neither shape occurs in chat blobs. Alembic
builds its own engine and stays on stdlib, which is fine in both directions since
each codec reads the other's output.
Claude-Session: https://claude.ai/code/session_014BXoM6QiFJKisxcxKAXii8
Co-authored-by: Claude <noreply@anthropic.com>
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.
Classic298