Sqlalchemy Models

Skill: sqlalchemy-models

Core position

This skill creates clean, production-ready SQLAlchemy 2.x ORM models for Python backend projects.

It enforces disciplined model patterns that prevent common problems such as:

• inconsistent base/model definitions
• broken or asymmetric relationships
• circular imports
• weak typing
• migration-hostile schema definitions
• persistence and API schema concerns getting mixed together

The skill favors explicit, typed, migration-friendly ORM design and ensures model code is:

• SQLAlchemy 2.x native
• typed
• composable
• easy to migrate
• easy to review

------------------------------------------------------------------------

Goals

Produce a model layer that:

1. Uses SQLAlchemy 2.x canonical style
2. Centralizes model infrastructure around a single DeclarativeBase
3. Defines columns and relationships with explicit typing
4. Organizes models in a predictable package structure
5. Avoids circular import traps
6. Stays compatible with Alembic autogenerate
7. Keeps ORM models separate from Pydantic/API schemas

------------------------------------------------------------------------

Step 0 — Inspect the existing project first

Before generating anything:

1. Check whether a models/ package or existing model files already exist. If

they do, extend the existing structure rather than creating a parallel one.

1. Note the existing package layout. If the project was scaffolded with

fastapi-init, the package root is {pkg_name}/{pkg_name}/ — place models at {pkg_name}/models/, not app/models/.

1. Check whether a DeclarativeBase subclass already exists anywhere. If one

does, adopt it rather than introducing a second base.

1. Check whether an Alembic env.py is present and how it imports

Base.metadata — preserve that import path.

1. Only create new files if the relevant structure is absent.

------------------------------------------------------------------------

When to use this skill

Use this skill when the user:

• wants to add SQLAlchemy models to a backend project
• needs to define new ORM entities or relationships
• wants to migrate older SQLAlchemy code to 2.x style
• has inconsistent model layout or imports
• needs timestamp/base mixins
• wants model patterns that work well with FastAPI
• needs the model layer cleaned up before adding Alembic migrations or CRUD routes

------------------------------------------------------------------------

When not to use this skill

Do not use this skill when:

• the user is asking for Pydantic request/response schemas only
• the user is not using SQLAlchemy
• the task is about Alembic migration authoring rather than model design
• the user explicitly wants a different ORM

------------------------------------------------------------------------

Non-goals

This skill does not:

• invent unrelated tables or domain entities
• generate large CRUD/service layers unless the user asks
• merge ORM models with transport schemas
• redesign the async session architecture unless required by the repo
• rewrite the database stack beyond what the current project calls for

------------------------------------------------------------------------

Required stance

When applying this skill:

• prefer minimal patches over broad rewrites
• preserve the repo's existing architectural direction when sane
• standardize on one canonical model pattern
• fix root-cause structure issues rather than layering aliases or compatibility shims
• optimize for maintainability, migration safety, and correctness over cleverness

------------------------------------------------------------------------

Preferred patterns

1) Base class

Prefer a single canonical base in db/base.py, separate from the engine and session factory. Include a naming convention so Alembic generates predictable constraint names:

from sqlalchemy import MetaData
from sqlalchemy.orm import DeclarativeBase

convention = {
    "ix": "ix_%(column_0_label)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
}

class Base(DeclarativeBase):
    metadata = MetaData(naming_convention=convention)

Model files import Base from db/base.py:

from {pkg_name}.db.base import Base

Do not create multiple unrelated declarative bases unless the repo already intentionally uses them.

------------------------------------------------------------------------

2) SQLAlchemy 2.x typed columns

Prefer:

from sqlalchemy import String
from sqlalchemy.orm import Mapped, mapped_column

email: Mapped[str] = mapped_column(String(255), unique=True, index=True)

Avoid legacy untyped declarations like:

email = Column(String, unique=True)

unless the repo is explicitly locked to an older SQLAlchemy style and the user did not ask for modernization.

------------------------------------------------------------------------

3) Primary key convention

Default to a simple explicit primary key:

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

Only introduce UUIDs or custom identifiers when the repo already uses them or there is a clear requirement.

------------------------------------------------------------------------

4) Relationship symmetry

Prefer fully paired relationships:

class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    posts: Mapped[list["Post"]] = relationship(back_populates="author")

class Post(Base):
    __tablename__ = "posts"

    id: Mapped[int] = mapped_column(primary_key=True)
    author_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    author: Mapped["User"] = relationship(back_populates="posts")

Avoid one-sided relationships unless intentionally required.

------------------------------------------------------------------------

5) Forward references to reduce import pressure

Prefer string references in relationships when models live in separate files:

posts: Mapped[list["Post"]] = relationship(back_populates="author")

This helps avoid circular imports and keeps modules loosely coupled.

------------------------------------------------------------------------

6) Explicit nullability and constraints

Be deliberate about nullability, uniqueness, indexes, and defaults.

Prefer model fields that make schema intent obvious.

