FastAPI Async + Postgres

# CLAUDE.md — FastAPI Async + Postgres

## Async stack

- Driver: **asyncpg** (fastest, async-native).
- ORM: SQLAlchemy 2.0 with `AsyncEngine` + `AsyncSession`. Or **SQLModel** if you want Pydantic-flavored models.
- Migrations: **Alembic** in async mode (`async def run_migrations_online`).

## Engine & sessions

```python
engine = create_async_engine(
    settings.DATABASE_URL,
    pool_size=10,
    max_overflow=10,
    pool_pre_ping=True,
)
SessionLocal = async_sessionmaker(engine, expire_on_commit=False)
```

- `expire_on_commit=False` so attributes stay accessible after commit.
- Pool sizing: start with `pool_size = workers × concurrent_requests`. Adjust under load.
- One async session per request, via a FastAPI dependency:
  ```python
  async def get_db() -> AsyncIterator[AsyncSession]:
      async with SessionLocal() as session:
          yield session
  ```

## Async patterns

- Every endpoint is `async def`. Sync defs run in a thread pool — usually wrong here.
- Every DB call: `await session.execute(stmt)`. Forgetting `await` returns a coroutine and a confusing error.
- Use `asyncio.gather` to run independent reads in parallel:
  ```python
  user, orders = await asyncio.gather(get_user(id), get_orders(id))
  ```

## Connection pooling

- For serverless / many short-lived containers, use **pgbouncer** in transaction mode in front of Postgres. Configure SQLAlchemy with `pool_pre_ping=True` and `connect_args={"prepared_statement_cache_size": 0}` for asyncpg.
- For long-lived processes (k8s deploy), pool inside the app with `pool_size`. Don't double-pool.
- `DATABASE_URL` for the pooled URL; `DIRECT_URL` for migrations (Alembic needs direct).

## Transactions

- Begin a transaction at the boundary, not per-statement:
  ```python
  async with session.begin():
      ...  # one atomic unit
  ```
- Read-only endpoints don't need an explicit transaction.
- Don't catch and swallow `IntegrityError` — let it propagate, map to `HTTPException(409)` at the boundary.

## Migrations

- Alembic with `revision --autogenerate` then **always review** the diff. Autogenerate misses constraints, indexes, and enum changes.
- Long migrations run as a separate job, not at app boot.
- Backward-compatible deploys: add nullable column → deploy code → backfill → make NOT NULL.

## Performance

- Add indexes for every WHERE/ORDER column you actually query.
- Use `selectinload` / `joinedload` to avoid N+1 — pick one per relationship and stick with it.
- For bulk inserts, use `session.execute(insert(Model), [...])`. Don't loop `session.add`.

## Don't

- Don't mix sync and async sessions. Pick one path; convert legacy code, don't bridge.
- Don't share an `AsyncSession` across coroutines. One per task.
- Don't use the default psycopg2 sync driver in an async app. It blocks.
- Don't run `await` calls in `__init__`. Use an async factory or lifespan.