Python + SQLAlchemy Rules for Windsurf

# Python + SQLAlchemy Rules for Windsurf

# Python + SQLAlchemy 2.0 Rules

## Modern SQLAlchemy
- Use SQLAlchemy 2.0+ syntax — not 1.x legacy patterns
- Declarative mapped classes with `mapped_column`
- Type annotations on all column mappings
- Async engine with asyncpg for async applications

```python
from sqlalchemy.orm import Mapped, mapped_column, DeclarativeBase

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(unique=True, index=True)
    name: Mapped[str | None]
```

## Session Management
- One session per request — use dependency injection
- Context managers for session lifecycle
- Async sessions for async applications

## Query Patterns
- `select()` function — not `session.query()`
- Explicit `joinedload` / `selectinload` to avoid N+1
- Paginate with `offset` / `limit` or keyset pagination

## Migrations
- Alembic for all schema changes
- Never modify existing migration files
- Autogenerate migrations then review before applying