sqlalchemy-code-review

N+1 queries occur when loading related objects triggers separate database queries for each parent record. Use joinedload() or selectinload() in your select() statements to fetch relationships efficiently in a single query, reducing database round trips and improving performance.

Use context managers (with Session() as session:) to ensure sessions are properly closed and resources released. For async contexts, use async context managers. Avoid sharing sessions across threads or requests; create a fresh session per request or operation to prevent state leaks and connection exhaustion.

Reversible migrations require explicit upgrade() and downgrade() functions that safely undo schema changes. Test both directions, avoid data loss, and use op.execute() carefully. Ensure migrations preserve data integrity and can roll back without manual intervention for production safety.

Lazy loading still works but is discouraged in SQLAlchemy 2.0; instead, explicitly load relationships using joinedload(), selectinload(), or subqueryload() in your select() query. This prevents surprise N+1 queries and makes data access patterns explicit and testable.

Misconfigured relationship() definitions—missing foreign_keys, incorrect cascade settings, or improper lazy parameters—lead to unexpected query behavior and session errors. Review lazy loading strategy, cascade rules, and back_populates alignment to ensure relationships load and persist data correctly.

Use bulk insert, update, and delete operations via insert(), update(), and delete() constructs instead of looping with add()/merge(). Bulk operations bypass ORM overhead, execute efficiently on the database, and are essential for high-volume data changes while maintaining session consistency.