| 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.
|
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:
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.
from flask_migrate import Migrate
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
migrate = Migrate()
Initialize in the factory:
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.
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)
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.
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()
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.
@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:
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)
with app.app_context():
from app.users import models as _user_models
from app.orders import models as _order_models
_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 |
