Database Migration (SQLModel + Alembic)

Overview

Automate database migration workflows for SQLModel-based projects using Alembic. This skill enforces strict rules: SQLModel models are the single source of truth, migrations are generated automatically only (never manual), and all migrations must include

import sqlmodel

for proper functioning.

When to Use This Skill

Trigger this skill when the user requests:

• "Create a migration for the new User model"
• "Generate migration after I updated Task fields"
• "I added a column to Topic, make a migration"
• "Apply pending migrations to the database"
• Any task involving database schema changes in SQLModel projects

Core Principles

1. SQLModel as Single Source of Truth

• NEVER modify database schema directly (via SQL, pgAdmin, psql, etc.)
• ALWAYS modify SQLModel model classes first, then generate migrations
• Database must reflect SQLModel models exactly

2. Automatic Generation Only

• ALWAYS use

  just alembic-auto -m "description"

  to generate migrations
• NEVER create empty manual migrations
• NEVER write custom SQL without explicit user approval

3. Required Import Fix

Alembic often omits

import sqlmodel

, causing errors with SQLModel types. ALWAYS add this import after generation.

Workflow

Step 1: Pre-Generation Safety Checks

Before generating any migration, verify prerequisites:

1. Check PostgreSQL is running:

   docker compose ps postgres
   

   If not running, start it:

   docker compose up -d postgres

2. Verify model imports in

   backend/app/models/__init__.py

   :
   • Read the file and confirm new/modified models are imported
   • Add missing imports if needed

3. Type check for syntax errors:

   just typecheck
   

   Fix any errors before proceeding

Step 2: Generate Migration

Use the automatic migration generation command:

just alembic-auto -m "descriptive message"

Message conventions:

• ✅

  "add User table with authentication fields"

• ✅

  "update Task: add priority and status fields"

• ✅

  "add foreign key relationship Task.user_id → User.id"

• ❌

  "migration"

  (too vague)
• ❌

  "changes"

  (not descriptive)

Step 3: Post-Generation Fixes (MANDATORY)

Critical step - never skip this:

1. Locate the migration file:

   • Alembic prints the path in output:

     backend/alembic/versions/{revision_id}_{slug}.py

   • Example:

     backend/alembic/versions/abc123def456_add_user_table.py

2. Read the migration file completely

3. Check for

   import sqlmodel

   :
   • Look in the import section (after

     import sqlalchemy as sa

     )
   • If

     import sqlmodel

     is missing, add it:

   """add User table
   
   Revision ID: abc123def456
   Revises: previous_revision
   Create Date: 2024-01-01 12:00:00.000000
   
   """
   from alembic import op
   import sqlalchemy as sa
   import sqlmodel  # ← ADD THIS IF MISSING
   
   # revision identifiers
   revision = 'abc123def456'
   down_revision = 'previous_revision'
   branch_labels = None
   depends_on = None
   

4. Validate migration operations:

   • Review

     upgrade()

     and

     downgrade()

     functions
   • Check operations match expected model changes
   • Warn user about destructive operations (DROP COLUMN, DROP TABLE)

Step 4: Present Migration for Review

Provide clickable file link for user review:

1. Provide file path as clickable link (format:

   backend/alembic/versions/{filename}.py:1

   )
2. Summarize key changes from

   upgrade()

   and

   downgrade()

   functions
3. Call out any potentially destructive operations (DROP COLUMN, DROP TABLE)
4. Wait for explicit user confirmation before applying

DO NOT paste full migration contents in chat - provide file link only.

Step 5: Apply Migration (After Confirmation Only)

Only proceed after user explicitly confirms:

just alembic-up

NEVER apply migrations without user approval.

Common Scenarios

Creating New Model

User adds a new SQLModel in

backend/app/models/

:

1. Verify model is imported in

   backend/app/models/__init__.py

2. Run pre-generation checks (PostgreSQL, type check)
3. Generate:

   just alembic-auto -m "add {ModelName} table"

