class Task(SQLModel, table=True): id: Optional[int] = Field(default=None, primary_key=True) title: str = Field(index=True) description: Optional[str] = None completed: bool = Field(default=False) created_at: datetime = Field(default_factory=datetime.utcnow)


### Initialize Database

```bash
# Using provided script
python scripts/init_db.py --url postgresql://user:pass@localhost/db

# Or manually
from sqlmodel import create_engine
engine = create_engine("postgresql://user:pass@localhost/db")
SQLModel.metadata.create_all(engine)

Create Migration

# Using provided helper script
./scripts/migrate.sh create "add user table"

# Or directly with Alembic
alembic revision --autogenerate -m "add user table"
alembic upgrade head

Core Topics

1. Advanced Model Patterns

See: references/advanced-models.md

Relationships: One-to-many, many-to-many, self-referential
Inheritance: Single table, joined table, polymorphism
Validation: Pydantic validators, custom constraints
Mixins: Timestamp, soft delete, reusable patterns
Field Types: Enums, JSON, arrays, custom types
Indexes: Single, composite, partial indexes
Constraints: Unique, check, foreign key cascades

2. Comprehensive Migrations

See: references/migrations.md

Alembic Setup: Configuration, env.py for SQLModel
Creating Migrations: Autogenerate vs manual
Schema Changes: Add/drop columns, rename, change types
Data Migrations: Complex data transformations
Production Workflow: Zero-downtime migrations
Rollback Strategies: Safe downgrade patterns
Troubleshooting: Common issues and solutions

3. Query Optimization

See: references/queries-optimization.md

N+1 Problem: Solutions with eager loading
Query Patterns: Joins, aggregations, subqueries
Performance: Indexes, batch operations, profiling
Advanced Queries: Window functions, CTEs
Bulk Operations: Insert, update, delete at scale
Testing: Query counting, explain analyze

Common Patterns

One-to-Many Relationship

from typing import List
from sqlmodel import Field, Relationship, SQLModel

class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str

    # One team has many heroes
    heroes: List["Hero"] = Relationship(back_populates="team")

class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    team_id: Optional[int] = Field(foreign_key="team.id")

    # Many heroes belong to one team
    team: Optional[Team] = Relationship(back_populates="heroes")

Many-to-Many with Link Table

class HeroTeamLink(SQLModel, table=True):
    hero_id: int = Field(foreign_key="hero.id", primary_key=True)
    team_id: int = Field(foreign_key="team.id", primary_key=True)
    joined_at: datetime = Field(default_factory=datetime.utcnow)

class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    teams: List["Team"] = Relationship(
        back_populates="heroes",
        link_model=HeroTeamLink
    )

class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    heroes: List[Hero] = Relationship(
        back_populates="teams",
        link_model=HeroTeamLink
    )

Solving N+1 Query Problem

from sqlalchemy.orm import selectinload

# BAD - N+1 queries
users = session.exec(select(User)).all()
for user in users:
    posts = user.posts  # Each triggers a query!

# GOOD - Eager loading (2 queries total)
statement = select(User).options(selectinload(User.posts))
users = session.exec(statement).all()
for user in users:
    posts = user.posts  # No additional query!

Creating a Migration

# 1. Modify your model
class User(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    email: str
    phone: str  # New field added

# 2. Generate migration
# alembic revision --autogenerate -m "add phone to user"

# 3. Review generated migration
def upgrade() -> None:
    op.add_column('user', sa.Column('phone', sa.String(), nullable=True))

def downgrade() -> None:
    op.drop_column('user', 'phone')

# 4. Apply migration
# alembic upgrade head

Migration Helper Scripts

Initialize Database

python scripts/init_db.py --url postgresql://user:pass@localhost/db

Migration Operations

./scripts/migrate.sh init              # Initialize Alembic
./scripts/migrate.sh create "message"  # Create migration
./scripts/migrate.sh upgrade           # Apply migrations
./scripts/migrate.sh downgrade         # Rollback one
./scripts/migrate.sh current           # Show current
./scripts/migrate.sh history           # Show history
./scripts/migrate.sh test              # Test up & down

Example Models

Use the example models in assets/example-models.py as templates:

User model with timestamp mixin
Task model with enums and relationships
Team model with many-to-many
Tag system with link tables
Separate read/write/update models

Copy to your project:

cp assets/example-models.py your-project/app/models.py

Best Practices Checklist

Model Design

Use type hints for all fields
Separate read/write/update models
Use mixins for common fields (timestamps, soft delete)
Define indexes on foreign keys and frequently queried columns
Use enums for constrained choices
Implement proper validation with Pydantic validators

Relationships

Use back_populates for bidirectional relationships
Create explicit link tables for many-to-many
Consider cascade delete behavior
Use eager loading to prevent N+1 queries
Index foreign key columns

Migrations

Always review autogenerated migrations
One logical change per migration
Test both upgrade and downgrade
Use descriptive migration names
Never edit applied migrations
Add data migrations when changing schemas
Backup database before production migrations

Query Optimization

Use eager loading (selectinload) for relationships
Select only needed columns
Use indexes for WHERE/ORDER BY columns
Batch operations instead of loops
Profile slow queries
Use connection pooling

Troubleshooting Guide

Migration Issues

Problem: Alembic doesn't detect model changes

# Solution: Ensure models are imported in env.py
from app.models import User, Task, Team  # Import all models
target_metadata = SQLModel.metadata

Problem: Failed migration

# Check current state
alembic current

# Manually fix issue, then stamp
alembic stamp head

# Or downgrade and retry
alembic downgrade -1
alembic upgrade head

Query Performance

Problem: Slow queries

# Enable query logging
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

# Use EXPLAIN ANALYZE
explain = session.exec(text("EXPLAIN ANALYZE SELECT ...")).all()

# Profile queries
# See references/queries-optimization.md for detailed patterns

Problem: N+1 queries

# Use selectinload
statement = select(User).options(selectinload(User.posts))

# Or joinedload
from sqlalchemy.orm import joinedload
statement = select(User).options(joinedload(User.posts))

Production Workflow

Development

Modify SQLModel models
Generate migration: ./scripts/migrate.sh create "description"
Review generated migration file
Test migration: ./scripts/migrate.sh test
Commit migration file

Staging

Deploy application code
Run migrations: alembic upgrade head
Verify data integrity
Test application

Production

Backup database: pg_dump mydb > backup.sql
Deploy in maintenance window
Run migrations: alembic upgrade head
Monitor logs and metrics
Verify application functionality

Zero-Downtime Migration Strategy

For large production databases:

# Phase 1: Add new column (nullable)
def upgrade():
    op.add_column('user', sa.Column('new_email', sa.String(), nullable=True))

# Deploy app version that writes to both columns

# Phase 2: Backfill data
def upgrade():
    op.execute("UPDATE user SET new_email = email WHERE new_email IS NULL")

# Phase 3: Make non-nullable
def upgrade():
    op.alter_column('user', 'new_email', nullable=False)

# Deploy app version that reads from new column

# Phase 4: Drop old column
def upgrade():
    op.drop_column('user', 'email')

Additional Resources

Advanced Patterns: See references/advanced-models.md for inheritance, polymorphism, composite keys
Migration Guide: See references/migrations.md for Alembic mastery
Query Optimization: See references/queries-optimization.md for performance tuning

This skill provides everything needed for professional SQLModel development and database management.

sqlmodel-expert

SQLModel Expert

Quick Start

Define a Basic Model