SQLAlchemy Setup

You are a Python backend developer. The user wants to set up SQLAlchemy ORM with declarative models and configure Alembic for database migrations.

What to check first

• Run pip list | grep -i sqlalchemy to verify SQLAlchemy is installed (need version 2.0+)
• Run pip list | grep -i alembic to confirm Alembic is available
• Check if a migrations/ directory already exists in your project root

Steps

1. Install SQLAlchemy and Alembic: pip install sqlalchemy alembic psycopg2-binary (psycopg2 for PostgreSQL; use pymysql for MySQL)
2. Create a database.py file with create_engine() and sessionmaker() to establish the database connection
3. Create a models.py file and import declarative_base from sqlalchemy.orm to define your base class
4. Define model classes inheriting from Base with __tablename__ and Column definitions using Integer, String, DateTime, etc.
5. Initialize Alembic in your project: alembic init migrations to generate the alembic.ini and migrations/ directory
6. Edit migrations/env.py to import your Base metadata and set target_metadata = Base.metadata
7. Update the sqlalchemy.url in alembic.ini with your database connection string (e.g., postgresql://user:pass@localhost/dbname)
8. Generate the first migration: alembic revision --autogenerate -m "Initial schema" to detect model changes
9. Apply migrations: alembic upgrade head to create tables in the database

Code

# database.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, Session

DATABASE_URL = "postgresql://user:password@localhost/mydb"

engine = create_engine(DATABASE_URL, echo=True)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


# models.py
from sqlalchemy import Column, Integer, String, DateTime, Boolean, ForeignKey
from sqlalchemy.orm import declarative_base, relationship
from datetime import datetime

Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True, index=True)
    email = Column(String(255), unique=True, nullable=False, index=True)
    username = Column(String(100), nullable=False)
    hashed_password = Column(String(255), nullable=False)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    posts = relationship

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change