Async ORM & SQLAlchemy

The use of Asynchronous SQLAlchemy (AsyncSession) is a core component for handling database operations in projects requiring high concurrency, such as FastAPI, aiohttp, or Celery-based workers. It provides the infrastructure for non-blocking I/O operations, allowing the system to handle multiple database requests simultaneously without blocking the main event loop.

Within the project context, SQLAlchemy Async is utilized to manage state transitions, maintain activity logs, and handle external integration webhooks. However, its implementation requires strict adherence to specific patterns regarding session lifecycle, task safety, and transaction management to avoid common pitfalls like MissingGreenlet errors or stale data attributes.

Sources: BY-STACK/async-orm.md:1-10, INTEGRATION.md:105-130

Session Lifecycle and Task Safety

The project enforces a "One AsyncSession per Task" architecture. AsyncSession instances are not task-safe and cannot be shared across multiple coroutines using tools like asyncio.gather. Each concurrent task must open and manage its own session via a session maker to prevent internal state corruption.

The diagram shows that each task must instantiate its own session rather than sharing a single global session, ensuring task isolation.
Sources: BY-STACK/async-orm.md:38-48, claude-md-snippets/async-orm.md:3-6

Lifecycle Pitfalls

A critical issue arises during error handling: after an await session.rollback(), all ORM objects within that session expire. Accessing attributes on these objects outside the appropriate greenlet context triggers a MissingGreenlet exception. To mitigate this, developers must extract necessary primitives (like IDs or emails) before the potential rollback or use session.expunge(obj) to detach the object from the session.

Sources: BY-STACK/async-orm.md:17-36, claude-md-snippets/async-orm.md:8-9

Transaction Management & Comparisons

Asynchronous operations introduce specific requirements for data consistency, particularly when dealing with loops or conditional updates.

Post-Commit Refreshes

When executing await session.commit() inside a loop, SQLAlchemy by default only refreshes Primary Keys (PK). Accessing other related fields or attributes after a commit without an explicit await session.refresh(row) can lead to stale data usage.

Sources: BY-STACK/async-orm.md:50-60, claude-md-snippets/async-orm.md:11-12

Compare-and-Swap (CAS) on Nullable Columns

A common bug occurs when performing updates on columns that allow NULL. In SQL, col = NULL evaluates to NULL (effectively FALSE). In SQLAlchemy, the is_(None) method must be used for comparison when the expected value is None to ensure the WHERE clause functions correctly.

Sources: BY-STACK/async-orm.md:79-96, claude-md-snippets/async-orm.md:18-20, claude-md-snippets/universal.md:33-35

Async Engine and External Workers

Integrating the Async ORM with task runners like Celery requires careful engine management. If an engine is created at the module level, it binds to the event loop present during import. Since Celery creates a fresh event loop for each task, the original engine will fail in subsequent tasks.

SQL vs. Python Operations

Developers must distinguish between Python-level methods and SQLAlchemy Column expressions. Applying Python string methods like .strip() or .lower() directly on a Column object is a no-op in the resulting SQL. The sqlalchemy.func module must be used to ensure the operation is translated to database functions like TRIM() or LOWER().

Sources: BY-STACK/async-orm.md:83-87, BY-STACK/postgres.md:118-124, claude-md-snippets/async-orm.md:18-19

Data Consistency and Audit Logging

The project employs an "Audit Log on Failure" pattern for UPDATE operations using Compare-and-Swap. If an UPDATE result returns a rowcount of 0 (indicating the condition was not met due to a race or stale data), an activity log must still be written with metadata.applied=false. This ensures that downstream systems, such as cron jobs or dashboards, are aware the event was attempted but not executed.

Sources: BY-STACK/async-orm.md:104-118, claude-md-snippets/async-orm.md:22-23, claude-md-snippets/universal.md:27-31

Conclusion

Successfully implementing Async ORM with SQLAlchemy requires managing the intersection of asynchronous task lifecycles and database transaction boundaries. By ensuring session isolation, handling nullable column logic with is_(None), and properly refreshing data after commits, the system maintains high performance without sacrificing data integrity or reliability.

Sources: BY-STACK/async-orm.md:1-120