Database Migration Management Table of Contents - Overview - When to Use - Quick Start - Reference Guides - Best Practices Overview Implement robust database migration systems with version control, rollback capabilities, and data transformation strategies. Includes migration frameworks and production deployment patterns. When to Use - Schema versioning and evolution - Data transformations and cleanup - Adding/removing tables and columns - Index creation and optimization - Migration testing and validation - Rollback planning and execution - Multi-environment deployments Quick Start Minimal wor…

);\n\n-- Create index\nCREATE INDEX CONCURRENTLY idx_users_phone ON users(phone);\n\n-- Rollback:\n-- DROP INDEX CONCURRENTLY idx_users_phone;\n-- ALTER TABLE users DROP COLUMN phone;\n```\n\n**MySQL - Column Addition:**\n\n```sql\n-- Migration: 20240115_001_add_phone_to_users.sql\n\n-- Add column with ALTER\nALTER TABLE users\nADD COLUMN phone VARCHAR(20) DEFAULT '',\nADD INDEX idx_phone (phone);\n\n-- Rollback:\n-- ALTER TABLE users DROP COLUMN phone;\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":811,"content_sha256":"a86dbfd694da95af7537dcb688d4a8bb9d88da2f55895138fdcab0232a0e2d26"},{"filename":"references/creating-indexes-non-blocking.md","content":"# Creating Indexes Non-blocking\n\n## Creating Indexes Non-blocking\n\n**PostgreSQL - Concurrent Index Creation:**\n\n```sql\n-- Migration: 20240115_003_add_performance_indexes.sql\n\n-- Create indexes without blocking writes\nCREATE INDEX CONCURRENTLY idx_orders_user_created\nON orders(user_id, created_at DESC);\n\nCREATE INDEX CONCURRENTLY idx_products_category_active\nON products(category_id)\nWHERE active = true;\n\n-- Verify index creation\nSELECT schemaname, tablename, indexname, idx_scan\nFROM pg_stat_user_indexes\nWHERE indexname LIKE 'idx_%';\n\n-- Rollback:\n-- DROP INDEX CONCURRENTLY idx_orders_user_created;\n-- DROP INDEX CONCURRENTLY idx_products_category_active;\n```\n\n**MySQL - Online Index Creation:**\n\n```sql\n-- Migration: 20240115_003_add_performance_indexes.sql\n\n-- Create indexes with ALGORITHM=INPLACE and LOCK=NONE\nALTER TABLE orders\nADD INDEX idx_user_created (user_id, created_at),\nALGORITHM=INPLACE, LOCK=NONE;\n\n-- Monitor progress\nSELECT * FROM INFORMATION_SCHEMA.PROCESSLIST\nWHERE INFO LIKE 'ALTER TABLE%';\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":1021,"content_sha256":"52479bb8c184849c04e3edcd7dd17a9c16b2136e1f8ec873672f87980d288c4a"},{"filename":"references/data-transformations.md","content":"# Data Transformations\n\n## Data Transformations\n\n**PostgreSQL - Data Cleanup Migration:**\n\n```sql\n-- Migration: 20240115_004_normalize_email_addresses.sql\n\n-- Normalize existing email addresses\nUPDATE users\nSET email = LOWER(TRIM(email))\nWHERE email != LOWER(TRIM(email));\n\n-- Remove duplicates by keeping latest\nDELETE FROM users\nWHERE id NOT IN (\n SELECT DISTINCT ON (LOWER(email)) id\n FROM users\n ORDER BY LOWER(email), created_at DESC\n);\n\n-- Rollback: Restore from backup (no safe rollback for data changes)\n```\n\n**MySQL - Bulk Data Update:**\n\n```sql\n-- Migration: 20240115_004_update_product_categories.sql\n\n-- Update multiple rows with JOIN\nUPDATE products p\nJOIN category_mapping cm ON p.old_category = cm.old_name\nSET p.category_id = cm.new_category_id\nWHERE p.old_category IS NOT NULL;\n\n-- Verify update\nSELECT COUNT(*) as updated_count\nFROM products\nWHERE category_id IS NOT NULL;\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":898,"content_sha256":"c796f0ec122a6de7f8054bdde761cdb9db46183ca6c87ccbdf3cff3a5dbfe203"},{"filename":"references/renaming-columns.md","content":"# Renaming Columns\n\n## Renaming Columns\n\n**PostgreSQL - Column Rename:**\n\n```sql\n-- Migration: 20240115_002_rename_user_name_columns.sql\n\n-- Rename columns\nALTER TABLE users RENAME COLUMN user_name TO full_name;\nALTER TABLE users RENAME COLUMN user_email TO email_address;\n\n-- Update indexes\nREINDEX TABLE users;\n\n-- Rollback:\n-- ALTER TABLE users RENAME COLUMN email_address TO user_email;\n-- ALTER TABLE users RENAME COLUMN full_name TO user_name;\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":454,"content_sha256":"77edee1029791780b8146ed3dd5c1475ecfc8fda4f2248993cc488788526499b"},{"filename":"references/table-structure-changes.md","content":"# Table Structure Changes\n\n## Table Structure Changes\n\n**PostgreSQL - Alter Table Migration:**\n\n```sql\n-- Migration: 20240115_005_modify_order_columns.sql\n\n-- Add new column\nALTER TABLE orders\nADD COLUMN status_updated_at TIMESTAMP;\n\n-- Add constraint\nALTER TABLE orders\nADD CONSTRAINT valid_status\nCHECK (status IN ('pending', 'processing', 'completed', 'cancelled'));\n\n-- Set default for existing records\nUPDATE orders\nSET status_updated_at = updated_at\nWHERE status_updated_at IS NULL;\n\n-- Make column NOT NULL\nALTER TABLE orders\nALTER COLUMN status_updated_at SET NOT NULL;\n\n-- Rollback:\n-- ALTER TABLE orders DROP COLUMN status_updated_at;\n-- ALTER TABLE orders DROP CONSTRAINT valid_status;\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":701,"content_sha256":"810f256879492f794692f6cce1ff31428d9e3cce38ffd92a044b3acd3fa34270"},{"filename":"scripts/validate-schema.sh","content":"#!/bin/bash\n# validate-schema.sh - Validate database schema\n# Usage: ./validate-schema.sh \u003cschema_file>\n\nset -euo pipefail\n\nSCHEMA_FILE=\"${{1:?Usage: $0 \u003cschema_file>}}\"\n\necho \"Validating schema: $SCHEMA_FILE\"\n\n# TODO: Add schema validation\n# - Check SQL syntax\n# - Verify foreign key references\n# - Check index definitions\n# - Validate naming conventions\n# - Check for missing constraints\n\necho \"Schema validation complete.\"\n","content_type":"application/x-sh; charset=utf-8","language":"bash","size":426,"content_sha256":"df4a785e3b4a890051c6095dd2238f29c149a91cf555c7cedffc0edc52b322bc"},{"filename":"templates/migration-template.sql","content":"-- Migration: [description]\n-- Created: [date]\n\n-- TODO: Customize for your migration framework\n\nBEGIN;\n\n-- Up migration\n-- TODO: Add schema changes\n-- CREATE TABLE IF NOT EXISTS ...\n-- ALTER TABLE ...\n\n-- Down migration (rollback)\n-- TODO: Add rollback statements\n-- DROP TABLE IF EXISTS ...\n\nCOMMIT;\n","content_type":"text/plain; charset=utf-8","language":"sql","size":302,"content_sha256":"f339b5b9269e9c6311faf330bf50143d2e10457b1126c3ba80df01d1d0c5b641"}],"content_json":{"type":"doc","content":[{"type":"heading","attrs":{"level":1},"content":[{"text":"Database Migration Management","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Table of Contents","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Overview","type":"text","marks":[{"type":"link","attrs":{"href":"#overview","title":null}}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"When to Use","type":"text","marks":[{"type":"link","attrs":{"href":"#when-to-use","title":null}}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Quick Start","type":"text","marks":[{"type":"link","attrs":{"href":"#quick-start","title":null}}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Reference Guides","type":"text","marks":[{"type":"link","attrs":{"href":"#reference-guides","title":null}}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Best Practices","type":"text","marks":[{"type":"link","attrs":{"href":"#best-practices","title":null}}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Overview","type":"text"}]},{"type":"paragraph","content":[{"text":"Implement robust database migration systems with version control, rollback capabilities, and data transformation strategies. Includes migration frameworks and production deployment patterns.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"When to Use","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Schema versioning and evolution","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Data transformations and cleanup","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Adding/removing tables and columns","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Index creation and optimization","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Migration testing and validation","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Rollback planning and execution","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Multi-environment deployments","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Quick Start","type":"text"}]},{"type":"paragraph","content":[{"text":"Minimal working example:","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Create migrations tracking table\nCREATE TABLE schema_migrations (\n version BIGINT PRIMARY KEY,\n name VARCHAR(255) NOT NULL,\n executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n duration_ms INTEGER,\n checksum VARCHAR(64)\n);\n\n-- Create migration log table\nCREATE TABLE migration_logs (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n version BIGINT NOT NULL,\n status VARCHAR(20) NOT NULL,\n error_message TEXT,\n rolled_back_at TIMESTAMP,\n executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\n-- Function to record migration\nCREATE OR REPLACE FUNCTION record_migration(\n p_version BIGINT,\n p_name VARCHAR,\n p_duration_ms INTEGER\n) RETURNS void AS $\n// ... (see reference guides for full implementation)","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Reference Guides","type":"text"}]},{"type":"paragraph","content":[{"text":"Detailed implementations in the ","type":"text"},{"text":"references/","type":"text","marks":[{"type":"code_inline"}]},{"text":" directory:","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Guide","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Contents","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Adding Columns","type":"text","marks":[{"type":"link","attrs":{"href":"references/adding-columns.md","title":null}}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Adding Columns","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Renaming Columns","type":"text","marks":[{"type":"link","attrs":{"href":"references/renaming-columns.md","title":null}}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Renaming Columns","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Creating Indexes Non-blocking","type":"text","marks":[{"type":"link","attrs":{"href":"references/creating-indexes-non-blocking.md","title":null}}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Creating Indexes Non-blocking","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Data Transformations","type":"text","marks":[{"type":"link","attrs":{"href":"references/data-transformations.md","title":null}}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Data Transformations","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Table Structure Changes","type":"text","marks":[{"type":"link","attrs":{"href":"references/table-structure-changes.md","title":null}}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Table Structure Changes","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Best Practices","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"✅ DO","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Follow established patterns and conventions","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Write clean, maintainable code","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Add appropriate documentation","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Test thoroughly before deploying","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"❌ DON'T","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Skip testing or validation","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Ignore error handling","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Hard-code configuration values","type":"text"}]}]}]}]},"metadata":{"date":"2026-11-02","name":"database-migration-management","author":"@skillopedia","source":{"stars":375,"repo_name":"useful-ai-prompts","origin_url":"https://github.com/aj-geddes/useful-ai-prompts/blob/HEAD/skills/database-migration-management/SKILL.md","repo_owner":"aj-geddes","body_sha256":"88ecebde517adb0febbe86b2dee5a1ef1d0d46e9468cd40ad6aaf325dfd782f4","cluster_key":"a18289c52dba0c2127715570f6f2d770aa6e860e8dc562e9a5cce4d8780f9aca","clean_bundle":{"format":"clean-skill-bundle-v1","source":"aj-geddes/useful-ai-prompts/skills/database-migration-management/SKILL.md","text_bytes":4613,"source_commit":"3f5182cfd739fc113f4af5244a1cf342ad7f7911","skills_sh_rank":19769,"attachment_count":7,"text_attachments":7,"attachment_storage":"skill_attachments","binary_attachments":0},"cluster_size":1,"skill_md_path":"skills/database-migration-management/SKILL.md","import_metadata":{"date":"2026-11-02","author":"@skillopedia","version":"v1","category":"data-analytics","category_label":"Data"},"exact_dupes_collapsed_into_this":0},"version":"v1","category":"data-analytics","import_tag":"skills-sh-top20k-2026-11","description":"Manage database migrations and schema versioning. Use when planning migrations, version control, rollback strategies, or data transformations in PostgreSQL and MySQL.\n"}},"renderedAt":1793666006028}

Database Migration Management Table of Contents - Overview - When to Use - Quick Start - Reference Guides - Best Practices Overview Implement robust database migration systems with version control, rollback capabilities, and data transformation strategies. Includes migration frameworks and production deployment patterns. When to Use - Schema versioning and evolution - Data transformations and cleanup - Adding/removing tables and columns - Index creation and optimization - Migration testing and validation - Rollback planning and execution - Multi-environment deployments Quick Start Minimal wor…