Skip to content

avibebuilder/claude-prime / .claude/starter-skills/backend-fastapi-python

backend-fastapi-python

Unverified

Use this skill for any Python backend work in this project: building FastAPI endpoints, writing service functions, defining Pydantic/SQLModel schemas, running Alembic migrations, or debugging 422 errors. Essential for authentication and authorization patterns — setting up get_current_user, is_superuser checks, admin-only guards, role-based access, and dependency injection chains like Depends(). Also covers middleware, background tasks, async SQLAlchemy sessions, ORM relationship loading, and request/response design. Activate whenever the question involves Python API code, FastAPI patterns, or backend architecture in this codebase. Not for frontend, Docker, CI/CD, or infrastructure.

FileKindSize
SKILL.mdskill md3.9 KB
references/auth.mdmarkdown5.5 KB
references/configuration.mdmarkdown3.5 KB
references/database.mdmarkdown3.9 KB
references/dependencies.mdmarkdown4.4 KB
references/error-handling.mdmarkdown4.7 KB
references/file-structure.mdmarkdown3.8 KB
references/middleware.mdmarkdown4.9 KB
references/models.mdmarkdown3.9 KB
references/routing.mdmarkdown4.5 KB
references/schemas.mdmarkdown4.3 KB
references/services.mdmarkdown5.1 KB

references/database.md

View on GitHub
# Database

## Async SQLAlchemy Setup

### Engine and Session Factory

```python
# src/core/database.py
from collections.abc import AsyncGenerator

from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine

from src.core.config import settings

# Create async engine
engine = create_async_engine(
    settings.DATABASE_URL,
    echo=settings.DEBUG,  # Log SQL in development
    future=True,
)

# Session factory
async_session_maker = async_sessionmaker(
    engine,
    class_=AsyncSession,
    expire_on_commit=False,  # Prevent lazy load errors
)


# Dependency for FastAPI
async def get_db() -> AsyncGenerator[AsyncSession, None]:
    async with async_session_maker() as session:
        yield session
```

### Using in Routes

```python
from fastapi import Depends
from sqlalchemy.ext.asyncio import AsyncSession

from src.core.database import get_db


@router.get("/users")
async def list_users(db: AsyncSession = Depends(get_db)):
    # Use db session here
    pass
```

## Alembic Migrations

### Setup

```bash
# Initialize alembic
alembic init alembic
```

### Configure env.py

```python
# alembic/env.py
from src.core.config import settings
from src.models import *  # Import all models

config.set_main_option("sqlalchemy.url", settings.DATABASE_URL.replace("+asyncpg", ""))
target_metadata = BaseSQLModel.metadata
```

### Common Commands

```bash
# Create migration
alembic revision --autogenerate -m "description"

# Apply migrations
alembic upgrade head

# Rollback one
alembic downgrade -1

# Show current
alembic current
```

## Query Patterns

### Basic CRUD

```python
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from src.models.user_model import User


# Get by ID
async def get_user_by_id(db: AsyncSession, user_id: str) -> User | None:
    result = await db.execute(select(User).where(User.id == user_id))
    return result.scalar_one_or_none()


# Get by unique field
async def get_user_by_email(db: AsyncSession, email: str) -> User | None:
    result = await db.execute(select(User).where(User.email == email))
    return result.scalar_one_or_none()


# Create
async def create_user(db: AsyncSession, user: User) -> User:
    db.add(user)
    await db.commit()
    await db.refresh(user)
    return user


# Update
async def update_user(db: AsyncSession, user: User) -> User:
    db.add(user)
    await db.commit()
    await db.refresh(user)
    return user


# Delete
async def delete_user(db: AsyncSession, user: User) -> None:
    await db.delete(user)
    await db.commit()
```

### Pagination with FastCRUD

```python
from fastcrud import FastCRUD

user_crud: FastCRUD = FastCRUD(User)

async def list_users_paginated(
    db: AsyncSession,
    page: int,
    limit: int,
    sort_by: list[str],
) -> dict:
    offset = (page - 1) * limit

    # Parse sort_by (e.g., ["created_at:desc"])
    sort_columns = []
    sort_orders = []
    for item in sort_by:
        if ":" in item:
            col, order = item.split(":", 1)
            sort_columns.append(col)
            sort_orders.append(order)

    return await user_crud.get_multi(
        db=db,
        offset=offset,
        limit=limit,
        sort_columns=sort_columns or ["created_at"],
        sort_orders=sort_orders or ["desc"],
        return_total_count=True,
    )
```

### Filtering

```python
# Simple filters
filters = {"is_active": True, "is_admin": False}

# OR conditions across fields
filters["_or"] = {
    "first_name__ilike": f"%{query}%",
    "last_name__ilike": f"%{query}%",
    "email__ilike": f"%{query}%",
}

result = await user_crud.get_multi(db=db, **filters)
```

## Best Practices

1. **Always use `expire_on_commit=False`** - Prevents lazy loading errors after commit
2. **Use `await db.refresh(obj)`** - Get updated values after commit
3. **Prefer `scalar_one_or_none()`** - For single object queries
4. **Use transactions explicitly** when needed - Default autocommit per operation
writing-skillsobra/superpowersUse when creating new skills, editing existing skills, or verifying skills work before deploymentStars: 318kUpdated Claude CodeCodexUnverifiedInstall
xlsxanthropics/skillsUse this skill any time a spreadsheet file is the primary input or output. This means any task where the user wants to: open, read, edit, or fix an existing .xlsx, .xlsm, .xltx, .csv, or .tsv file (e.g., adding columns, computing formulas, formatting, charting, cleaning messy data); create a new spreadsheet from scratch or from other data sources; or convert between tabular file formats. Trigger especially when the user references a spreadsheet file by name or path — even casually (like "the xlsx in my downloads") — and wants something done to it or produced from it. Also trigger for cleaning or restructuring messy tabular data files (malformed rows, misplaced headers, junk data) into proper spreadsheets. The deliverable must be a spreadsheet file. Do NOT trigger when the primary deliverable is a Word document, HTML report, standalone Python script, database pipeline, or Google Sheets API integration, even if tabular data is involved.Stars: 195kUpdated Claude CodeCodexVerifiedInstall
shipping-and-launchaddyosmani/agent-skillsPrepares production launches. Use when preparing to deploy to production, or when asking what needs to be in place before shipping. Use when you need a pre-launch checklist, when setting up monitoring, when planning a staged rollout, or when you need a rollback strategy.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
childhoodthedotmack/claude-memBabysit a shipped release after merge, publication, or deployment. Verify published packages, production behavior, installed plugins, and late CI or user reports until the observation window is clean; use babysit for PR readiness before release.Stars: 117kUpdated Claude CodeCodexUnverifiedInstall