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

sqlalchemy-patterns

/sqlalchemy-patterns

Podpowiada, jak konfigurować Flask-SQLAlchemy, pisać modele db.Model i obsługiwać sesję oraz migracje Flask-Migrate.

Repozytorium
claude-sdlc ↗
Licencja
MIT
Ostatnia zmiana repozytorium
26.10.2026

Ocena i bezpieczeństwo

87/ 100Klasa: dobry
Format20 / 25
Frontmatter z nazwą i opisem, poprawna nazwa, opis do 1024 znaków, nazwa zgodna z folderem.
Odkrywalność17 / 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 konfigurować Flask-SQLAlchemy, pisać modele db.Model i obsługiwać sesję oraz migracje Flask-Migrate.

Oryginalny opis (EN)

Flask-specific delta on top of python-foundation:sqlalchemy-patterns: Flask-SQLAlchemy extension setup, db.Model declarative models (3.x Mapped style and 2.x legacy db.Column), synchronous db.session lifecycle bound to the app context, Flask-Migrate integration. Used by flask-architect (model definitions) and flask-migrate-specialist (column finalization and migration). Activated automatically by flask-plugin/stack.md. Use this skill to: - Set up the SQLAlchemy and Migrate extensions with the app-factory pattern. - Write Flask-SQLAlchemy models with the db.Model base. - Query with db.session a

Jak działa

  1. Version detection
  2. Extension setup
  3. Model definition with db.Model
  4. Session lifecycle and querying (synchronous)
  5. Lazy loading in synchronous Flask
  6. Flask-Migrate integration
  7. Flask-specific anti-patterns
Sekcje z nagłówków SKILL.md, w oryginale · 210 linii, 885 słów
Podgląd SKILL.md
---
name: sqlalchemy-patterns
description: |
  Flask-specific delta on top of python-foundation:sqlalchemy-patterns: Flask-SQLAlchemy extension setup, db.Model declarative models (3.x Mapped style and 2.x legacy db.Column), synchronous db.session lifecycle bound to the app context, Flask-Migrate integration. Used by flask-architect (model definitions) and flask-migrate-specialist (column finalization and migration). Activated automatically by flask-plugin/stack.md.

  Use this skill to:
  - Set up the SQLAlchemy and Migrate extensions with the app-factory pattern.
  - Write Flask-SQLAlchemy models with the db.Model base.
  - Query with db.session and manage the request-scoped session lifecycle.
  - Integrate Flask-Migrate for Alembic-based migrations managed via flask db commands.

  Do NOT use this skill for:
  - Framework-agnostic model, column, relationship, and querying rules — see python-foundation:sqlalchemy-patterns (load it first).
  - Flask routing and template/API patterns — see flask-plugin:flask-conventions.
  - Migration execution (flask db migrate, flask db upgrade) — that's flask-migrate-specialist's job.
user-invocable: false
paths: ["**/*.py"]
---

# SQLAlchemy Patterns for Flask (sync 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 Flask-SQLAlchemy delta.

---

## Version detection

Beyond the foundation skill's SQLAlchemy detection, determine the Flask-SQLAlchemy version:

```bash
grep -E "flask.sqlalchemy" requirements.txt pyproject.toml
```

- **Flask-SQLAlchemy 3.x** (released 2022+): Uses SQLAlchemy 2.0 under the hood. Supports `Mapped`/`mapped_column` style with `db.Model`. This is the baseline for new projects.
- **Flask-SQLAlchemy 2.x** (legacy): Uses `db.Column()` style. Still common in existing projects.

Always use **Flask-SQLAlchemy 3.x style** for new code. When working in an existing project using 2.x style throughout, match the existing style. Never mix `db.Column()` and `mapped_column()` in the same model.

---

## Extension setup

Define extensions at module level in `app/extensions.py` and call `.init_app(app)` in the factory. This avoids circular imports and allows multiple app instances for testing.

```python
# app/extensions.py
from flask_migrate import Migrate
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()
migrate = Migrate()
```

Initialize in the factory:

```python
# app/__init__.py
from app.extensions import db, migrate


def create_app(config_name: str = "development") -> Flask:
    app = Flask(__name__)
    app.config.from_object(config_by_name[config_name])

    db.init_app(app)
    migrate.init_app(app, db)

    return app
```

---

## Model definition with db.Model

Models inherit from `db.Model` instead of a hand-written `DeclarativeBase`; all mapped-column, typing, and relationship rules from the foundation skill apply unchanged.

```python
from datetime import datetime

from sqlalchemy import DateTime, String, func
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.extensions import db


class User(db.Model):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String(255), unique=True, index=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )

    orders: Mapped[list["Order"]] = relationship(
        "Order", back_populates="user", lazy="select"
    )
```

### Flask-SQLAlchemy 2.x with db.Column (legacy)

```python
from app.extensions import db


class User(db.Model):
    __tablename__ = "users"

    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(255), unique=True, nullable=False)
    display_name = db.Column(db.String(100), nullable=False)
    hashed_password = db.Column(db.String(255), nullable=False)
    is_active = db.Column(db.Boolean, default=True)
    created_at = db.Column(db.DateTime(timezone=True), server_default=db.func.now())

    orders = db.relationship("Order", back_populates="user", lazy="select")

    def __repr__(self) -> str:
        return f"<User id={self.id} email={self.email!r}>"
```

---

## Session lifecycle and querying (synchronous)

