GET /api/contacts issues one extra SQL query per contact (N+1)

GET /api/contacts is the single most-hit endpoint in this API -- it's the main contact list view. Right now, listing N contacts issues roughly N + 1 SQL queries against the database (one to fetch the contacts, then one more per contact to fetch that contact's groups). On a dev machine with 5 contacts this is invisible. On a production account with a few thousand contacts, this endpoint's database load -- and its latency -- grows linearly with the size of the contact list, with no cap. This is the textbook "worked fine in the demo, fell over in production" bug: it degrades gradually as data grows, so it tends to surface first as a vague "the app feels slow today" complaint and, later, as a full database-connection-pool exhaustion incident that pages whoever is on-call -- not as a clean error anyone can immediately trace back to one line of code.

Contact.groups (in app/models.py) is configured with lazy="select" -- SQLAlchemy's default lazy-loading strategy, meaning each contact's .groups collection is only fetched from the database the moment it's first accessed, with its own separate query. Serializing a list of contacts (which reads .groups on every single one, to nest each contact's groups in the JSON response) therefore issues one additional query per contact on top of the base list query.

cd flask/contact_book source .venv/bin/activate SECRET_KEY=dev-secret pytest practice_tickets/tests/test_ticket_06_n_plus_one_groups.py -v

This is a query-count regression, not visible in a single curl response body (the JSON

output is byte-for-byte identical either way) -- the dedicated test creates 8 contacts,

each in their own group, then counts every SELECT issued by a single GET /api/contacts

call using a SQLAlchemy before_cursor_execute event hook.

This is the classic ORM "N+1 query" problem, and it's specifically about which loading strategy a relationship uses, not about the query being "wrong" in the SQL-correctness sense -- every individual query it runs is perfectly valid, there's just far more of them than there needs to be. SQLAlchemy relationships can be configured with different lazy= loading strategies ("select" = lazy, one query per access; "selectin" = one extra batched query using an IN (...) clause covering every object in the parent result set, regardless of how many there are; "joined" = a single JOINed query, etc.) -- picking the wrong one is invisible in code review unless you specifically check for it, since nothing about the Python code that reads contact.groups looks any different either way.

Look at the lazy= argument on the Contact.groups relationship in app/models.py, and compare it against the lazy= argument on the Group.contacts relationship right below it in the same file (they were originally configured to match). Consider what "batch every related object across an entire result set into one extra query" is called as a SQLAlchemy loading strategy.