Automate SQL Database Migrations Safely

This skill provides expert guidance on database migration scripting - robust, maintainable, and safe schema changes across database platforms, covering migration versioning, rollback strategies, data preservation, and production deployment. Core principles: version migrations with sequential numbering or timestamp-based naming (e.g. V001__initial_schema.sql or 20240315_001_add_user_table.sql) with descriptive names, maintain strict ordering to avoid dependency conflicts, and never modify a migration file once it's deployed to production. Idempotency and safety require checking for existence before creating/dropping objects (IF EXISTS/IF NOT EXISTS), explicit transaction boundaries, and proper error handling and validation.

Use this skill when writing new schema migrations, planning rollback scripts, migrating large datasets safely, or preparing a production deployment checklist for schema changes. A standard migration template includes a header comment (migration name, description, author, date, and its paired rollback script name), wraps logic in BEGIN TRANSACTION/COMMIT TRANSACTION, validates prerequisites exist before proceeding (raising an error and rolling back if not), creates the target table with appropriate constraints and foreign keys, adds performance indexes, and records the migration in a tracking table.

Cross-platform patterns are shown for PostgreSQL (CREATE TABLE IF NOT EXISTS with BIGSERIAL), SQL Server (IF NOT EXISTS check against sys.tables with IDENTITY(1,1)), and MySQL (CREATE TABLE IF NOT EXISTS with AUTO_INCREMENT) for the same logical table. Safe data migration patterns include adding a nullable column with a default, backfilling it, then setting it NOT NULL and adding a check constraint; and batch-processing large tables with a bounded UPDATE TOP (@batch_size) loop plus a short WAITFOR DELAY between batches to avoid blocking other operations. A rollback script mirrors the migration: optionally backs up data into a timestamped table, drops dependent indexes then the main table, and removes the corresponding row from the migration-history table.

A migration_history tracking table records version, description, a checksum for integrity verification, applied_by, applied_at, execution_time_ms, and whether a rollback is available, indexed by version and applied date. Index management favors online/concurrent index creation (CREATE INDEX CONCURRENTLY on SQL Server/PostgreSQL) where possible, or dropping and recreating indexes around bulk operations on large tables. A production deployment checklist: test migrations against production-like data volumes, estimate execution time and plan maintenance windows, verify rollback procedures actually work, monitor lock duration and blocking queries, use database-specific online operations, and implement circuit breakers for long-running operations. Schema evolution patterns include expand-contract (add new structures, migrate data, remove old ones), blue-green (deploy to a parallel environment), feature flags (control schema usage via configuration), and maintaining backward compatibility so applications keep working during the transition. Error handling wraps migration logic in BEGIN TRY/BEGIN CATCH, validates the result (e.g. checking a non-zero row count after migration), rolls back and raises a detailed error with line number and message on failure, and prints a success confirmation otherwise.

Database engineers and backend developers writing production schema migrations who need concrete, safe patterns for versioning, idempotent execution, large-data batch migration, rollback scripts, and deployment checklists across SQL Server, PostgreSQL, and MySQL.