751 queries, a locked SQLite file, and two injection surfaces

The 751 breaks down as 1 root query + 250 lazy loads of order.customer + 250 lazy loads of order.lines + 250 further statements when len() materialises each collection's contents. SQLAlchemy's lazy="select" default issues one statement per distinct parent object.

Loader choice by cardinality:

• Order.customer is many-to-one. joinedload(Order.customer) adds a LEFT OUTER JOIN and does it in the same round trip - no extra query, and no row multiplication because a customer maps to exactly one row per order.
• Order.lines is one-to-many. joinedload here multiplies the result set (250 orders × their lines, with duplicated order columns) and forces .unique() on the result. selectinload(Order.lines) issues one SELECT ... WHERE order_id IN (...) and stitches, which for 250 parents is 1 extra query and no duplication.

stmt = (select(Order)
        .options(joinedload(Order.customer), selectinload(Order.lines))
        .order_by(Order.created_at.desc())
        .limit(50))

That is 3 statements, and the limit brings the payload down too - 250 rows in one response is the other half of the 1.4 s.

Do not use len(o.lines) for a count at all. If the UI only needs a badge, project it: select(Order.id, func.count(Line.id)).outerjoin(Line).group_by(Order.id) - one query, no collection loaded. Loading objects just to count them is the real cost.

Guard against regression: attach a before_cursor_execute event listener that counts statements per request and assert an exact count in a test. Asserting "3 queries" fails loudly the moment someone adds a lazy access; asserting latency does not.

SQLite permits one writer at a time for the whole database file, regardless of thread count. In the default rollback-journal mode a reader also takes a SHARED lock that blocks the writer's RESERVED→EXCLUSIVE upgrade, so readers block writers too. With eight threads the failures are structural, not bad luck. Python's sqlite3 sets busy_timeout to 5 s by default, so a database is locked error means a lock was held longer than five seconds - almost always a write transaction left open across slow Python work (an HTTP call, a hash, a log flush) inside the session.

Fix in layers:

1. PRAGMA journal_mode=WAL - readers no longer block the writer and vice versa. This alone removes most of the 200/hour. Persist it; it survives reopen.
2. PRAGMA busy_timeout=5000, PRAGMA synchronous=NORMAL (safe under WAL).
3. Short transactions. Audit writes should be a single INSERT committed immediately - no session held across the surrounding request.
4. One writer. Serialise writes through a queue.Queue drained by a single dedicated writer thread, or use BEGIN IMMEDIATE so contention fails fast at BEGIN instead of mid-transaction (a deferred transaction that tries to upgrade can deadlock and must be retried anyway).
5. Connection per thread. Never share a connection; if you must, check_same_thread=False plus an explicit lock, but then transactions interleave.
6. Retry on OperationalError with exponential backoff for the residual cases - but a fixed retry is a band-aid, not the fix.

Stop discarding audit rows: enqueue and let the writer own persistence. If the write volume genuinely needs parallel writers, SQLite is the wrong engine and PostgreSQL is the answer - that is an architectural decision, not a pragma.

SQL - string interpolation. text(f"... status = '{status}'") lets status=' OR '1'='1 return every order (the customer who saw another customer's order), and '; DROP TABLE orders; -- destroy data. text() does not sanitise; it only marks the string as executable.

Fix: bind parameters, never format.

stmt = text("SELECT * FROM orders WHERE status = :status")
rows = session.execute(stmt, {"status": status})

Or the ORM equivalent select(Order).where(Order.status == status). Add defence in depth: validate status against an enum (Literal/Enum in the Pydantic model) so an unknown value is a 422 before it reaches a driver, and give the app a least-privilege database role - no DDL.

MongoDB - operator injection. The reasoning transfers because the driver still parses structured input; the grammar is just different. json.loads(request.body) passed as a filter means the client sends {"email": "[email protected]", "password": {"$ne": null}} - the query matches a user whose password is not null, and login succeeds without credentials. {"$gt": ""} dumps the collection; $where executes JavaScript server-side.

Fix: never pass a client body through as a filter. Build it from validated fields:

payload = LoginIn.model_validate_json(request.body)   # typed, strict
user = db.users.find_one({"email": payload.email})

And reject user-supplied keys starting with $ or containing . anywhere a field name is client-controlled. Then verify the password hash in Python - never in the query.

The shared principle: interpreters must never receive client text as syntax, whether that interpreter is SQL, a Mongo query planner, or a shell.