SQLAlchemy, Repositories, and Units of Work

SQLAlchemy maps Python operations to SQL and manages a session identity map; it does not remove the need to understand transactions or queries. Repositories express application-specific persistence, while a unit of work owns one transaction. Routes should not scatter commits, and domain code should not depend on ORM sessions.

What will you be able to do?

• configure an async SQLAlchemy engine and bounded pool;
• map a ticket table and execute explicit statements;
• own commit and rollback in one unit of work;
• detect N+1 queries and accidental lazy I/O.

How do you configure the persistence adapter?

Install the driver and ORM:

Create src/supportdesk_ai/adapters/db.py:

Pool sizes across every worker must remain below database connection capacity with headroom for migrations and operations.

An application-specific repository uses explicit tenant scope:

The application service decides when the use case succeeds; the unit of work commits once. On exception it rolls back. Repository methods do not commit independently, because multiple related changes may need atomicity.

How do you avoid ORM surprises?

• Load required relationships explicitly.
• Count emitted queries in tests for critical endpoints.
• Select only fields needed for large lists.
• Never serialize an ORM row as the public API by accident.
• Set statement timeouts and slow-query monitoring at appropriate layers.

Why does this matter in AI-native systems?

An AI action may create an ai_run, enqueue an outbox event, and update ticket state atomically. The model call itself should normally occur outside a long database transaction; record intent, perform external work, then persist the outcome idempotently.

Common mistakes

• One global session shared across concurrent requests.
• Committing inside repository methods.
• Lazy-loading while serializing creates N+1 queries or async errors.
• Catching an integrity error without rolling back leaves the session unusable.
• Pool capacity multiplied by workers overwhelms PostgreSQL.

AI Pair-Programmer Prompt

Exercise

Implement create-and-comment as one transaction. Force the comment insert to fail and prove the ticket is not committed. Count SQL statements for listing 25 tickets with authors.

Acceptance criteria: rollback is atomic, tenant scope is in every query, and the list query count does not grow with result size.

Knowledge check

1. Who should own the transaction?
2. Why avoid a model call inside an open database transaction?
3. What is the N+1 problem?

---

Answers

1. The use case's unit of work.
2. Remote latency holds locks/connections and failure semantics do not align with the database.
3. Loading one collection triggers one initial query plus another query for each item.

Completion checklist

• Session scope is per unit of work.
• Repositories never hide commits.
• Critical query counts are tested.

Primary references

• SQLAlchemy unified tutorial
• SQLAlchemy asyncio