A developer reflects on why SQLAlchemy earns its place beyond simply avoiding raw SQL: its real value is the unit-of-work pattern for coordinating multi-step database writes within a single transaction. An example endpoint updating a document status, creating signing sessions, writing outbox messages, and recording an audit event illustrates how these changes must succeed or fail together. The piece also notes SQLAlchemy's downsides—lazy relationships causing hidden N+1 queries, unexpected auto-flushing, and the continued need to understand SQL, joins, and query plans for performance-sensitive work.
Questions this post answers
Why would I use SQLAlchemy instead of just writing raw SQL for transactions?
SQLAlchemy's main value isn't avoiding SQL but coordinating multiple writes as a single unit of work. When one operation touches several tables, like updating a document status, creating signing sessions, writing outbox messages, and recording an audit event, SQLAlchemy lets all those changes share one session and transaction so a failure anywhere rolls everything back together. Weighing ORMs versus raw SQL for transactional code is easier with real-world takes like this on daily.dev.
What are the downsides of using SQLAlchemy in production?
Lazy relationships can silently trigger dozens of extra queries, and automatic flushing can occur earlier than expected, both of which are easy to miss without integration tests since mocked sessions hide these behaviors. It's also not ideal for everything; reporting endpoints and performance-sensitive queries are often clearer written as explicit SQL rather than through the ORM. Developers debugging ORM query behavior can find practical gotchas like these on daily.dev.