sqlalchemy-models

SQLAlchemy Models and Migrations

context7: If the mcp_context7 tool is available, load the full sqlalchemy and alembic documentation before debugging, creating or modifying models or queries, or writing migrations:

mcp_context7_resolve-library-id: "sqlalchemy"
mcp_context7_get-library-docs: <resolved-id>
mcp_context7_resolve-library-id: "alembic"
mcp_context7_get-library-docs: <resolved-id>

Guidelines and patterns for database models, queries, and Alembic migrations in this codebase.

---

Core Requirements

• Always use async SQLAlchemy APIs (never synchronous).
• Always use SQLAlchemy 2.0 syntax.
• Represent database tables with the declarative class system (Base subclass).
• Use Alembic for all schema changes — never alter the schema manually.
• Migrations must be compatible with both SQLite and PostgreSQL.

---

Model Definition

Models live in {{cookiecutter.__package_slug}}/models/. Import and extend the shared Base from {{cookiecutter.__package_slug}}.models.base.

from uuid import UUID, uuid4

from sqlalchemy.orm import Mapped, mapped_column

from {{cookiecutter.__package_slug}}.models.base import Base


class User(Base):
    __tablename__ = "users"

    id: Mapped[UUID] = mapped_column(primary_key=True, default=uuid4)
    email: Mapped[str] = mapped_column(unique=True)
    name: Mapped[str]
    is_active: Mapped[bool] = mapped_column(default=True)

Typing Conventions

Pattern	Meaning
Mapped[str]	NOT NULL column
Mapped[str | None]	NULLable column
mapped_column(default=...)	Server-side / Python-side default
mapped_column(primary_key=True)	Primary key
mapped_column(unique=True)	Unique constraint
mapped_column(index=True)	Index