petpicturequeue/migrations

Database Migrations

This directory contains database migrations for the Pet Picture Queue application.

Migration System

The migration system provides:

• ✅ Ordered execution - Migrations run in filename order (001_, 002_, etc.)
• ✅ Tracking - Applied migrations are tracked in the database
• ✅ Idempotent - Safe to run multiple times
• ✅ Rollback support - Individual migrations can implement rollback logic

Usage

Run All Pending Migrations

# From project root
python migrations/migrate.py

# Or make it executable and run directly
chmod +x migrations/migrate.py
./migrations/migrate.py

Run Individual Migration

python migrations/001_add_description_column.py

Migration Structure

Each migration file should follow this pattern:

#!/usr/bin/env python3
"""
Migration XXX: Description of what this migration does
Date: YYYY-MM-DD
Description: Detailed explanation
"""

import sqlite3
import sys
from datetime import datetime

def migrate_up(cursor):
    """Apply the migration"""
    # Your migration code here
    cursor.execute("ALTER TABLE ...")
    return True  # or False if skipped

def migrate_down(cursor):
    """Rollback the migration (optional)"""
    # Rollback code here
    return True

def main():
    """Run migration standalone"""
    # Standard standalone execution code
    pass

if __name__ == "__main__":
    sys.exit(main())

Existing Migrations

001_add_description_column.py

• Purpose: Add description field to pet_pictures table
• Date: 2025-08-08
• Changes: Adds description TEXT NULL column

Migration Naming Convention

• Use 3-digit numbers: 001_, 002_, 003_, etc.
• Descriptive names: add_description_column, create_user_table
• Full format: XXX_descriptive_name.py

Database Schema Tracking

The migration system creates a migrations table to track applied migrations:

CREATE TABLE migrations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    migration_name TEXT NOT NULL UNIQUE,
    applied_at TIMESTAMP NOT NULL
);

Best Practices

1. Always backup before running migrations on production data
2. Test migrations on development data first
3. Make migrations idempotent - check if changes already exist
4. Keep migrations small - one logical change per migration
5. Never edit existing migration files after they've been applied
6. Document breaking changes in migration comments

Development Workflow

1. Create migration: Copy template and modify
2. Test locally: Run migration on development database
3. Add to version control: Commit the migration file
4. Deploy: Run migrations on staging/production
5. Verify: Check database schema and data integrity

Troubleshooting

Migration fails:

# Check database connection
ls -la pet_pictures.db

# Check migration table
sqlite3 pet_pictures.db "SELECT * FROM migrations;"

# Run specific migration for debugging
python migrations/001_add_description_column.py

Reset migration tracking:

-- Careful! This will re-run all migrations
DELETE FROM migrations WHERE migration_name = '001_add_description_column';

Integration

Docker

The migration runner is integrated with Docker:

# Run migrations in Docker
docker compose --profile migrate up migrate

CI/CD

Add to deployment scripts:

# Run migrations before starting app
python migrations/migrate.py && python main.py