Przejdź do treści
Biblioteka skilli
Programowanie · Budowanie i kod

sqlalchemy-patterns

/sqlalchemy-patterns

Podpowiada, jak w FastAPI użyć asynchronicznych sesji SQLAlchemy, wstrzykiwania zależności i migracji Alembic.

Repozytorium
claude-sdlc ↗
Licencja
MIT
Ostatnia zmiana repozytorium
26.10.2026

Ocena i bezpieczeństwo

100/ 100Klasa: dobry
Format25 / 25
Frontmatter z nazwą i opisem, poprawna nazwa, opis do 1024 znaków, nazwa zgodna z folderem.
Odkrywalność25 / 25
Opis mówi, kiedy użyć skilla, ma sensowną długość i jest w 3. osobie.
Treść35 / 35
Co najmniej 150 słów i do 500 linii, nagłówki, listy kroków, przykłady albo kod.
Higiena15 / 15
Bez zaślepek typu TODO, bez ścieżek Windows, z licencją.
Ryzykobrak sygnałów w skanie
Pliki w paczce
1
Skrypty
0
Komentarze HTML
0
Adresy URL w SKILL.md
0
Przestarzałość

Brak sygnałów. Repozytorium zmienione 26.10.2026, bez odwołań do wycofanych modeli i API.

Licencja

MIT, otwarta: można używać i zmieniać, z zachowaniem informacji o autorze.

Co robi i kiedy

Podpowiada, jak w FastAPI użyć asynchronicznych sesji SQLAlchemy, wstrzykiwania zależności i migracji Alembic.

Wyzwalacze z opisu

selectinraise
Oryginalny opis (EN)