Do not rely on vague or accidental defaults.

------------------------------------------------------------------------

7) Mixins

Use mixins only where they reduce obvious duplication.

Typical good candidates:

• timestamp fields
• soft-delete marker fields
• small shared utility methods

Example:

from datetime import datetime, timezone
from sqlalchemy import DateTime
from sqlalchemy.orm import Mapped, mapped_column

def utcnow() -> datetime:
    return datetime.now(timezone.utc)

class TimestampMixin:
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow)
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        default=utcnow,
        onupdate=utcnow,
    )

Do not add mixins that obscure the real model shape.

------------------------------------------------------------------------

Package structure

Prefer a predictable model package. Adapt the root to the actual project layout found in Step 0 — for fastapi-init projects this is {pkg_name}/models/, for other layouts it may be app/models/ or similar.

{pkg_name}/
  db/
    base.py             # Base, TimestampMixin, naming convention
    session.py          # engine, AsyncSessionLocal, get_db
  models/
    __init__.py
    user.py
    post.py

Where appropriate:

• Base and shared mixins live in db/base.py (separate from engine and session)
• each entity gets its own module under models/
• models/__init__.py should import all model classes so that

Base.metadata is fully populated when Alembic (or any other tool) imports it — this is what makes autogenerate reliable

Avoid dumping all models into one huge file once the project has more than a few entities.

------------------------------------------------------------------------

Import discipline

Prefer explicit imports.

Good:

from {pkg_name}.db.base import Base
from {pkg_name}.models.user import User
from {pkg_name}.models.post import Post

Avoid wildcard imports:

from {pkg_name}.models import *

Also avoid tangled cross-import chains between model modules.

------------------------------------------------------------------------

Model vs schema boundary

Keep ORM models and Pydantic schemas separate.

ORM models represent:

• persistence
• relationships
• table structure

Pydantic schemas represent:

• request validation
• response serialization
• transport contracts

Do not collapse both concerns into the same class structure.

------------------------------------------------------------------------

Alembic compatibility requirements

Model code should be written so Alembic autogenerate can reason about it cleanly.

Prefer:

• explicit table names
• explicit foreign keys
• explicit constraints where needed
• stable import paths for model metadata discovery

Avoid patterns that obscure metadata registration or hide model definitions.

------------------------------------------------------------------------

FastAPI integration stance

This skill does not redesign session management unless necessary, but it should produce model code that fits normal FastAPI backend usage.

Assume the expected separation is:

• model definitions in models/
• DB session lifecycle elsewhere
• Pydantic schemas elsewhere
• route/service layers consume ORM models without redefining them

------------------------------------------------------------------------

Review checklist

Before finishing, verify:

• all models inherit from the same Base
• columns use Mapped[...] and mapped_column(...)
• relationships are typed and symmetric where applicable
• foreign keys are explicit
• __tablename__ is defined consistently
• imports do not create obvious circular dependency risks
• model files are organized predictably
• ORM models are not mixed with request/response schema logic
• patterns are migration-friendly

------------------------------------------------------------------------

Common failure modes this skill should prevent

• legacy Column(...) style mixed inconsistently with 2.x style
• missing back_populates
• broken relationship typing
• circular imports between model files
• multiple competing base classes
• hidden metadata registration issues
• nullable/unique/index behavior implied rather than stated
• putting API serialization concerns directly into ORM model code

------------------------------------------------------------------------

Execution pattern

When using this skill, the assistant should usually:

1. Inspect the repo's existing DB/model/session conventions
2. Identify the canonical path already present or the smallest sound pattern to add
3. Normalize model definitions toward SQLAlchemy 2.x style
4. Add or clean up base/mixin structure only as much as needed
5. Keep patches compact and easy to review
6. Call out any follow-on work that belongs in adjacent skills

------------------------------------------------------------------------

Adjacent skills

This skill pairs naturally with others in this plugin and anticipated future skills. Not all of these exist yet — treat them as integration points, not dependencies.

• settings-config — database URL and other config values come from here
• pydantic-schemas — API request/response schemas that mirror (but stay

separate from) the ORM models

• alembic-migrations — migration authoring from model metadata
• pytest-service — test fixtures that use SQLite in-memory DB

A route/service layer skill is planned - check the plugin's current skill list for availability.

Typical order when building from scratch:

1. settings-config
2. sqlalchemy-models
3. pydantic-schemas
4. alembic-migrations

------------------------------------------------------------------------

Subtle rules

• Prefer canonical path enforcement: if the repo already has one clearly intended place for model infrastructure, use it rather than creating a parallel pattern.
• Prefer minimal patch first: do not reorganize every model file if a smaller change can establish a clean standard.
• Prefer verify before hand-off: sanity-check imports, typing shape, and relationship symmetry before concluding the model layer is correct.

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: robhowley
• Source: robhowley/py-pit-skills
• License: MIT

Install and usage instructions live in the source repository linked above.