Flask-SQLAlchemy uses synchronous sessions. All queries are blocking — no `await`, no async generators. Statement construction follows the foundation skill; execution goes through `db.session`.

```python
from sqlalchemy import select

from app.extensions import db
from app.users.models import User


def get_user_by_id(user_id: int) -> User | None:
    return db.session.get(User, user_id)


def get_user_by_email(email: str) -> User | None:
    return db.session.execute(
        select(User).where(User.email == email)
    ).scalar_one_or_none()


def create_user(email: str, hashed_password: str, display_name: str) -> User:
    user = User(email=email, hashed_password=hashed_password, display_name=display_name)
    db.session.add(user)
    db.session.flush()  # assigns id without committing
    return user


def delete_user(user: User) -> None:
    db.session.delete(user)
```

**Session lifecycle in Flask:** Flask-SQLAlchemy automatically calls `db.session.remove()` at the end of each request via `teardown_appcontext`. This closes the session and returns the connection to the pool. You do not need to call `db.session.close()` manually in view functions.

**When to commit:** call `db.session.commit()` in the view function or service after all writes are complete for the request. Use `db.session.flush()` inside a unit of work to get the generated PK without committing.

```python
@orders_bp.route("/", methods=["POST"])
@login_required
def create_order_view():
    data = order_create_schema.load(request.get_json())
    order = create_order(data)
    db.session.commit()
    return jsonify(order_schema.dump(order)), 201
```

---

## Lazy loading in synchronous Flask

The foundation skill's lazy-strategy catalog applies; in synchronous Flask the sync-only strategies are also valid:

- `lazy="select"` — the SQLAlchemy default; loads on first attribute access with a separate `SELECT`. Safe in synchronous Flask (unlike async FastAPI, where it can block the event loop).
- `lazy="joined"` — JOIN in the same query; best for one-to-one relations or small, always-needed collections.
- `lazy="subquery"` — valid in synchronous Flask (unlike async where it is not supported). Useful for loading collections alongside the parent.
- `lazy="dynamic"` — deprecated in 2.0, do not use (see foundation skill).

For N+1 prevention on list endpoints, use query-time `options(joinedload(...))` or `options(selectinload(...))` as shown in the foundation skill.

---

## Flask-Migrate integration

Flask-Migrate wraps Alembic. Running `flask db init` scaffolds the `migrations/` directory including `migrations/env.py` and `migrations/alembic.ini`. Do not hand-write `env.py` — Flask-Migrate generates a synchronous configuration automatically.

```
migrations/
    alembic.ini
    env.py          — generated by flask db init; imports db.metadata
    script.py.mako
    versions/       — generated migration scripts live here
```

Per the foundation skill's metadata rules, all model modules must be imported before `target_metadata = db.metadata`. The Flask convention is to import them in `app/models/__init__.py` or in the app factory:

```python
# app/__init__.py
def create_app(config_name: str = "development") -> Flask:
    app = Flask(__name__)
    app.config.from_object(config_by_name[config_name])

    db.init_app(app)
    migrate.init_app(app, db)

    # Import models so Flask-Migrate sees them in db.metadata
    with app.app_context():
        from app.users import models as _user_models  # noqa: F401
        from app.orders import models as _order_models  # noqa: F401

    _register_blueprints(app)
    return app
```

Flask-migrate-specialist runs `flask db migrate` and `flask db upgrade`. Flask-architect only **defines the models**. Never call `flask db` commands from flask-architect.

---

## Flask-specific anti-patterns

| Anti-pattern | Problem | Correct approach |
|---|---|---|
| `db.session.commit()` in every helper function | Makes unit testing harder; scattered transaction boundaries | Commit at the end of the request in the view or service layer |
| Mixing `db.Column()` and `mapped_column()` | Produces inconsistent metadata; confuses tooling | One style per project; prefer `mapped_column()` for new code |
| Importing models only in routers | Flask-Migrate's `env.py` never sees them; `--autogenerate` misses tables | Import all models in the app factory or `app/models/__init__.py` |

Wersje

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

Migawka bazy z 13.11.2026 · wersja paczki e05e7accf6dd (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

  • 100flask
    Pomaga budować aplikacje i API JSON we Flasku: fabryka aplikacji z blueprintami, Flask-SQLAlchemy, Flask-Migrate, Flask-Login, testy pytest oraz uruchamianie przez flask CLI i gunicorn.
  • 100flask
    Wspiera tworzenie aplikacji we Flask: fabryka aplikacji, blueprinty, routing, kontekst żądania, rozszerzenia (SQLAlchemy, Login, WTF), obsługa błędów, testy i wdrożenie.
  • 100flask
    Pomaga w pracy z Flaskiem: Blueprints, fabryki aplikacji, szablony Jinja2 i rozszerzenia SQLAlchemy przy lekkich usługach webowych w Pythonie.
  • 100flask
    Wspiera tworzenie prostych API i mikroserwisów w Pythonie z użyciem Flaska, blueprintów i rozszerzeń takich jak flask-restful, flask-sqlalchemy i flask-jwt.
  • 100enferno-dev
    Pomaga implementować funkcje i pisać kod we frameworku Enferno Flask: blueprinty, modele SQLAlchemy, migracje, API administracyjne JSON i zadania Celery.
  • 90flask-backend
    Buduje backend we Flasku z blueprintami, wzorcem fabryki aplikacji, rozszerzeniami, szablonami Jinja2 i ORM SQLAlchemy.

Uwagi i zgłoszenia

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