Flask-SQLAlchemy Session and Query Lifetime

Working case

A permit queue initially retrieves 47 cases and then reads case.district.name in a template. Each relationship access issues another query. A rushed fix eagerly loads all attachments too, causing one permit with thousands of scans to inflate memory. Another developer returns ORM cases inside a streaming CSV generator; the view has already returned when the generator runs, so lazy reads may fail or hold a session across a slow client. The corrected list applies reviewer scope in SQL, selects the displayed scalar columns, computes attachment counts in the database, orders by created_at and ID, and materializes the bounded response while the request context exists.

Implementation boundary

from sqlalchemy import func, select
from .database import db
from .models import Attachment, District, PermitCase, PermitReviewer

attachment_total = (
    select(func.count(Attachment.id))
    .where(Attachment.case_id == PermitCase.id)
    .correlate(PermitCase)
    .scalar_subquery()
)
statement = (
    select(PermitCase.id, PermitCase.title, District.name, attachment_total)
    .join(PermitReviewer, PermitReviewer.case_id == PermitCase.id)
    .join(District, District.id == PermitCase.district_id)
    .where(PermitReviewer.reviewer_id == reviewer_id)
    .order_by(PermitCase.created_at.desc(), PermitCase.id.desc())
    .limit(47)
)
cards = [tuple(row) for row in db.session.execute(statement).all()]

Use a select statement with an assignment join or exists predicate. Build a count subquery for attachments rather than loading the collection. For one-to-many relations that really must be shown, selectinload can batch children for a bounded page; for a to-one relation, joinedload can avoid repeated district reads. Prefer a scalar projection when the endpoint only needs a few columns. Cursor continuation must use the same reviewer predicate on every request and a unique tie breaker. A client cursor is not a permission token. For large exports, create a durable export job with an explicit actor and query parameters; let its worker open its own application context and session, then store an artifact for download.

Cost and boundaries

A 47-row scalar page has O(47) response memory, while a full eager load costs O(parents plus children), which may be unbounded in practice. An attachment count subquery uses indexed database work and avoids transferring all attachment rows, but it still needs a plan check under large attachment tables. Cursor paging can avoid the increasing skip cost of deep offsets when the order and scope have usable indexes; it gives up cheap numbered-page navigation and exact totals. A long-running streamed query may tie up a database connection for the duration of the client's download. Observe query count, rows transferred, memory, connection occupancy, and the plan for a deep page, not just wall-clock time on an empty fixture.

Failure trace

Seed 47 cases with repeated timestamps and follow continuation through the entire queue without duplicate or missing IDs. Add one case with 620 attachments and verify the list obtains its count without hydrating 620 models. Remove the district join in a negative test and record the per-row query rise. Submit the same cursor as reviewer 81 and confirm the reviewer scope remains on the SQL query. Close the request context before consuming a deliberately lazy generator and confirm that test detects the invalid dependency. Simulate a slow export client; the normal request path should not keep a database transaction or connection open for the download duration.

Verification

Reviewer scope is part of SQL on every page.
The response holds scalar values instead of detached ORM models.
An attachment count does not load every attachment object.

Practice drill

Implement a bounded reviewer queue with 47 items per page. Display case ID, title, district, attachment total, and created time. Use explicit scalar output records. Compare a lazy district relationship, a bounded joined district query, and an unnecessary attachment eager load while recording query and row counts. Add a composite index that matches reviewer scope and sort order where the actual schema permits it. Create timestamp ties and verify deterministic next-page behavior. Move the CSV export to a worker that receives only reviewer identity and filter values, opens a fresh application context, and writes an artifact. Ensure the artifact download performs a new permission check.

Decision note

Finish database work inside a bounded context, then hand plain response data or durable job identifiers to later stages.

Common Mistakes

Using a long-lived request session as a worker session.
Fixing N+1 by eagerly loading unbounded collections.
Ordering a cursor only by a nonunique timestamp.

Apply and check

Build Project: Flask permit service boundaries and review Web Development: Flask context and service boundaries quiz.