sqlalchemy-patterns

SQLAlchemy Patterns and Conventions

Overview

SQLAlchemy 2.0 conventions for the SilverKey backend: typed ORM models under Server/app/models/, API serialization via Server/app/dtos/.

Forbidden in app/models/

• db.Column — use Mapped[T] = mapped_column(...)
• backref= — use back_populates with typed Mapped / DynamicMapped on both sides
• to_dict() on ORM classes — use app/dtos/* (see app/dtos/README.md)

CI blocks db.Column and backref= via Server/scripts/lint/lint_sqlalchemy_legacy_models.py.

Forbidden query APIs (app/, tests/, scripts/)

• Model.query.* and db.session.query(...) — use select() + db.session.scalar / scalars / execute

CI blocks legacy query usage via Server/scripts/lint/lint_legacy_orm_query.py.

Model Definitions

Basic Model Structure

from __future__ import annotations

from datetime import datetime, timezone

from sqlalchemy import Index
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app import db


class MyModel(db.Model):
    """Brief description of what this model represents."""
    __tablename__ = "my_models"

    id: Mapped[str] = mapped_column(db.String(36), primary_key=True)
    user_id: Mapped[str] = mapped_column(
        db.String(36), db.ForeignKey("users.id"), nullable=False, index=True
    )
    name: Mapped[str] = mapped_column(db.String(255), nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        default=lambda: datetime.now(timezone.utc), nullable=False, index=True
    )
    updated_at: Mapped[datetime] = mapped_column(
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc),
        nullable=False,
    )

    user: Mapped["User"] = relationship("User", back_populates="my_models")
    items: Mapped[list["Item"]] = relationship(
        "Item", back_populates="my_model", cascade="all, delete-orphan"
    )

    __table_args__ = (Index("idx_user_created", "user_id", "created_at"),)