Sqlalchemy Orm

{
  "name": "sqlalchemy",
  "version": "1.0.0",
  "category": "toolchain",
  "toolchain": "python",
  "framework": "sqlalchemy",
  "tags": [
    "orm",
    "database",
    "sqlalchemy",
    "alembic",
    "migrations",
    "sql",
    "postgresql",
    "mysql",
    "sqlite",
    "async",
    "fastapi",
    "flask"
  ],
  "entry_point_tokens": 80,
  "full_tokens": 5876,
  "related_skills": [
    "../../frameworks/fastapi",
    "../../frameworks/flask",
    "../../testing/pytest",
    "../../../database/postgresql",
    "../../../database/mysql"
  ],
  "author": "Claude MPM",
  "license": "MIT",
  "subcategory": "data",
  "description": "SQLAlchemy 2.0 ORM with declarative models, relationships, migrations, and async support",
  "when_to_use": [
    "Building Python applications with relational databases",
    "Need type-safe ORM with powerful query builder",
    "Working with FastAPI, Flask, or Django",
    "Managing database schemas with migrations (Alembic)",
    "Async database operations with AsyncSession"
  ],
  "key_features": [
    "SQLAlchemy 2.0 modern syntax with Mapped types",
    "Declarative models with type hints",
    "Powerful query builder with select() API",
    "Relationship mapping (one-to-many, many-to-many)",
    "Alembic migration support",
    "Async SQLAlchemy with AsyncSession",
    "Connection pooling and performance optimization",
    "FastAPI and Flask integration patterns"
  ],
  "prerequisites": [
    "Python 3.10+",
    "Basic SQL knowledge",
    "Understanding of relational databases"
  ],
  "installation": "pip install sqlalchemy alembic",
  "created_at": "2025-11-30",
  "progressive_loading": {
    "entry_tokens": 80,
    "full_tokens": 5000,
    "has_progressive_structure": true
  }
}


---
name: sqlalchemy-orm
description: "SQLAlchemy Python SQL toolkit and ORM with powerful query builder, relationship mapping, and database migrations via Alembic"
user-invocable: false
disable-model-invocation: true
progressive_disclosure:
  entry_point:
    summary: "SQLAlchemy Python SQL toolkit and ORM with powerful query builder, relationship mapping, and database migrations via Alembic"
    when_to_use: "When working with sqlalchemy-orm or related functionality."
    quick_start: "1. Review the core concepts below. 2. Apply patterns to your use case. 3. Follow best practices for implementation."
---
# SQLAlchemy ORM Skill

---
progressive_disclosure:
  entry_point:
    summary: "Python SQL toolkit and ORM with powerful query builder and relationship mapping"
    when_to_use:
      - "When building Python applications with databases"
      - "When needing complex SQL queries with type safety"
      - "When working with FastAPI/Flask/Django"
      - "When needing database migrations (Alembic)"
    quick_start:
      - "pip install sqlalchemy"
      - "Define models with declarative base"
      - "Create engine and session"
      - "Query with select() and commit()"
  token_estimate:
    entry: 70-85
    full: 4500-5500
---

## Core Concepts

### SQLAlchemy 2.0 Modern API
SQLAlchemy 2.0 introduced modern patterns with better type hints, improved query syntax, and async support.

**Key Changes from 1.x:**
- `select()` instead of `Query`
- `Mapped[T]` and `mapped_column()` for type hints
- Explicit `Session.execute()` for queries
- Better async support with `AsyncSession`

### Installation
```bash
# Core SQLAlchemy
pip install sqlalchemy

# With async support
pip install sqlalchemy[asyncio] aiosqlite  # SQLite
pip install sqlalchemy[asyncio] asyncpg    # PostgreSQL

# With Alembic for migrations
pip install alembic

# FastAPI integration
pip install fastapi sqlalchemy
```

## Declarative Models (SQLAlchemy 2.0)

### Basic Model Definition
```python
from datetime import datetime
from typing import Optional
from sqlalchemy import String, DateTime, ForeignKey, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship

# Base class for all models
class Base(DeclarativeBase):
    pass

#