4. Read migration file
5. Add

   import sqlmodel

   if missing
6. Provide clickable file link and summarize changes
7. Apply after confirmation:

   just alembic-up

Modifying Existing Model

User changes fields in an existing SQLModel:

1. Run pre-generation checks
2. Generate:

   just alembic-auto -m "update {ModelName}: {change description}"

3. Read migration file
4. Add

   import sqlmodel

   if missing
5. Provide clickable file link and summarize changes
6. Apply after confirmation:

   just alembic-up

Multiple Model Changes

User modifies several models at once:

1. Group related changes into logical units
2. Generate one migration per logical change (preferred)
3. OR generate single migration for all changes if closely related
4. Follow standard workflow for each migration

Error Handling

"NameError: name 'sqlmodel' is not defined"

Cause: Missing

import sqlmodel

in migration Fix: Edit migration file, add

import sqlmodel

to imports

"Target database is not up to date"

Cause: Pending unapplied migrations exist Fix: Run

just alembic-up

to apply pending migrations before generating new ones

"Can't proceed with autogenerate"

Cause: Models not imported or database inaccessible Fix:

1. Check

   backend/app/models/__init__.py

   for missing imports
2. Verify:

   docker compose ps postgres

3. Check database connection settings

Extending SQLModel

⚠️ Important: Always prefer native SQLModel features first. Only use SQLAlchemy extensions when SQLModel doesn't support the feature.

When SQLModel lacks native support for a feature (e.g., PostgreSQL JSON/ARRAY, CHECK constraints, composite unique constraints), extend via SQLAlchemy:

from sqlmodel import Field, Column
from sqlalchemy import JSON, CheckConstraint

class Model(SQLModel, table=True):
    # PostgreSQL JSON type
    data: dict = Field(sa_column=Column(JSON, nullable=False))

    # CHECK constraint
    rating: int = Field(
        sa_column=Column(Integer, CheckConstraint('rating >= 1 AND rating <= 5'))
    )

See

references/sqlmodel-migration-rules.md

→ "Extending SQLModel with SQLAlchemy" for full examples.

Safety Rules (STRICT)

Forbidden Without User Approval

• Creating manual migrations (empty migrations)
• Modifying database schema directly (SQL commands, GUI tools)
• Editing already-applied migrations (in

  alembic_version

  )
• Running custom SQL in migrations
• Applying migrations without providing file link and summary first
• Removing

  import sqlmodel

  from migrations

Always Required

• Generate migrations using

  just alembic-auto

• Add

  import sqlmodel

  to every migration if missing
• Provide clickable file link and summarize changes before applying
• Wait for explicit user confirmation before

  alembic-up

• Verify PostgreSQL is running before generation

References

For detailed information about SQLModel types, migration patterns, and comprehensive rules, refer to:

• references/sqlmodel-migration-rules.md

  - Complete migration rules and type mappings

Quick Reference

# Pre-generation checks
docker compose ps postgres          # Verify DB is running
just typecheck                      # Check for syntax errors

# Generate migration
just alembic-auto -m "description"  # Create migration

# Apply migration (after review)
just alembic-up                     # Apply to database

# Common debugging
docker compose logs postgres        # Check DB logs
docker compose restart postgres     # Restart if needed

Workflow Summary

1. ✅ Pre-checks: PostgreSQL running, models imported, no syntax errors
2. ✅ Generate:

   just alembic-auto -m "description"

3. ✅ Read migration file completely
4. ✅ Add

   import sqlmodel

   if missing
5. ✅ Provide file link (format:

   file.py:1

   ) and summarize changes
6. ⏸️ WAIT for user confirmation
7. ✅ Apply:

   just alembic-up

   (only after confirmation)

Remember: SQLModel is truth, autogenerate only, always add

import sqlmodel

, provide file links (not full contents), never apply without approval.