sqlalchemy-patterns

name

sqlalchemy-patterns

description

SQLAlchemy ORM patterns for Flask: Flask-SQLAlchemy extension setup, declarative models with db.Model, synchronous db.session queries, relationships with explicit lazy loading, Flask-Migrate integration for schema migrations. 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: - Write Flask-SQLAlchemy models with db.Model base and properly typed columns. - Query the database with db.session and SQLAlchemy 2.0-style select() statements. - Define relationships with explicit lazy loading strategy. - Integrate Flask-Migrate for Alembic-based migrations managed via flask db commands. Do NOT use this skill for: - 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. - Python idioms — see python-foundation:python-conventions.

SQLAlchemy Patterns for Flask

Detection

Read pyproject.toml or requirements.txt before writing any model code:

grep -E "flask.sqlalchemy|sqlalchemy" requirements.txt pyproject.toml

Determine the Flask-SQLAlchemy version:

• 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.

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

db = SQLAlchemy()
migrate = Migrate()

Initialize in the factory:

# 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

Flask-SQLAlchemy 3.x with Mapped (preferred for new code)

from datetime import datetime
from decimal import Decimal
from typing import Optional

from sqlalchemy import DateTime, ForeignKey, Numeric, 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)
    display_name: Mapped[str] = mapped_column(String(100))
    hashed_password: Mapped[str] = mapped_column(String(255))
    is_active: Mapped[bool] = mapped_column(default=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"
    )

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

Key rules:

• Mapped[T] without Optional means NOT NULL. Mapped[Optional[T]] means nullable.
• Always provide the SQLAlchemy type explicitly (e.g., String(255)) — flask-migrate-specialist uses this to finalize column lengths, precision, and constraints.
• Use server_default=func.now() for database-side default timestamps, not default=datetime.utcnow (Python-side defaults are not reflected in DB schema).

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}>"

---

Column type guidance

Python type	SQLAlchemy column type	Notes
str	String(N)	Always set length; never bare String
Decimal	Numeric(precision, scale)	Never Float for money or precise values
datetime	DateTime(timezone=True)	Always set timezone=True
int	Integer or BigInteger	Use BigInteger for large tables (users, events)
bool	Boolean
UUID	Uuid (SA 2.0+) or String(36)	Uuid stores as native UUID on PostgreSQL
enum	Enum(MyEnum, native_enum=False)	native_enum=False for DB portability
float	Float	Only for non-monetary approximations (lat/lon, scores)

---

Querying patterns (synchronous)

Flask-SQLAlchemy uses synchronous sessions. All queries are blocking — no await, no async generators.

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 list_users(skip: int = 0, limit: int = 20) -> list[User]:
    return list(
        db.session.execute(select(User).offset(skip).limit(limit)).scalars().all()
    )


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.

@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

---

Relationships

Define relationships with explicit lazy and cascade settings.

from sqlalchemy.orm import Mapped, mapped_column, relationship


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

    id: Mapped[int] = mapped_column(primary_key=True)

    # One-to-many: user has many orders
    # lazy="select" — loads orders with a separate SELECT when accessed (default)
    orders: Mapped[list["Order"]] = relationship(
        "Order",
        back_populates="user",
        lazy="select",
        cascade="all, delete-orphan",
    )

    # One-to-one: user has one profile
    # lazy="joined" — loads profile with a JOIN in the same query
    profile: Mapped[Optional["UserProfile"]] = relationship(
        "UserProfile",
        back_populates="user",
        lazy="joined",
        uselist=False,
        cascade="all, delete-orphan",
    )


class Order(db.Model):
    __tablename__ = "orders"

    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"))

    user: Mapped["User"] = relationship("User", back_populates="orders", lazy="select")
    lines: Mapped[list["OrderLine"]] = relationship(
        "OrderLine",
        back_populates="order",
        lazy="select",
        cascade="all, delete-orphan",
    )

Lazy loading strategy guide (synchronous Flask context):

• lazy="select" — loads the related collection with a separate SELECT when the attribute is first accessed. This is the SQLAlchemy default and is safe in synchronous Flask (unlike async FastAPI, where it can block the event loop).
• lazy="joined" — loads the relation with a JOIN in the same query. Best for one-to-one relations or small, always-needed collections.
• lazy="subquery" — loads the relation with a subquery. Valid in synchronous Flask (unlike async where it is not supported). Useful for loading collections alongside the parent.
• lazy="dynamic" — deprecated in SQLAlchemy 2.0. Do not use. Replace with explicit select() queries.

To prevent N+1 queries on list endpoints, use options(joinedload(...)) or options(selectinload(...)) at query time:

from sqlalchemy.orm import joinedload, selectinload


def list_users_with_orders() -> list[User]:
    return list(
        db.session.execute(
            select(User).options(selectinload(User.orders))
        ).scalars().all()
    )

---

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

For autogenerate to detect all models, Flask-Migrate's env.py must import all model modules before target_metadata = db.metadata. A common pattern is to import all models in app/models/__init__.py or in the app factory before db.init_app(app):

# 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.

---

Anti-patterns

Anti-pattern	Problem	Correct approach
String without length	Alembic autogenerate produces VARCHAR with no length; PostgreSQL uses TEXT	Always String(N)
lazy="dynamic"	Deprecated in SQLAlchemy 2.0; raises a warning	Use lazy="select" with explicit selectinload() for large collections
Float for monetary values	IEEE 754 rounding errors on financial calculations	Numeric(precision, scale)
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
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
default=datetime.utcnow	Python-side default — not reflected in DB schema; utcnow is deprecated	server_default=func.now() with DateTime(timezone=True)
Mixing db.Column() and mapped_column()	Produces inconsistent metadata; confuses tooling	Use one style per project; prefer mapped_column() for new code