FastAPI-specific delta on top of python-foundation:sqlalchemy-patterns: async engine and AsyncSession lifecycle, get_db dependency injection, async-safe lazy loading rules, async Alembic env.py wiring. Used by fastapi-architect (model definitions) and alembic-specialist (column type finalization and migration generation). Activated automatically by fastapi-plugin/stack.md. Use this skill to: - Manage async database sessions with AsyncSession and async_sessionmaker. - Inject sessions into routes via the get_db() dependency. - Apply async-safe lazy loading (lazy="selectin" or "raise"; never sync

Jak działa

  1. Async session setup
  2. Async querying
  3. Async lazy loading rules
  4. Alembic integration (async)
  5. Async anti-patterns
Sekcje z nagłówków SKILL.md, w oryginale · 183 linii, 638 słów
Podgląd SKILL.md
---
name: sqlalchemy-patterns
description: |
  FastAPI-specific delta on top of python-foundation:sqlalchemy-patterns: async engine and AsyncSession lifecycle, get_db dependency injection, async-safe lazy loading rules, async Alembic env.py wiring. Used by fastapi-architect (model definitions) and alembic-specialist (column type finalization and migration generation). Activated automatically by fastapi-plugin/stack.md.

  Use this skill to:
  - Manage async database sessions with AsyncSession and async_sessionmaker.
  - Inject sessions into routes via the get_db() dependency.
  - Apply async-safe lazy loading (lazy="selectin" or "raise"; never sync lazy loads).
  - Integrate async Alembic env.py for migration autogeneration.

  Do NOT use this skill for:
  - Framework-agnostic model, column, relationship, and querying rules — see python-foundation:sqlalchemy-patterns (load it first).
  - FastAPI routing and Pydantic schemas — see fastapi-plugin:fastapi-conventions.
  - Alembic migration execution (that's alembic-specialist's job) — this skill covers definitions.
user-invocable: false
paths: ["**/*.py"]
---

# SQLAlchemy Patterns for FastAPI (async delta)

**Load `python-foundation:sqlalchemy-patterns` via the Skill tool FIRST.** It contains the shared SQLAlchemy 2.0 core: detection, `Mapped`/`mapped_column` model definition, column type guidance, `select()` querying, relationship structure, lazy-strategy overview, and migration metadata rules. This skill covers only the async/FastAPI delta.

---

## Async session setup

```python
# app/db/session.py
from collections.abc import AsyncGenerator

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

from app.core.config import settings

engine = create_async_engine(
    settings.DATABASE_URL,
    echo=False,
    pool_pre_ping=True,
    pool_size=10,
    max_overflow=20,
)

AsyncSessionLocal = async_sessionmaker(
    engine,
    class_=AsyncSession,
    expire_on_commit=False,
)


async def get_db() -> AsyncGenerator[AsyncSession, None]:
    async with AsyncSessionLocal() as session:
        try:
            yield session
            await session.commit()
        except Exception:
            await session.rollback()
            raise
```

Use `expire_on_commit=False` so that model attributes remain accessible after a commit without triggering lazy loads — important in async contexts where implicit IO is not allowed.

Use `pool_pre_ping=True` to detect stale connections before use.

The `get_db()` dependency owns the transaction boundary: it commits on successful `yield` exit and rolls back on exception. Never call `session.commit()` in a router handler.

---

## Async querying

Every execution is awaited; statement construction follows the foundation skill.

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

from app.users.models import User


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


async def get_user_with_orders(db: AsyncSession, user_id: int) -> User | None:
    result = await db.execute(
        select(User)
        .options(selectinload(User.orders))
        .where(User.id == user_id)
    )
    return result.scalar_one_or_none()


async def create_user(db: AsyncSession, email: str, hashed_password: str, display_name: str) -> User:
    user = User(email=email, hashed_password=hashed_password, display_name=display_name)
    db.add(user)
    await db.flush()  # assigns id without committing; session.commit() happens in get_db()
    return user
```

---

## Async lazy loading rules

The foundation skill defines the lazy-strategy catalog; in async contexts only a subset is safe:

- `lazy="selectin"` — safe; loads collections with a separate `SELECT IN` query.
- `lazy="raise"` — safe; raises `MissingGreenlet` if accessed without explicit eager loading, forcing `selectinload()` at query time. Best for large or rarely-needed collections.
- `lazy="select"` (the SQLAlchemy default) — **forbidden**: sync lazy load raises in async context. Never leave a `relationship()` without an explicit `lazy=`.
- `lazy="subquery"` — **forbidden**: not supported by async drivers.

---

## Alembic integration (async)

### alembic.ini

Point `script_location` to the `alembic/` directory and configure the async database URL:

```ini
[alembic]
script_location = alembic
sqlalchemy.url = driver://user:pass@localhost/dbname
```

The URL in `alembic.ini` is overridden in `env.py` — do not put production credentials here.

### env.py — async pattern

```python
# alembic/env.py
import asyncio
from logging.config import fileConfig

from alembic import context
from sqlalchemy.ext.asyncio import create_async_engine

from app.core.config import settings
from app.db.base import Base  # import all models so Base.metadata is populated

config = context.config
fileConfig(config.config_file_name)

target_metadata = Base.metadata


def run_migrations_offline() -> None:
    context.configure(
        url=settings.DATABASE_URL,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )
    with context.begin_transaction():
        context.run_migrations()


async def run_async_migrations() -> None:
    connectable = create_async_engine(settings.DATABASE_URL)
    async with connectable.connect() as connection:
        await connection.run_sync(do_run_migrations)
    await connectable.dispose()


def do_run_migrations(connection):
    context.configure(connection=connection, target_metadata=target_metadata)
    with context.begin_transaction():
        context.run_migrations()


def run_migrations_online() -> None:
    asyncio.run(run_async_migrations())


if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()
```

Per the foundation skill's metadata rules, import every model before `target_metadata = Base.metadata`. The FastAPI convention is an `app/db/base.py` aggregator:

```python
# app/db/base.py
from app.db.session import Base  # noqa: F401 — must import Base
from app.users.models import User  # noqa: F401
from app.orders.models import Order, OrderLine  # noqa: F401
```

---

## Async anti-patterns

| Anti-pattern | Problem | Correct approach |
|---|---|---|
| `session.execute(select(...))` without `await` on `AsyncSession` | Implicit IO in async — raises `MissingGreenlet` | Always `await session.execute(...)` |
| `session.commit()` in a router handler | Couples transport layer to transaction lifecycle | Let the `get_db()` dependency commit on `yield` exit |
| Bare `relationship()` with no `lazy=` | Defaults to `lazy="select"` (sync lazy load) — raises in async context | Always set `lazy="selectin"` or `lazy="raise"` |
| `lazy="subquery"` on any relationship | Not supported by async drivers | `lazy="selectin"` or query-time `selectinload()` |

Wersje

RepozytoriumWynikLicencja
Ta strona · aratkruglik/claude-sdlc100MIT
aratkruglik/claude-sdlc87MIT
aratkruglik/claude-sdlc87MIT

Migawka bazy z 13.11.2026 · wersja paczki 30aff3d5be00 (skrót SHA-256 całej paczki)

Pobrania w ostatnich 30 dniach: 0 · ranking

Zgłoś problem z tym skillem: szkodliwa treść, błędny opis albo prośba autora o usunięcie.

Podobne skille

  • 100sqlalchemy
    Pomaga w asynchronicznym SQLAlchemy 2.0: modele, zapytania, sesje i migracje Alembic bez zapytań N+1.
  • 100sqlalchemy
    Pomaga pisać asynchroniczny kod SQLAlchemy 2.0, czyli sesje, relacje ładowane bez N+1 i migracje Alembic.
  • 100fastapi-pro
    Podaje produkcyjne wzorce FastAPI dla Pythona 3.11+: endpointy async, SQLAlchemy 2.0 async, Pydantic V2, wstrzykiwanie zależności, JWT i testy.
  • 100alembic-migrations
    Konfiguruje migracje Alembic dla asynchronicznego SQLAlchemy: async engine z run_sync w env.py, autogenerate i migracje RLS dla wielu najemców.
  • 90sqlalchemy-code-review
    Przegląda kod SQLAlchemy pod kątem zarządzania sesją, relacji, zapytań N+1 i wzorców migracji z Alembic.
  • 85sqlalchemy-code-review
    Przegląda kod SQLAlchemy pod kątem zarządzania sesją, relacji, zapytań N+1 i migracji Alembic, zwłaszcza w wersji 2.0.

Uwagi i zgłoszenia

Wiadomość trafi do Bartosza Sokolińskiego. Adres e-mail jest potrzebny tylko do odpowiedzi. Szczegóły w polityce prywatności.