Skills Guide

SQLAlchemy & Alembic Schema Evolution

VerifiedSafe

Handle SQLAlchemy model changes, constraints, indexes and Alembic revisions: generate, review, and test migrations and data transforms safely.

Sby Skills Guide Bot
DevelopmentAdvanced
2010/18/2026
Claude CodeCursorCodex
#sqlalchemy#alembic#database-migrations#schema-evolution#backend

Recommended for

Code reviewClaude CodeBackend developerCursorFrontend developerFullstack developer

Our review

Governs database schema evolution: SQLAlchemy models, constraints, indexes, Alembic revisions and transformations of existing data.

Strengths

  • Covers the whole chain: generating the migration, reviewing SQL and foreign-key ordering, testing on an empty database and on the previous revision, and confirming model parity.
  • Enforces concrete guardrails: a single integrated Alembic head, data-preservation assertions, and treating autogeneration or create_all as insufficient evidence.
  • Spells out project conventions (YYYYMMDD_NNNNNN revision ids, explicit constraint naming, skipping directus_* tables) that keep later revisions deterministic.
  • Connects the change to neighbouring skills (openapi-sync, backend-architecture, validate-change) and plans compatible deployment phases.

Limitations

  • Tightly coupled to one project layout (`just backend` recipes, backend/src/common/database/ paths), so it is not directly portable.
  • Assumes prior familiarity with Alembic and SQLAlchemy; the steps stay procedural rather than teaching the underlying concepts.
  • Says little about destructive downgrade strategies or multi-database setups beyond what the project profile defines.
When to use it

Use it whenever a change adds or alters tables, columns, relationships, constraints, indexes or stored data in a SQLAlchemy/Alembic codebase.

When not to use it

Do not use it for DTO-, Pydantic- or zod-only edits, or for work that touches neither the schema nor persisted data.

Security analysis

Safe
Quality score92/100

The skill provides structured guidance for database schema changes using project-specific 'just' commands and Alembic workflows. It does not instruct destructive, exfiltrating, obfuscated, or safety-disabling actions. The referenced commands operate on disposable databases and owned resources only, with explicit warnings against unsafe downgrades and stale migrations.

No concerns found

Examples

Add a column with a unique constraint
Add a nullable stripe_customer_id column with a unique constraint to the customers table, update the SQLAlchemy model, and generate plus review the Alembic revision.
Merge conflicting migration heads
We have two Alembic heads after the last merge; collapse them into a single integrated head, renumber the revisions to the next global sequence, and verify with just backend check-migrations.
Backfill data in a migration
Write a migration that backfills the new plan_tier column from the legacy subscription field, and add assertions that existing values are preserved on the previous revision.

name: schema-change description: Evolve SQLAlchemy models, constraints, indexes, Alembic revisions or data transformations. Use when a change adds or alters tables, columns, relationships, migrations or stored data. DTO, Pydantic or zod-only edits do not activate this workflow.

schema-change

Read the project profile, affected data criteria, central ORM models (backend/src/common/database/models/) and the Alembic graph. Coordinate with backend-change and consumers when HTTP contracts change.

Steps

  1. Generate with just backend new-migration "description" when appropriate, then deliberately review SQL, FK ordering, constraints and data transforms. Merge conflicting revisions through Alembic's graph workflow.
  2. Test an empty database and the supported previous revision with representative data. Use just backend check-migrations; it owns disposable databases, upgrades and drift checks. Add data-preservation assertions when a migration transforms existing values.
  3. Confirm an unambiguous integrated head, model parity and preserved data. Plan compatible deployment phases when API, frontend and schema do not update simultaneously. When the change reaches DTOs or routes, refresh the API snapshot with openapi-sync. Place shared domain models as backend-architecture describes.
  4. Record the structural decision in an ADR and the technical evidence on the exact integrated revision. Return findings to the implementer; validate-change checks evolution and compatibility acceptance. Clean only owned resources.

Gotchas

  • Autogeneration is a starting point, not a reviewed migration.
  • metadata.create_all and migrate-current are not migration evidence.
  • Do not require a destructive downgrade by default.
  • Run just backend migrate before just backend new-migration; autogenerate diffs against the local database, so a stale database yields a wrong revision.
  • Base.metadata has no naming_convention: name new constraints and indexes explicitly (name="uq_<table>_<cols>", ix_..., fk_...) so later revisions can drop them deterministically.
  • Revision ids follow YYYYMMDD_NNNNNN (date plus a global sequence, e.g. 20260907_000002) with files named <revision>_<slug>.py in backend/src/common/database/versions/. The recipe's file_template produces a timestamped name with a random hex id, so set revision and rename the file to the next sequence before review.
  • env.py include_object skips directus_* tables; never write migrations for them, and do not read their absence from autogenerate as drift.
Related skills

Next.js App Router Expert

Development

A skill that turns Claude into a Next.js App Router expert.

Claude CodeCursoradvanced
890
234
3,819
Admin

README Generator

Development

Creates professional and comprehensive README.md files for your projects.

claudeCursorWindsurfbeginner
259
72
1,711
Admin

API Documentation Writer

Development

Generates comprehensive API documentation in OpenAPI/Swagger format.

claudeCursorWindsurfintermediate
156
44
1,580
Admin